Parameter query but cant use crosstab

[HardballGets]

Hi

I have a query that gives me the total sales amount, as well as other information from other tables. But I cant use a crosstab query because I have to use more than one table in the query.

What I want to happen is have a query that returns something like the following

TITLE : | MTD | Month 1 | Month 2 | Month 3 | Month 4
Product1 SALES: | $5302 | $4583 | $5842 | $5833 | $3843
Product2 SALES: | $85309 | $124223 | $131287 | $126210 | $172712

I need it to run this way because I have thousands of products running down.

Can I have Now()-30 in the criteria field in one column and have Between Now()-30 and Now()-60 in the next column and so on???(I know I cant because I've tried it but something along those lines)

Can someone help?

 

[Andrew Fergus]

Hello and welcome to Mr Excel.

You can use more than one table to create a crosstab query - provided they are linked in some way (for example, linked on order number or product id etc.)

Use the product description as the row heading (you can actually have multiple row headings, so you could include another row heading such as product group for example), and the sales value (or volume) as the Crosstab 'Value' (change the Group By to Sum) and use Month([Date]) as the column heading.

The Month([Date]) bit extracts just the month from the Date field (be sure to use the actual name of your date field) - this way you don't have to worry about things like Now()-30 etc. But be aware that the same month for multiple years will be lumped together. You can avoid this by adding the date field to the crosstab query a 2nd time, but instead of being a part of the crosstab query, you can set some criteria e.g. >=#01/01/05# to limit it to just this calendar year or whatever you want.

Post a reply to this (including the crosstab query SQL that you are attempting) if you can't get it to work.

HTH, Andrew.

 

[HardballGets]

Thanks Andrew, a solution which I can use.

Thanks again