Join , merge , update 2 tables imported from excel - help please

[tur13o]

Read many posts and get the idea of full outer joins ( using union in
access ) but I can't find a post that helps me...

I have a master table in excel with about 2000 rows and 20 columns.
The first column includes (key) text that is unique to each row.

A copy of the table is e-mailed to people to update. They find their
rows (by the key) and update the data in the other columns and send
the table back to me to be consolidated back in to the master table.
(I don't know which rows they own as the owners change from month to
month so I have to send them all the whole table)

The unique identifier is never changed, column names never change, but
they may submit new rows with new unique identifiers in the first
column.

Is there a way to automate the consolidation in Access? Consolidating
the responses one at a time would be ok.

I'll accept that if two people updated the same row in their
respective copies then the last one to be merged would prevail.

Thanks in advance.

p.s. I noticed that Access 2007 can collect data via e-mail but it
mentions that the users have to have Outlook 2007 and InfoPath 2007
which is not the case so I don't think this option will help.

 

[KARL DEWEY]

The way I see it you will need to run two queries for each return.
First make sure the unique identifier that is never changed is set as a
unique index, no duplicates.
Always do a backup of the database.
First query is an append query to add any new records.
Second query use a calculated field for comparrison like this --
MyCheck: [Field1] & [Field2] & [Field3] & [Field4] & ... & [Field19] &
[Field20]
and as criteria use --
[ImportData].[Field1] & [ImportData].[Field2] & [ImportData].[Field3] &
[ImportData].[Field4] & ... & [ImportData].[Field19] & [ImportData].[Field20]

Set the update query to update all fields except of course the unique
identifier that is never changed.