Re: Adding missing records to a table
- From: Steve Schapel <schapel@xxxxxxxxxxx>
- Date: Sun, 18 Sep 2005 12:21:34 +1200
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 :-)
-- Steve Schapel, Microsoft Access MVP
Petterq wrote:
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?
.
- References:
- Adding missing records to a table
- From: Petterq
- Adding missing records to a table
- Prev by Date: Re: Names in an Expression
- Next by Date: Re: Having trouble joining a table and query
- Previous by thread: Adding missing records to a table
- Next by thread: Can't find last order with data fields in a query
- Index(es):
Relevant Pages
|
|
