Update Query from Excel

[Zonie32]

Using MS Excel 2003 and Access 2003 on Windows XP.

I have an Excel file with 2 columns, ClaimNum and QADate. Both columns have data in them.

I have an Access table with the same 2 columns, except the QADate is blank in this table.

I want to create an Update Query to say...Take the QADate from Excel and update the Access table QADate column with it if the claim numbers match.

I have no idea how to do that. Can anyone help? thanks.

 

[PHV]

Create an access table linked to your spreadsheet and then use a simple update query:
UPDATE [your access table] AS A INNER JOIN [your linked table] AS B ON A.ClaimNum=B.ClaimNum
SET A.QADate=B.QADate
WHERE A.QADate Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

You can either link or import the Excel table (File->Get External Data). Once you have done that, you can either update the Access table from the linked table using a query, or substitute the imported table for the existing Access table.

A query might look something like this:

UPDATE NameOfExistingTable 
INNER JOIN NameOfLinkedTable
ON NameOfExistingTable.ClaimNum = NameOfLinkedTable.ClaimNum
SET NameOfExistingTable.QADate = NameOfLinkedTable.QDate