If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update joined tables with info that is currently in Excel?
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 |
#2
|
|||
|
|||
Update joined tables with info that is currently in Excel?
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? -- Build a little, test a little. "BobC" wrote: 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 . |
#3
|
|||
|
|||
Update joined tables with info that is currently in Excel?
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 KARL DEWEY wrote: 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? |
#4
|
|||
|
|||
Update joined tables with info that is currently in Excel?
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]; -- Build a little, test a little. "BobC" wrote: 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 KARL DEWEY wrote: 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? . |
#5
|
|||
|
|||
Update joined tables with info that is currently in Excel?
BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
BACKUP DATABASE -- Build a little, test a little. "BobC" wrote: 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 KARL DEWEY wrote: 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? . |
#6
|
|||
|
|||
Update joined tables with info that is currently in Excel?
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. KARL DEWEY wrote: BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE |
Thread Tools | |
Display Modes | |
|
|