07-30-07, 12:57 #1Registered User
- Join Date
- Aug 2004
- Cary, NC
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:
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.
07-30-07, 14:56 #2Moderator
- Join Date
- Dec 2004
- Madison, WI
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.
Last edited by pkstormy; 07-30-07 at 15:19.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
07-31-07, 16:30 #3Registered User
- Join Date
- May 2004
- New York State
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.
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?