Query to list sales by product in 2 periods

[DaveWilds]

Please refer to attachment for details of my problem.

 

[neileg]

You can't do this in a single simple query, but it's not too hard. You need to create 3 queries.

The first is a simple query that returns all the valid product codes. If your data spans more than the two years, apply a criterion to restrict to between 1/1/07 and 31/21/08. Set the unique values property of the query to Yes.

Then crete an aggregate query that selects the 2007 data and totals volume and value and groups by product code. Then one that does the same for 2008.

Now create a fourth query. Add the three queries you just made to the design grid. Link the product code in the first query using a left join (type 2 join) to the 2007 query. Then link the product ID in the first query with the 2008 query, again with a left join.

Drag the 5 relevant fields to the query grid.

Job done.

 

[DaveWilds]

You are an absolute gem. This worked practically first time and has solved something I have wrestled with for ages. Thanks very much.

 

[neileg]

Happy to help.

 

[khawar]

Infact this can also be done in a single query and its the easiest way
Download the attached sample

 

[DaveWilds]

Khawar,

I would like to see your single query solution, but unfortunately security restrictions on our network won't let me open the downloaded file. Can you give an explanation of the query?

Thanks, Dave

 

[neileg]

SELECT Table1.ProductCode, Sum(IIf([invoicedate] Between #1/1/2007# And #12/31/2007#,[Volume],0)) AS [Per1 total Volume], Sum(IIf([invoicedate] Between #1/1/2007# And #12/31/2007#,[Value],0)) AS [Per1 total Value], Sum(IIf([invoicedate] Between #1/1/2008# And #12/31/2008#,[Volume],0)) AS [Per2 total Volume], Sum(IIf([invoicedate] Between #1/1/2008# And #12/31/2008#,[Value],0)) AS [Per2 total Value]
FROM Table1
GROUP BY Table1.ProductCode;

This is cleverer than my solution!