By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

import into access using some king of mapping of fields

P: 1

I am fairly new at Access and used to use Lotus Approach. There was one function in Approach I used to find very useful and I can't seem to find it in Access.

It is to do with importing records into an existing table. Rather than automatically add to the list of records I have in my already existing table in Access, I would like to update (and add if necessary).

For instance: I may have a table in access with fields such as first name, last name, address1, address 2, town, postal code, etc.... Say the field "postal code" is empty in my access table. But I have an xl spreadsheet with the same field name. In that xl file, there is a record that has the same first name, last name and address 1, but it also has something in the postal code field.

How can I tell access to add the content of the field "postal code" in xl to my already existing table in access in the already existing record in the field called "postal code"?

Is there any way I can associate a mapping criteria to access when it does the import?

I hope this is clear and I thank you in advance.

Oct 2 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 2.5K+
P: 3,072
For this you can use a workaround. Just link or load the xl file into a temp table.
Next create a UNION query like:
Expand|Select|Wrap|Line Numbers
  1. select * from tblOriginal
  2. UNION
  3. select * from xl
Now use this UNION in a groupby query and use for the "unique" fields (firstname, lastname, etc.) the GroupBy option and for the "non-unique" fields (like Zipcode meail, etc.) the Max option from the grouping combobox in the graphical query editor.
Save this query and use it to create the final table with a CreateTable query.

Idea ?

Oct 6 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.