Update Query

[katjoe]

Hi,
Looking for help with update query.
A bit of background:

I created a database with three tables that I have imported three tabs from an Excel spreadsheet.
I got it to import the files and then move the file to another folder. All works well!

Now, if one of the files that has already been imported was updated with new data I do not want to import the data instead I would like to update the record that matches the file name.

Not sure which is the best way to go about this but I would like the file to either update the data in the table such as if …THEN data updated else import the data.
I created a table which holds the file names and so I am not sure which is best to use.
Option 1: create a “if/then” condition and if it meets the criteria then update the data or if not then import the data into the table.
But not sure how to create this condition (WKS is an object). I did begin the condition with:
#


IF (WKS.name = “table1.file Name.values” = filename) THEN
strSQL = “UPDATE [table1[ SET [ID] = “” WHERE [File Name] = ‘ ” & filename & “ ‘;” (filename is a variable that holds the name of the Excel file)
#
Access did not seem to like this.

Option2:
Create and update statement but the problem is that I do not know the ID for the particular row since there are about 300 records in the spreadsheet.
I thought of using a select statement within the Update statement that I have listed below but I get a syntax error.
#
strSQL = “UPDATE [table1] SET (SELECT * FROM [table1] WHERE File Name = ‘ “ & filename & “ ‘) ;”
#

I have search for ideas but only get part answers. There is so many ways but not able to find anything close. Can someone please help?

Many thanks in advance!!

[madpiet]

"Now, if one of the files that has already been imported was updated with new data I do not want to import the data instead I would like to update the record that matches the file name."

I'd look up UPSERT...
basically, you'd use a join to find the records that are / are not in the table already.
For those that are already in the table, execute the UPDATE statement.
For those that are not, execute the INSERT statement.

So you could break it into two pieces:'
Execute Update statement (with filters).
Execute Insert statement (with filters).

In T-SQL you can do WHEN NOT MATCHED, but this isn't TSQL... (it's just that Access forces you to use VBA for almost everything when you handle "exceptions")

Since that's not possible in Access, you write two queries.

The Update joins the two tables (INNER JOIN).

UPDATE TableA
FROM TableA INNER JOIN TableB ON TableA.Key = TableB.Field

(so the join filters out any key values that don't appear in both tables.)

then you do an insert of the rows that are in B but not in A.

INSERT INTO TableA (col1, col2, col3)
SELECT col1, col2, col3
FROM TableB LEFT JOIN TableA ON TableB.Col1=TableA.Col1
WHERE TableA.col1 IS NULL

[mike60smart]

Hi
Are you able to upload a copy of the Database with no confidential data together with a copy of the 3 excel files?