Append and Update an access table importing from excel

[AccessHunter]

Please advice me on this.
I need to create a new access database that will used to collect the employee hours from various locations. Every friday supervisors from various locations will be sending a spreadsheet that has the following fields;Agency ID,Name,In Date,Out Date, Feb1 week,Feb2 week..until Jun5 week.Data from these spreadsheets needs to be imported and appended/updated to a master table in the access database.You have to keep in mind when the spreadsheets that come with feb2 week data can have updated or new data in any of the columns until 'Feb2 week'. I know how to import and load data into an access table from a spreadsheet. By issue here is how to update/append all the rows/columns in the table every week.

PLease help.

Thanks in advance

[DonRayner]

How about importing your spreadsheet to a temp table and then using an update query to update/append the main table. Here is a SQL example that would Update/Append From Table1a to Table1b.

sqlString = "UPDATE Table1b " & _
"RIGHT JOIN Table1a ON Table1b.Field1 = Table1a.Field1 " & _
"SET Table1b.Field1 = [Table1a]![Field1], Table1b.Field2 = [Table1a]![Field2], Table1b.Field3 = [Table1a]![Field3];"

DoCmd.RunSQL sqlString

[NeoPa]

It's not a design I'd advise, but if that's what you have to work with then the concept would be to :

1. Import data into an intermediate table (can be created on the fly if preferred, or re-use the same one over again).
2. Run an update query on your main table with an INNER JOIN betwen matching records (You don't indicate what a match is so you'll have to manage those details yourself).
3. Run an append query where the key of the intermediate table is LEFT JOINed to that of the main table, and the WHERE clause only allows those records with a Null key in the main table to be processed.
4. Clear down the intermediate table for next time. This will mean either clearing all the records or deleting the table, depending on your choice in step 1 above.