newbie: how to determine dupe/updated records from import

[Andrew L.]

I want to base my my db around one main table which is imported from
an xls file. Frequently, new records are added to xls, but there are
also modified records (changes in one or more fields). How can I
easily determine which records in my updated table are the new,
modified ones, and then automate deleting the older, outdated records?

I don't want to constantly be creating new tables, as I want some
forms and queries based on that main table to remain working.

Hope that makes sense.

Thanks,
Andrew L.

 

[John Vinson]

I want to base my my db around one main table which is imported from
an xls file. Frequently, new records are added to xls, but there are
also modified records (changes in one or more fields). How can I
easily determine which records in my updated table are the new,
modified ones, and then automate deleting the older, outdated records?

I don't want to constantly be creating new tables, as I want some
forms and queries based on that main table to remain working.

If the Excel spreadsheets are the "master" data and can be counted on
to be accurate, consider not importing them at all: you can use
File... Get External Data... Link to simply link to them, and they'll
be treated as a table. Or, you can run a Delete query to empty your
table, and then import into the existing table (rather than building a
new one).

To identify modified records you'll need a fairly complex query
joining your local Access table to the (linked or imported)
spreadsheet by its Primary Key, with criteria on each field

<> [linkedtable].[fieldname]

These should all be on separate lines so it uses OR logic. These will
be the changed records.

John W. Vinson[MVP]