tracking transactions with date-based header

[JoeyB_99]

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 quantities. for example,

Cost code A, October 13/12, 100.0
Cost code A, October 20/12, 71.0
Cost code B, October 13/12, 15.0
Cost code A, October 31/12, 101.0
Cost code B, October 31/12, 21.0
Cost code C, October 31/12, 90.0
Cost code D, October 20/12, 11.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 100.0 71.0 101.0
Cost code B 15.0 21.0
Cost code C 90.0
Cost code D 11.0

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?

 

[pr2-eugin]

Might look into Pivot Table view? As I am not sure you can have Data in Column header.. but it is just me.. Others might have a better answer..

 

[nanscombe]

It looks like you want to create a Crosstab query. The easiest method is to use The Query Wizard and pick The Crosstab option.

You'll be asked a series of questions:

1) Which table or query you want to use as the source - You know what that is. *
2) The fields, up to three, to be used as Row headings - Looks like your Cost Code field
3) The Column headings - Your dates
4) The data for the intersection - Probably your Cost Field with the Sum function
5) Finally give it a name

Have a play around but I think that should give you what you want.

* Oh yes, you'll probably want to limit the data, by date, in a query to use as a source for this otherwise you may get a very, very, very wide result assuming it doesn't just fail

 

[pr2-eugin]

Yup Nigel is right.. CrossTab.. totally forgot about that devil.. That is more useful..