Multiple Cross-tab Query?

[JOEYB99]

I am using Access 2007 and having trouble setting up a query (or queries?) that will create a report in a certain fashion.

I have transactions that are date based for cost codes, with three different quantities. for example,

Cost code A, October 13/12, 100.0, 0.0, 55.0
Cost code A, October 20/12, 71.0, 60.0, 110.0
Cost code B, October 13/12, 15.0, 0.0, 0.0
Cost code A, October 31/12, 101.0, 900.0, 750.0
Cost code B, October 31/12, 21.0, 0.0, 0.0
Cost code C, October 31/12, 90.0, 0.0, 15.5
Cost code D, October 20/12, 11.0, 10.0, 20.0

There is no set frequency to the dates, and not every cost code will have a transaction for every given date.

My end-goal is to come up with a report that looks like,


Start date: October 10/12 End date: October 31/12


October 13/12 October 20/12 October 31/12

Cost Code A Planned 100 71 101
Actual 0 60 900
Earned 55 110 750

Cost Code B Planned 15 21
Actual 0 0
Earned 0 0

Cost Code C Planned 90
Actual 0
Earned 15.5


The user inputs a date range and the columns are set up for all possible dates of the transactions within that date range. The transactions are then listed with the appropriate values under the corresponding columns.

I'm having trouble setting up the column headers, and how the transactions will have the values fall out properly. Can someone please help?

 

[dhookom]

Where do paonned, Actual, and earned come from? They aren't anywhere in your data that I can tell.

Why do you mix your date formatting like "October 20/12" rather than "10/20/12" or "20 Oct 12" or more common formats? Is this really a date field?

I think you need a normalizing union query first to get each quantity value into its own record. Then create a crosstab query based on the union query that has Cost Code and costtype as the Row Headings as the date as the column heading. The value will be the sum of quantity.

Duane
Hook'D on Access
MS Access MVP