Comparing sales for the last 2 years

[Adele]

Hi all,



I have the following 4 tables:

Customer
Invoice details 2010
Invoice details 2011
Products (used to specify criteria e.g. search for “plastic” only)

I need to produce a query that will return sales for each customer for 2010 & 2011, even if the values in 2011 are null and did have a value in 2010 and vice versa.

Many thanks,

Adele

[June7]

Did you try a join of these tables? Not what you want?

Invoice details should not be two tables. Use a UNION query to combine these two so you get the effect of one table. Then join this query to the Customer table. There is no wizard or designer for UNION queries, must type into the SQL View window of query designer.

SELECT * FROM [Invoice Details 2010]
UNION SELECT * FROM [Invoice Details 2011];

This assumes the tables have the exact same structure and fields are in the same order.

Is the invoice number unique across all years?

[Adele]

thanks for the reply, i have created the union for sales tables 2010 & 2011 and called it ‘unionSales’ I started a new query and used this to pull data from however the query result I get just groups all of the sales from 2010 & 2011 together rather than giving a comparison

any ideas as to what I am doing wrong?
many thanks
Adele

[alansidman]

Perhaps a cross tab query to show the comparisons.

Look here on how to do that.

http://www.datapigtechnologies.com/f.../crosstab.html

Alan

[Adele]

thanks to both of you,

i used the union query to link the tables then ran an expression like the guy did in the vid..

cheers guys!

[cechopin]

Thank you so much for your question and answers! I have been struggling with this same problem for months! Perfect explanation! So glad I found this!!!

[cechopin]

Is there a way to subtotal within the query or do I need to do that in the report?