Importing Data into Relational Table

[Scott Huish]

Please bear with me, my Access knowledge is very limited right now.

I have a database setup and the relation is set from the Automatic ID # from the main table and then this is referred to as CustomerID in the related tables.

However, I am importing data from Excel spreadsheets into this one.

The main table has the email address of the customer, but the related tables do not.

The data I need to import has the email address but not of course the Access generated ID #. How do I move this data?

Thanks.

 

[Joe4]

I am having a little difficulty visualizing the issue.

What is the structure of the tables in question?
What does the data you are trying to import look like?

 

[Scott Huish]

The Main Customer Table is:
ID - AutoNumber
Email - Short Text
Source - Short Text
FirstName - Short Text
LastName - Short Text
Company - Short Text
Address- Short Text
Address2- Short Text
City- Short Text
State- Short Text
ZipCode- Short Text
Gender- Short Text
Industry - Number
job_title - Number
company_size - Number
Phone - Short Text
UserName - Short Text
TermDate - Date/Time
PubStatus - Short Text

The table I need to also import data to is:
ID-AutoNumber
CustomerID-Number
Event_Date - Date/Time
Event - Short Text
Action - Number

ID from the main table is related to CustomerID in the second table.
But in my source data, the ID # is not present, that only exists as generated by Access.

Now the second piece of data after their main customer information, which is what I'm trying to get in the 2nd Table (~Customer_Actions) looks like this:
<b>2import</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:90px;" /><col style="width:235px;" /><col style="width:128px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td ></td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >ID</td><td >CustomerID</td><td >Email</td><td >Event_Date</td><td >Event</td><td >Action</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td ></td><td ></td><td style="color:#0000ff; text-decoration:underline; ">yes@no.com</td><td style="text-align:right; ">8/16/2011</td><td >TestEvent</td><td >Attendee</td></tr></table>

Now there is no CustomerID in this table as that was generated by bringing data into the main customer table. How do I tie these together. I did try creating a second relationship on email address but that didn't work.
I also am thinking that if I used email address as the primary key, the link would break between the tables if the email address changed

I seriously hope this make sense.

 

[Joe4]

Sorry, I have been away for a few days and am just getting back.

I see your dilemma. That is a tricky one. I am not sure I know the best answer to this one. Here is how I would try to approach it.

If you all the record you are importing have email addresses that match something in your main Customer Table, and if you email addresses are unique in both tables, I would say go ahead and use that as the Primary Key field in your Event table (and get rid of the Customer ID field from the table altogether). Otherwise, you might have to try something a little different, like importing to a temporary table, look up the Customer ID, and writing to your Event table (but if your email addresses are not unique, that is going to problematic anyway).

I usually don't come across situations like this, because we usually do not an Autonumber field as the main Company/Customer Identifier. We usually assign a unique code, and then make whoever is sending us data always use that code. Then you never have to worry about issues like that. Don't know if that is a possibility.

 

[Scott Huish]

I wish it was but the information is coming from email lists from a commercial email marketing company we use and a event registration company we use. The only thing that is consistent is the email address and the only thing that is uniquely exportable from both systems.

The email address would be unique in the main Customer table, but would appear multiple times in the Event table for every event they had anything to do with.

 

[Joe4]

The email address would be unique in the main Customer table, but would appear multiple times in the Event table for every event they had anything to do with.

That should be OK then, as long as it is unique in your Event table. I would try getting rid of the CustomerID field in the Event table, and use email address as the field you link the two tables on. You can leave the Autonumber ID field in the Event table, just so you have a unique ID for each record.

See if that works for you.

 

[Scott Huish]

OK. Thanks!
If a customer updates their email address, is there any way to replace it across all tables?

 

[Joe4]

You can set it to Cascade updates, so if you make a change in the Customer table, it automatically updates all the affected records in the Events table.

You can do this by doing the following:
1. First, go into the Customer table and set the Indexed property on the Email Address field to be "Yes (No Duplicates)
2. Then close the table and go to the Database Tools menu and click on Relationships
3. Add the Customer and Event tables
4. Create a relationship between the Email Address in both tables (by clicking on the Email Address field in one table and dragging and dropping on top of the Email Address field in the other table)
5. In the "Edit Relationships" box that pops up, check all three check boxes (Enforce Referential Integrity, Cascade Update Related Fields, Cascade Delete Related Fields)
6. Click the "Create" button
7. Close out of Relationships

Now, whenever you edit an email address in your Customer table, it will automatically update all related records in your Events table.

Note that if you try to import a record in your Events table where the Email Address does not exist in your Customer table, it will not let you do it, and tell you that you need a related record in your Customer table.