Question Linking Excel and Access files

[DJ-Specter]

Hi, my client has two databases, one smaller one saved as an access database with more complete details of clients and a much larger one saved as an excel spreadsheet with many incomplete entries but many more fields.

I need to create a system whereby upon entering a new user in the smaller database, the system searches the first and last name of the record in the larger database and fills in any existing information contained in it and inserts it into the relevant fields in the smaller database.

Any ideas are hugely welcomed!

Thanks

 

[namliam]

link the excel sheet as a table and do a search on it, sounds pretty simple.

 

[DJ-Specter]

link the excel sheet as a table and do a search on it, sounds pretty simple.

When you say link them, would a relationship with the update option enabled between first and last names on both be what you mean?

Also, would both tables have to have exactly the same fields in them for it to be able to retrieve all the relevant information or would I have to make a relationship between each similar field i.e. birth date to birth date, ethinicity to ethinicity etc?

Thanks alot

 

[DCrake]

When you link a spreadsheet it uses the first row as column headings. If the column headings match your small table then use can use a simple append/update query. What you mainly need is a unique identifier that appears in both the Access table and the Excel spreadsheet that links the two together.

Are you going to have:

1. Records in Access that don't appear in Excel
2. Records in Excel that don't exist in Access

Are you asuming that Excel is more up to date than Access for matching fields


David

 

[DJ-Specter]

When you link a spreadsheet it uses the first row as column headings. If the column headings match your small table then use can use a simple append/update query. What you mainly need is a unique identifier that appears in both the Access table and the Excel spreadsheet that links the two together.

Are you going to have:

1. Records in Access that don't appear in Excel
2. Records in Excel that don't exist in Access

Are you asuming that Excel is more up to date than Access for matching fields


David

I thought it would make things easier if i moved turned the spreadsheet into an access table. aside from first and last names I cant think of anything else i could use as a unique field as if i insert an autonumber field in the old spreadsheet table, as some of the records in the spreadsheet table dont dont appear in the shorter table and visa versa they almost certainly wouldn't get given the same unique number. does this help? thanks

 

[DCrake]

What is the likelyhood of two or more people sharing the same name?