Swap data between fields in different tables?

[cjmitton]

I have assigned to a contact an email address in a 'contact' table. I then have a table for additional email addresses (some of our contacts have home / work / other email addresses), this works fine with the default address always appears in the contact information and the additional emails appear on a pop up box in a list box.

I now want to be able to swap one of the 'additional' email addresses from the separate table to the contact table and move the old (default contact) address to the additional address (i.e. swap them around).

Each additional email address has a unique ID and the contacts have an unique ID too. My guess is that (using VBA) I open each recordset then update the relevent field, but do I have to create a temporary table or can I just pick the values (as variables) then update?

I've tried to search through the posts here and do a google but I'm getting no where as I think my terminology is off!

 

[boblarson]

They should all be in the same table and just have a yes/no to denote their primary email address.

 

[Beetle]

The more appropriate solution would be to keep all e-mail addresses in the EMail table with a boolean (Yes/No) field indicating which one is the default. Then changing the default is a simple matter of updating the default field.

If you need a record set that returns Customer info including only their default e-mail address, you create a query of the two tables with criteria that restricts it to only the default e-mail address, and use this query wherever you would have otherwise used the table you have now.

 

[cjmitton]

I was going to do that but sadly one of our contacts has 4 addresses? and quite a few with 3 addresses. This was the only flexible way I could think of covering all bases.

 

[boblarson]

I was going to do that but sadly one of our contacts has 4 addresses? and quite a few with 3 addresses. This was the only flexible way I could think of covering all bases.

What do you mean that it is the only way you could think of covering all bases. You should be fine with something like:

tblAddresses
AddressID
ContactID
AddressLine1
AddressLine2
AddressLine3
AddressLine4
City
StateOrRegion
PostalCode
PrimaryAddress


So, then you can have 3 addresses for some because the AddressLine fields are NOT for full addresses. It is just for if they have

Bill Jones
The Crazy Company
MailStop x0-2
Routing Code 4
Department 17
Big Town, NY 13112

But you can add as many records for them as you like. So if they have a business address, an alternate business address, a holiday address, a home address, a cabin address, another business address, etc. you can put them all in. No Problem...

 

[cjmitton]

Beetle & Bob, thanks for that but sadly I have it coded now and a 'pilot' scheme running with the app. I thought the easier solution would be to add a button to swap the data around. I could then look at changing it in Version 2... I'm on a dead line to get it live and it would take a long time for me to sort out.

 

[cjmitton]

Sorry bob my last post mislead you slightly, its not full addresses I'm talking purely email address assigned to one contact with one postal address. i.e.
I have my home email address (as my default) held in the same record in the contact table (me@home.co.uk) then I have in the additionalEmail table my works emailaddress of: me@work.co.uk then a generic email address like: me@someotheraddress.co.uk again in the additionalEmail Table. I then want to swap my work email address with my home email address so the work email address becomes the default.

Sorry for the confusion, I take on board my error in the initial design and will try to sort that out in v2!

 

[boblarson]

Email addresses would work just the same as I've described with regular addresses. How can I picture this for you so you will get it? Okay - pictures -

Here's the form to add as many emails to someone as I want. And the table structure behind it.

 

[cjmitton]

Bob, thanks for the pictures it explains perfectly what you have suggested. I shall try to work this in to V2 of the system.