Linking data between two tables

[cardinal]

Hi All,

Access newb-ish, I've worked with the basics for several years but recently got upgraded to 2007 so I'm having to re-learn some stuff and learn more about creating a database now that I'm needing to do that instead of just enter data and run queries!

Apologies in advance for what I'm sure is a very simple issue - I recently imported an excel file into access. Let's say it contained data for both domestic and international customers. With different things needed from each type of customer, once in Access, it became needed and easier to create separate tables- one for international and one for domestic. However, each customer needs their own unique id - and I need a way to link or relate the tables in a manner that it will not allow duplicate customer numbers between the two tables.

Any input is greatly appreciated!

 

[Beetle]

Can you be more specific? What do you mean by "different things needed from each type of customer"? Do you mean they have completely different sets of attributes? I would think that they would have at least several in common like;

Customer Name
Address
City
State / Province
Country
Postal Code
Telephone
etc.

We need a bit more information in order to offer you correct advice.

 

[cardinal]

Each table contains nearly identical fields, but there are issues such as allowing people access to only domestic information that require the tables to be separate. Previously, my primary key was the customer number. That is still the case once international and domestic were split, but I just need to be sure that when, for instance, I create a new domestic customer, that I am not duplicating an existing international customer number.

 

[Beetle]

there are issues such as allowing people access to only domestic information that require the tables to be separate.

Are you talking about restricting users of the database to only domestic or international customer information? You certainly don't need (nor would I recommend) separate tables for that. You could just have a boolean (Yes/No) field, or maybe just use the postal code field, to determine if a customer is domestic or international, then use a query to restrict which records are returned based on this field. That query would then be the record source of your data entry forms and reports.

 

[cardinal]

There are multiple reasons that they need to be separate. I just need to know if/how I can ensure that a customer ID entered into one table is not duplicating a customer ID in the other.

 

[kipcliff]

Since you are insisting on keeping your tables separate, you can create a third table as the parent table for the customer ID, and join it in a 1-to-1 relationship with each of your two customer tables.

 

[cardinal]

Since you are insisting on keeping your tables separate, you can create a third table as the parent table for the customer ID, and join it in a 1-to-1 relationship with each of your two customer tables.

Yep, I was able to talk to my husband (computer engineer) after work and get some input on this. He suggested the same thing. Think I have found what I was after, thank you for your input.

 

[kipcliff]

Glad to hear it.