Append Query Question

[jackadamson]

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.

 

[jdraw]

Sounds to me you have 3 tables - Products, Customers and Sales. Also you have chosen to name Products as Assets. I think you may have a table design issue.
There is a data model here ( forget the Dell specific stuff)
http://www.databaseanswers.org/data_models/dell_computer_sales/index.htm

It might help with tables and relationships.

You can calculate Dates and Sales Amount from these tables. No need to have a table dedicates to Top sales as far as I can see, but you know the data better than we do.

 

[jackadamson]

Thanks for the response.

Unfortunately I am not able to change the tables. They are extracted from an excel file that is extracted from a large external database. This can not change and in order to it easy for the user to update the information, I can not change the table structures at all.

 

[jdraw]

Then I'd suggest you show us a picture and description of exactly what you have and what you need.