Excel data added to Access 3

[Boomer56]

I have a MS Access table containing loan data. We are collecting pieces of data from various sources, all of which end-up in Excel spreadsheets.

I would like to be able to populate fields in the table, based on the data in the spreadhseets.

For example, in the db, each record now has a LoanNumber, Balance and Name field. The LoanNumber and Name fields contain data, the Balance field is blank.

The spreadsheet has LoanNumber and Balance data.

How can I merge the data from the Excel spreadsheet to the MS Access db, so that the db will have data in all three fields.

Thank you in advance

 

[EliseFreedman]

In Access, create a link to the spreadsheet via File, Get External Data, Link Tables.

Now create a new query and make it an update query. Add your database table and your spreadsheet table into the query creating a join between the two tables via the loan number field.

Now add all three fields from your database table into the grid. Finally Set the Update To row of the Balance Column to [SpreadsheetTableName]![Balance] and run the query

HTH

Elise

 

[Ecniv]

Or import the spreadsheet first then use the imported table to update the table already there...

Vince

 

[Boomer56]

Thanks. This was helpful in getting me headed in the right direction.

 

[PROXI]

with the method that is discussed above... will it add the records from the excel sheet to the access database if they don't exist yet, or will it just add data to existing records?

Thanks,

Shadd Parker

 

[Boomer56]

Thought this one was dead! Glad to help...

Based on the path I followed, it would depend on the type of action query you use. An update query makes global changes to a group of records in one or more tables. It will point to the tables you assign in the design mode. An append query adds a group of records from one or more tables to the end of one or more tables.

 

[uscitizen]

I'd like to use the 'Get External Data' command on the 'File' menu but it's GONE. What'd I do and how do I restore it?

Thanks for any tips.

 

[davecar]

There is also a TransferSpreadsheet Action (VB) which allows you to imiport spreadsheet data with defined columns in to a specified table. I have used it sucessfully. Look in Access help and explains it well.

 

[uscitizen]

thank you davecar.