Reporting stacked data from qry in columns

[SPW_12]

Good afternoon. I have a particularly challenging task that I just cannot seem to overcome.

I want to to use a query to build a report. The table that builds the query has the following structure:

ID Account Date SumofAmt

I have multiple years in the table and therefore chose to stack the data so I can query on the date field. I want to query on the date field so I can run a Calendar year query or a trending query (i.e. 12 months of the most recent date. eg. Jul 2010 through Jun 2011).

When I want to create a report from this query, the result is a vertical column with the following structure:

ID 1: First_Name, Last_Name
Date: January 2011
Account: Account1, SumofAmt
Account2, SumofAmt
Account3, SumofAmt

Date: February 2011
Account: Account1, SumofAmt
Account2, SumofAmt
Account3, SumofAmt

Etc. until next ID:

ID 2: First_Name, Last_Name
Date: January 2011
Account: Account1, SumofAmt
Account2, SumofAmt
Account3, SumofAmt

Date: February 2011
Account: Account1, SumofAmt
Account2, SumofAmt
Account3, SumofAmt

and what I want to accomplish is the following structure:

ID 1: First_Name, Last_Name

January February March etc...
Account: Account1, SumofAmt SumofAmt SumofAmt


Account2, SumofAmt SumofAmt SumofAmt
Account3, SumofAmt SumofAmt SumofAmt

ID 2: First_Name, Last_Name

January February March etc...
Account: Account1, SumofAmt SumofAmt SumofAmt
Account2, SumofAmt SumofAmt SumofAmt
Account3, SumofAmt SumofAmt SumofAmt

If I were to structure the table to have the months/years across the top I can just pull the individual fields I want to report on, say I want to run a 12 month rolling trend each month, I can just write the query by pulling the month fields. The problem I have with this is that I cannot query by date...I just think this is an inferior DB structure...

Any suggestions that will allow me to stack the data so I can query the date field, yet be able to report with months aligned side-by-side will be greatly appreciated.

Thanks!

[aytee111]

Sometimes I make a temp table for this, although that is a lot of work. Have you tried a crosstab query? You might have to make two, I can't remember exactly, one for the account and one for the amount, but then your record source on your report could join them together on ID.

[June7]

The data structure is normalized, the report design is not conventional. Crosstab might accomplish what you want. Other methods of left-to-right record display discussed in this recent thread https://www.accessforums.net/showthr...9062#post99062

[SPW_12]

Thank you both. How dumb of me. I think this will work. I was trying something a little too complicated I think. I was trying to solve my issue with a QBF (Query by form) and VB. I do a lot of work in Excel, Access is newer to me.

Thanks again!