January 17th, 2013, 11:52 AM
Cross Tab With Multiple Rows And Date Headers...Help!
I am trying to recreate this table in MS Access using a cross tab query but not having any success.
How can I get this done because I am at a loss. Help!
OPEN Date 9/1 9/2 9/3 9/4 9/5
VOLUME 92 80 138 2524 2063
COMPLETE 61 65 108 1505 1179
PASS% 66.3% 81.3% 78.3% 59.6% 57.1%
FAIL 31 15 30 1019 884
Any information would be greatly appreciated.
January 17th, 2013, 03:01 PM
It's hard to give an accurate answer because we know absolutely nothing about your data, but from what I do see, I do not believe what you want to do is possible. The total fields in a cross tab query must be uniform. (you can only select one total field in a crosstab query) They're always going to be the same calculation or data field from your table or query. Also it should be noted, that while you can use dates in your crosstab query, you can't use them on a report or if you place your query in a subform.
January 18th, 2013, 05:04 PM
I won't say 'not possible' but instead 'not easy'.
Do 5 crosstabs and then either join them on common unique ID (results in one record) then arrange the fields in report to have the appearance of 5 records or UNION the crosstabs to show 5 records.
Stabilizing a crosstab to run perpetually in report can be tricky, especially when pivoting on date value. Review:
As meratigoerr says, need better understanding of data to give better advice.
Last edited by June7; January 18th, 2013 at 05:12 PM.
January 18th, 2013, 05:45 PM
June, I had never considered the idea of a union on crosstab queries. I'm definitly going to have to give it a try.