Sales Analysis Help

[richj55]

I am attempting to analyze sales for my company. I need to compare 1999 sales to 2000 sales and calculate the difference. Our company uses customer numbers, region codes, product class codes, etc. My problem is that when I run a query for 1999 sales and then run the query for 2000 sales, I cannot combine the numbers properly into one query and calculate the difference. I am now using 1999 and 2000 as different queries and then have master tables for each of the codes. The final report needs to have the names that are associated with the codes as well as the sales figures. Any help is appreciated.

 

[DreewToo]

Try using a Union query...if both tables are the same format, and you have 2 separate queries set up already for the output desired, copy the SQL from one of them, move to the other query in SQL view, remove the semi-colon from the end of the query, type UNION, and paste the first query at the end.&nbsp;&nbsp;This will combine the query results, so long as the order/number of the colunms are identical, and the results can be queried for comparisons.<br><br>Something like:<br><br>SELECT field1, field2, field3 FROM table1<br>UNION<br>SELECT field1, field2, field3 FROM table2;<br><br>One result of this is the design view for the query becomes unavailable.<br><br>HTH,<br>Drew<br><br>

 

[lwilly]

I have done what sounds like the same thing, so if you havent had any luck getting this worked out I will show you some examples. One of my reports calculates current and previous Month, Year it also show current and previous rolling year totals as well as differences. This is all done by Region and grouped by State