customer form with notes from a linked table

[dcorleto]

I have a table customers that is created from a linked excel file.
(Last, First, FullName, email, phone)


This xls updates nightly, so every day the table might have different information:
1. New customers added
2. Old customers deleted
3. Edited information within

The file does have a unique field which is the customer email address.
My goal is create something, either a related table or form (not sure what is the right thing do to here) so that I can input additional information about each customer (e.g., notes, start date, department, location) and be able to view/edit that information on a form. I will not need to change any of the xls information, just the extra fields that I want to edit.
What is the best practice for attempting this, and many thanks in advance to anyone who can help -

[June7]

The email is the only unique identifier? People tend to change emails. Why aren't customers assigned a unique ID (CustomerNum) and documented on the Excel?

Yes, use related tables. What about the deleted customers? Do you want to delete all the related records? Shouldn't you import the customer records to Access table for permanent retention?

[dcorleto]

First thank you so much for your reply - yes the email is the only unique field, though it will not chane. While the customers have an ID, they do not appear on the Excel report. I do not want to delete the old customers. I imagine the records should be imported, but how would they get updated thereafter? Are you suggesting that the linked files get an update query each time Access is run and if so, how would I then go about adding the extra fields for input such as notes, etc?

[June7]

Use a find unmatched query to identify records in the Excel not yet in the Access table and then APPEND those records. Use an UPDATE query to edit the existing records.

The 'extra' fields can be in the same table unless you want 1 to many relationships (many NOTE records for each customer). Suggest you input the CustomerID and use that as the pk/fk link to any child tables.

[dcorleto]

Thank you very much for the reply. I would not need many Note Records, just one. So then I would run these queries that you suggest at each startup?

[June7]

Run the sql actions when you see fit. Can be when the db first opens (code behind default open form or maybe an autoexec macro) or with button click event.

[dcorleto]

stuck now on the append part.. I have found the non-matching records and saved that as a query - I now create an Append Query, but when I run it, it wants to import 2000 records instead of only the 17 unmatched - any ideas??

[dcorleto]

I got it - one of the excel tables has many rows of null info, so I added a not is null criteria and now I see only the 17 matching records -