Adding missing records to a table

[Petterq]

I have a table containing prices for items, linked to a customer database.
One key is CompanyID, indicating what company the spesific price is valid for.

There is a certain basic amount of items that should be valid for all
companies. Theese items is linked to a test customerID.

I would like to make a query that adds any records missing from the basic
amount of items to the companies missing them.

I.e. if one itemID is 30, and should be valid for all companies. This item
might be missing for a certain percentage of the customers in the database.
The query should add a record with itemID 30 and the customerID for each
customer that misses the record, ignoring those who already have it.

Any hints?

 

[Steve Schapel]

Petterq,

You can just run an Append Query to complete the missing items. The SQL
of this query will look something like this...
INSERT INTO Prices ( CompanyID, ItemID )
SELECT CompanyID, ItemID
FROM Companies, BasicItems
If you have a Unique Index set in the Prices table for the composite of
CompanyID and ItemID, existing records will not be duplicated. Having
said that, test on a backup copy first