Append Query Question

[JackA]

I have two tables, sales and assets.



Sales has product code, name, sales figure and sales period (e.g. 01/01/2001 to 01/02/2001).

Assets has product code, name, asset value and date (e.g. 01/02/2001).

The product codes are linked and are the same in these two tables.

I have also linked the dates in a top date table.

Top date has, top date (e.g. Q1), sales period (from sales table), and date (from assets table).

The Assets and Sales tables are updated by running a macro that transfers data from excel sheets to the respective tables.

However when the sales figure = 0 for a specific period, a record is not created in the excel file and therefore does not appear in the access table.

I would like to create an append query that adds a record into the sales table in access if there is a record in the assets table but not a record in the sales table for the same top date.

I hope that makes sense. Please let me know if you need any more explanation.

If that is not possible, I would like to run a query that finds all the sales records that are missing (i.e. there is a record for assets but not for sales).

Can anyone help? It would be very much appreciated.

[AndreT]

Here's how I do it -

1-Create query 1 of (Product Code) form Sales,
2-Create query 2 of (Product Code) from Assets
3-Create query 3 UNION these 2 together, duplicates will be eliminated
4-Create query 4 outer join back to each table and select (product code) that is null on the other side.
5-Create query 5 insert query 4 into the table

Should work like a charm.

[JackA]

Thanks AndreT,

It works to an extent. It finds all the codes that appear in 1 and not 3. However it does not find all the codes that appear in 1 with a certain date but not in 3 with a certain date.

For example if there were the following data,

Query 1
Code Date
ABC 18/01/2010
ABC 16/01/2010
AMS 16/01/2010

Query 3
Code Date
ABC 16/01/2010

Query 4 would find the following data,

Code Date
AMS 16/01/2010

It would not find ABC 18/01/2010 aswell. Which I want it to find to because i want to add both records to table 2.

Any suggestions?

[rpeare]

Run this query on the queries you have listed in your example:

SELECT Query1.Code, Query1.CodeDate
FROM Query1 LEFT JOIN Query3 ON (Query1.CodeDate = Query3.CodeDate) AND (Query1.Code = Query3.Code)
WHERE (((Query3.CodeDate) Is Null));

[AndreT]

My example uses "Product Code" as the primary key. In the example you provide, you have ("Produce Code", "Date") as the primary key. So you need to created queries using both fields as the matching key.

Just watch out if the "Date" field is truly a date only field. If it also has time in there, you will get more than you bargain for.

Or you may use rpeare's approach and INSERT directly without a UNION.

Good luck.

[JackA]

Thanks guys. You are true Access Wizards. It works perfect now.