If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > Report horizontal from vertical data

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Aug 2004
Location: Cary, NC
Posts: 264
Lightbulb Report horizontal from vertical data

I think I may have backed myself into a corner but I thought I would check first.

I need to show the completeness level for three different stages for specific categories.

The users flag each individual record as a yes/no for each of the three stages and I need to create the high level view as a percentage of complete vs total.

The flags were stored in a seperate table using partId, metricType of 1,2,3 and metricState as T/F.

I calculated the proper values that I would like to display in a query grouped by the category and then the metric type so I have three lines per category in my query

cat, type , value
cat 1, metric 1, .34
cat 1, metric 2, .46
cat 1, metric 3, .18

I want to display them on a report so they format like this:

category, production, quality, manager (as the headers for metrics 1,2,3)
1 , .34 , .46 , .18 (as the values lined up under the headers)
and so on for each of the categories in the report.

I would even be ok with a report that listed the three types vertically on the page but with a descriptive label. It would look something like:

category 1
Production completeness: .34
Quality control: .46
Manager reviewed: .18

So my question:
How do I get the proper data source into the report field? I tried using the above query as the data Source for the report but I can't figure out how to get the proper value for the desired type.

What I want logically is: " metricValue where metricType is 1 " then repeated for type 2 and 3.

The only way I have figured out so far is to maintain three copies of the metrics query with the only difference being the value in the type field. I then create a combined query joining those three queries on the category. I really do not like this approach.

I tried using a prompt on the base query for the metric type but I could not figure out how to send that value to the query from the query that combines.

Any thoughts?

Reply With Quote
  #2 (permalink)  
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925

I'm not sure if this is what you're getting at, but what I used to do when I needed to get things to look right in a report (which needed to show totals, averages, or other stuff, etc. with details of records and needed to be fashioned in a certain way where just a totals/averages field didn't work in the report footer) was something like this...

I would have 1 or more append-table/update queries which made the report table in a "grouping-capable" fashion. Or even a pivot type-table look.

I figured out what fields for the table I would want to group on and then added the other fields. The table was 1 flat table. So the fields for the table might look like this...

Category <- this would be a grouping field on the report
SubCategory <- this would be a sub-grouping field on the report
ToolName (or other detailed field name) <- to show in the detailed section
OtherDetailFieldName <- to show in the detailed section
TotalPriceFieldName <- to show in the detailed section
AveragePriceFieldName <- to show in the detailed section

Then, for example, I might have an append table query which had the Category field, another grouping type field, etc (something common on each of the records), which appended to this table. I might have a totalling or averaging query which then updated the table with the totals/averages using/linking the grouping fields which were common (ie. the total or average fields would be duplicated for a set of records in the table, like the grouping fields.) This wouldn't matter as it would all get sorted out in the report when I did the groupings for the report. It was 1 flat table where some fields of information were duplicated but again, doesn't matter as it's grouped in the report.

Once I got down how I want it to look in the table, then for the report, it was fairly easy to simply group and show the fields under each grouping.

Again, sorry if I misread your question or if I didn't explain this well at all. It's hard to explain it without showing you and this may not even help with what you're trying to accomplish.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Last edited by pkstormy; 07-30-07 at 15:19.
Reply With Quote
  #3 (permalink)  
Registered User
Join Date: May 2004
Location: New York State
Posts: 1,177
I'm not sure if I understand either, but if I'm not mistaken, you want to display what's now in a column horizontally, and what's in a row, you want to display vertically.

If that's what you want, it's not an unusual need. It's also not hard to accomplish. Simply read your data in (you'll have to control the amount of records read in code) and populate a 2-level array.

For example,

Dim arTblDta(15, 4) As String

' where 15 is the maximum number of records you'll ever have to process at a time, and 4 is the number of fields you need to display.

As you read through the records (I made the assumption that you've already set a recordset object, and initialized all the elements of the array to ""),

arTblDta(1, 1) = !cat
arTblDta(1, 2) = !type
arTblDta(1, 3) = !value

next record would be 2, 1; 2, 2; 2, 3


Now when you display the report, use text boxes in the detail section that are UNBOUND. (I use shout, because if you use bound, you'll get into trouble in this step.) Line by line, feed the contents of the array into the unbound textboxes; not 1,1;1,2;1,3 like you read it in, but 1,1;2,1;3,1, so they'll line up the way you want it.

Did I bingo, or did I blah?

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On