How to compare 2003 sales vs 2002 in Access

[CS]

I have 2 tables one contains 2003 sales YTD which lists
account name,product code, units sold, price and net
sales. I have another table with the same data for
2002. I need to find a way to write a query that will
compare the results by account 2002 vs 2003. So far any
queries I have written have run endless loops.

Anyhelp will be greatly appreciated

 

[eric]

Assuming that the account numbers are unique in both
years. Also that you already have summed to the year per
each account number:
add both tables in the query
join at the account number
subtract 2002 ytd from 2003 ytd
The only issue is what do you want to do with accounts
that are in 2002 and not in 2003 or reverse. This will
impact the type of join.

Eric

 

[Greg H]

I agree with Van, but this solution should work for you until you
restructure your tables.

Create a Union query based on the two tables. Rename the Units Sold,
Price and Net Sales form 2002 to 2002Units Sold, 2002Price and 2002
Net Sales. Add these three colums: 2003 units Sold, 2003Price and 2003
Net Sales and set the value to zero. Here is an example of the sql
code (assumes table names are 2002Table and 2003 Table):

SELECT [2003Table].AccountName, [2003Table].ProductCode, 0 AS
2002UnitsSold, 0 AS 2002Price, 0 AS 2002NetSales,
[2003Table].UnitsSold AS 2003UnitsSold, [2003Table].Price AS
2003Price, [2003Table].NetSales AS 2003NetSales
FROM 2003Table
Union SELECT All [2002Table].AccountName, [2002Table].ProductCode,
[2002Table].UnitsSold AS 2002UnitsSold, [2002Table].Price AS
2002Price, [2002Table].NetSales AS 2002NetSales, 0 AS 2003UnitsSold, 0
AS 2003Price, 0 AS 2003NetSales
FROM 2002Table;

Save the query, then create another query based off of it, group by
sum on the Units Sold, price and Net Sales.

Thank you,
GH