Update joined tables with info that is currently in Excel?

  • Thread starter Thread starter BobC
  • Start date Start date
B

BobC

I have about 9000 records in an Excel spreadsheet.
Each Record contains 8 fields.
3 of the fields will uniquely reference the specific Location,Site and
Item that I want to update.
The other 5 fields contain the information that I want to update
(replace existing table data).
I can rename the fields in the Excel spreadsheet to match the Access
2007 fields if that helps?
I'm thinking of inporting the ~9000 records into a new Access table and
using that to update the existing 4 joined tables?
I am new at using update queries.

I am looking for recommended approaches?

Thanks,
Bob
 
Missing somethis --
about 9000 records
8 fields.
3 of the fields
5 fields
~9000 records
4 joined tables

What are the 4 tables? What are the relationship?
Where is the new information to update the records to come from?
 
The 4 Access 2007 Tables contain the data to be updated.
The 4 Tables are joined '1 to many'.
The information to update the 4 Tables is coming from the 8 fields on
the Excel spreadsheet.
Of the 8 fields of data, 3 of them (Location,Site and Item) will
uniquely define which Access record needs to be updated.

Thanks for tying to help!
Bob
 
Use the Get External Data and link the Excel file. You will need to run
either 4 queries or the same query 4 times but editing it for each table.
UPDATE [Excel] LEFT JOIN [Table1] ON [Excel].[Location] =
[Table1].[Location] AND [Table1] ON [Excel].[Site] = [Table1].[Site] AND
[Table1] ON [Excel].[Item] = [Table1].[Item] SET [Table1].[4thField] =
[Excel]+[4thCol], [Table1].[5thField] = [Excel]+[5thCol],[Table1].[6thField]
= [Excel]+[6thCol],[Table1].[7thField] = [Excel]+[7thCol],[Table1].[8thField]
= [Excel]+[8thCol];
 
I did backup! ... 2 copies.

Meanwhile, I tried something that seemed to have worked.

I Imported the data to a MS ACCESS file.
In Access, I assembled an update query with the 4 tables linked as they
normally are.
I added the newly created (imported) table and linked it (3 links)to the
original 4 tables.
I added 5 'Updates to' for the fields I wanted to update (Update to:)

When I ran the update query it seemed to work fine. I am checking the
results as we speak.
 
Back
Top