Using Append or update query

[Guest]

Hi,

I have a table called Customer with a CustID field. I have another table
called Licenses that has this same CustID field. We update our Customer
Table every day with new customers. I want to update the Licenses table as
well. If there is a new customer added to the Customer table I want to run
an append or update query that will check the CustID values that are already
in this Licenses table and add the new ones. Can someone help me with how to
do this?

Thanks,

 

[Guest]

I think you could use an Appendquery. If the ID is a key field you should
normally get a message indicating that x customerID's couldn't be appended
because of key violation. That's exactly what you need because at that point
it will append the new ones and leave out the duplicates.

To make it look nice you could surpress the error message by setting the
warnings to false and after the append to true again like:

docmd.setwarnings false
docmd.runquery "your append query"
docmd.setwarnings true

but that is if you are running the query from VBA.

hth

 

[John Spencer]

Use an append query. The SQL for this would look something like the
following.

INSERT INTO Licenses (CustID, <Other Fields>)
SELECT C.CustID, <Other Fields>
FROM Customers as C LEFT JOIN Licenses as L
ON C.CustID = L.CustID
WHERE L.CustID is Null


You can use the unmatched query wizard to build a Select query showing the
records that are in customers and now in Licenses. Then you can convert
that query into an append query by selecting Query: Append query from the
menu bar and specifying Licenses as the table to append to. Then all you
need to do is set up the fields you want to append.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..