Best way to show last month vs this month side by side

[tagteam]

I am trying to create a report like this

Sales
Customer Last Month This Month
Customer 1 500 500
Customer 2 700 800

I have all the data but I am getting confused on how I can display it. When i try to create a separate query for each month and then use them both as the source for a new query and use it as a total I end up with too many records, or details and not summary. I am a little confused on the best way to do this. Would it be better to make another table that stores the customer name and amount for each month and then use that for a report?

Any help is appreciated.

[pbaldy]

You can have your 2 queries sum the data by customer for each period of time. In a third query join the customer table to those 2 queries with a join on the customer ID that pulls all records from the customer table. That type of join would make sure you display a customer even if they didn't have a transaction in either of the 2 months.

[CJ_London]

perhaps use a crosstab query?
rowheader customer
columnheader year/month invoice date
value - sum invoice value

[tagteam]

are you talking do the crosstab in excel because I didnt think that they had those in access any more. Crosstab probably would be the best. Thanks to pbaldy, i was able to get the base query working. I was forgetting about adding the customer table and then joining to two queries to that. Howerver, it still doesnt work very well if I add in rider. like below

Customer| rider| total last month |rider |total this month

[CJ_London]

Crosstabs are alive and well. Think you may be thinking of pivot queries which have now be deprecated

[tagteam]

Ah, yes, I was definitely thinking of pivot and not crosstab. I will look that up and try it today.

[tagteam]

Ok, Thanks to Ajax. Yes that is exactly what I was looking for and unfortunately I have mucked about several times with joining queries where that would have really helped. I never use the query wizard and thought that with pivot gone so was crosstab. I actually thought they were basically the same. I actually looked it up to see the difference this morning. It appears that crosstab is basically just a simple fixed pivot. With pivot you can add in new variables and drill down.

Anyway, I wish they still had pivots in access but the crosstab is great.

Thanks Ajax

[Gicu]

If you are interested please have a look at my pivot table builder utility: http://forestbyte.com/ms-access-util...able-designer/

You can design the pivot in Access and with a click will create an Excel file with the pivot (and the source data so it is totally detached from the db).

Cheers,