Updating Data from a spreadsheet

  • Thread starter Thread starter Raj
  • Start date Start date
R

Raj

Hi! I have a spreadsheet from which I get my data to be
added in the datatable(in access). I imported the
spreadsheet to access to make a table.
Now the problem is that new records are added to the
spreadsheet everyday. That means I need to add those
records to my datatable too.
How can I add those records without duplicating the
records in my datatable.
What I did was I made a link table, linking it to the
spreadsheet and then I made an append query between the
linked table and the datatable which added the records to
the datatable.But with this the previous records which are
already there in the datatable gets added twice.
Please advice what should I do. The datatable has 8 fields
and it has an auto number field too. Thank you for any
help I can get.
Raj
 
Hi Raj,

The way I've done this is two create and unmatched query (not using the
wizard) and then use the result of that query to append to the main table.

In the first query, link all the related fields between your spreadsheet
table and your main table using the left join (all in spreadsheet and only
matching in main). Then, take any of the fields in the main table that
consistently contain data (no null values) and remove the "Show" check and
set the criteria to 'Is Null'. This should give you all the records in the
spreadsheet that are not in the main table.

Finally, use the results of the unmatched query to append the data to the
main table. You may also want to follow up with an update query to make
sure any changes in the spreadsheet are reflected in the main table.

Hope this helps,
- Glen
 
Back
Top