Combine two Fields to One with Multiple Records

[PureCoffee]

I have two cases of this. One with email the other with Category.
What I would like to do is take the following:

Email Email_2
Record--> purecoffe@gmail.com purecoffee@purecoffee.com

(tblemail)
Email EmailType Subscribe (checkbox)
Record-->purecoffee@gmail Personal checked
Record-->purecoffee@pur.. Business Null

Basically this would allow me to have multiple addresses and tied to a tblpeople.

Is this possible? There are 9000 Records and I would rather not have to manually enter them in.

Thank you,
PureCoffee

 

[vbaInet]

Welcome to AWF PureCoffee!

It would depend on how each e-mail address is saved. Are they both on the same record?

Record1__________PersonalEmail___________BusinessEmail

 

[PureCoffee]

Sorry for not clarifying! Yikes!

Yes, a little more background. I am attempting to normalize a "Flat Table". As an example; Record 1 under field "Email" and "Email_2" are filled in with different email addresses.

So they belong to the person and I would rather have it so there is ONE field called Email (I can add type and subscribe later) with a record for each email address. Should they get another email I don't have to keep adding columns (fields such as Email_3 etc) but just have a new record.

Does this help? Thank you for yours!

/purecoffee

 

[vbaInet]

So a separate table to hold e-mail address linked to each customer yes?

 

[PureCoffee]

Yes, this is correct.
Thank you,
PureCoffee

 

[vbaInet]

Looks like you will still need to do some typing in the end, i.e. for e-mail type because there's no way of distinguishing.

I'm guessing your new table is using Customer_ID and E_mail as the Primary keys?

What character separates each e-mail address in a record at the moment and is it ALWAYS that character? For example a space or a semi-colon?

 

[PureCoffee]

Currently the Table is like this:

Email(this is field1) Email_2(this is field2)

purecoffee@gmail.com purecoffee@yahoo.com


Here is what I would like to do:

Email
purecoffee@gmail.com
purecoffee@yahoo.com

Forget about all the other stuff that will be added later. The email table will be linked to the people table so that the emails correspond the the person (in this example PureCoffee). Now imagine 9000 records in the above format. If someone wanted purecoffee@hotmail, then I would have to add another field instead of a record to the tblemail associated with the person.

Thanks,
Steve

There is NOTHING separating the email addresses as they are in separate fields.

 

[vbaInet]

As they are in seperate fields it makes things even easier

It would be a tedious task trying to explain the process, unless you know some VBA?

 

[PureCoffee]

I know some VBA. It doesn't scare me at any rate...

PureCoffee

 

[vbaInet]

Goodie. You would need to use a recordset for the table your importing from (table_A) and use an INSERT statement for the new table (table_B). I'm also guessing there's one customer per record? Here are the steps:

1. Iterate through recordset for table_A
2. Ensure the domain is sorted by Customer ID
3. For each record do this:

CurrentDB.Execute "INSERT INTO table_B ([CustomerID], [email]) VALUES (" & rst![Customer_ID] & ", '" & rst![Email_1] & "');"
     If rst![Email_2] & "" <> "" Then
          CurrentDB.Execute "INSERT INTO table_B ([CustID], [email]) VALUES (" & rst![Customer_ID] & ", '" & rst![Email_2] & "');"
     End If
     rst.MoveNext

Obviously all air code.

Do you get the idea?

 

[PureCoffee]

OH I get it!!! O.k. makes sense. Wow that looks good. I will try it and let you know.
/PureCoffee

 

[vbaInet]

See how you get on.