import into access using some king of mapping of fields

[EricEAS]

Hi.

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.

Eric

[nico5038]

For this you can use a workaround. Just link or load the xl file into a temp table.
Next create a UNION query like:

select * from tblOriginal
UNION
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 ?

Nic;o)