Add record if none exists

[skwilliams]

I have a table (tblSales) with these fields (RecDate, Code, Type, OrderCount).

I also have a linked table (lnkSales) with these fields.

Daily I append the data from the lnkSales to tblSales.

The tblSales table must have a record for each code daily.
These are the codes (01,02,05,07,09,10,15).

I need to automatically add a record for each code that wasn't appended.

For example,

lnkSales contains:
03/22/06 01 Mc 3
03/22/06 02 Mc 1
03/22/06 05 Mc 1
03/22/06 07 Mc 2
03/22/06 10 Mc 1

When appended to tblSales there is no record for code 09 or 15.

I need to add these records to tblSales
03/22/06 09 Mc 0
03/22/06 15 Mc 0

Can someone explain the best way to accomplish this?

Thanks.

 

[Banaticus]

To delete a message, click [Edit] in the bottom right corner of the message, then two radio buttons will appear at the bottom of the message. Change that to "Delete Message" then click on the [Delete Message] button that appears over on the right. You posted this message several times.

 

[Banaticus]

As Pat put it, but make sure to wrap the code (01,02,05,07,09,10,15) field in Nz(), when doing comparisons so that you don't run into a problem if you test a record that has a Null in that spot.

If you tell us what you do with tblSales (how you use it), we can tell you where to insert Nz([fieldName]).