Simple Subtraction

I have two tables with the exact same format/structure. One is for the end of month 1, the other is for the end of month 2. Each table contains four fields, and the same number of records (model, color, price, amount). I want a query that will return the change in the amount field. How do I join the tables (join on 1, 2, 3, or all 4 fields?), and what is the SQL to generate the query result?

Thanks

George

You may want to consider revising your table structure. The "one table per
month" design you described may work in a spreadsheet, but causes you (and
Access) headaches.

Access is a relational database, and can handle the situation by simply
adding a field into a single table that hold a date for that row. Thus, all
the rows where Month = 1 in this new table (or =2, or ...) is a simple
query.


--
Good luck

Jeff Boyce
Access MVP

Thanks for the insight. However, I may have made this sound easier than it is. Each database I have consists of several tables, one of which is the amount table described below. In my current database I am importing (linking to) a table from a different database (file). Now that I have the two tables, I want to subtract them and find the net gain or loss for each "category". My current approach is to join the two tables on the three "categories" (model, color, price) and calculates the difference between the two amount fields. It seems slow (30 seconds for 1,000,000 records). I was hoping for a more efficient way to get the net results.

"Jeff Boyce" wrote:

George

You may want to consider revising your table structure. The "one table per
month" design you described may work in a spreadsheet, but causes you (and
Access) headaches.

Access is a relational database, and can handle the situation by simply
adding a field into a single table that hold a date for that row. Thus, all
the rows where Month = 1 in this new table (or =2, or ...) is a simple
query.


--
Good luck

Jeff Boyce
Access MVP

George

You can sometimes get better performance if the fields on which you select
and/or join are indexed in their respective table.

--
Good luck

Jeff Boyce
Access MVP