Two tables with data that do not match | Solutions?

[Onestar]

Hello All,

I've been trying to learn Access lately and have come across a few different scenarios where I am slightly confused. Maybe someone can clear it up for me?

Problem: There are 2 tables:
Table 1 has National Insurance Number, first name, last name, phone no, address.
Table 2: has National Insurance Number and email address.

Table 1 is the master table where it will link to table 2 containing the email addresses of the individuals.

However, there will be a lot of email addresses in table 2 which do not relate to any record in table 1 because I do not have the individual in my database yet.
BUT I want to keep their email address because in the future this person may enlist in my imaginary business and therefore - if they do I would have their email address!

1. Does anyone know if this is possible in Access?

In Excel, it would be the case of a simple vlookup from the email address field into another sheet containing the National Insurance Number and email address.

2. Would it be possible to establish a one-one relationship while enforcing referential integrity?

3. Is there a way I can establish a lookup which can be built into table 1 which can lookup the email address of a person in table 2, matching on National Insurance Numbers?
-so in the future if new data is input into table 1 or 2 which results in a match of National Insurance ID numbers, the outcome would mean an email address is now paired to the corresponding individual it belongs to.

Any help on this would be really appreciated!
I have been trying to rack my brains but have had no success yet.

 

[RuralGuy]

You should probably study up on the Domain functions: DLookup() etc.
http://access.mvps.org/access/general/gen0018.htm

 

[plog]

Vlookups are basically simple queries (just done in Excel). So if its simple in Excel with a Vlookup it is even simpler to do in Access with just a query.

You will need a LEFT JOIN (http://www.w3schools.com/sql/sql_join_left.asp) query that looks at all the data in Table 1 and brings in any data that matches from Table 2.

I'd read through a few query tutorials. Advanced users of Excel usually have trouble stepping into Access because its a different mindset. What you want to accomplish is simple in both Excel and Access, but they are simple in different ways so it might be hard to grasp at first.

 

[Onestar]

Thanks guys!!

Am I correct in assuming you cannot enforce referential integrity when establishing a relationship between two tables when there is some data in Table A that Table B does not have and vice versa?

How would you recommend I approach a simple problem as:
Where Table A has data that Table B does not have;
and where Table B has data that Table A does not have

 

[RuralGuy]

Am I correct in assuming you cannot enforce referential integrity when establishing a relationship between two tables when there is some data in Table A that Table B does not have and vice versa?

You are correct.

 

[plog]

How would you recommend I approach a simple problem as:
Where Table A has data that Table B does not have;
and where Table B has data that Table A does not have

You gave us a starting point but not a destination. I'd need to know what your ultimate goal is with the data to advise you what path to take to get there.

 

[Onestar]

Table A: Social security number (PK), first name, last name, address.

Table B: Social security number (PK), email address

The issue is that I have two tables with records in Table A with no relationship to Table B? e.g. the PK 111 in Table A does not have a corresponding PK 111 in Table B.

However, say in the future, when I source more data, I may come across this PK 111 and add it to Table B. Therefore, the PK 111 in Table A would now have a corresponding PK 111 in Table B.

Do you know a relationship / method that can be built into Access to incorporate this kind of scenario?

Many Thanks

 

[plog]

Your still not giving me what you want. You provided me a scenario, but not what the end result or goal was. Here's where we are:

You have 2 data sources, A and B. They intersect, but neither is a proper subset of the other.

Explain to me in english what you want. No more database jargon, if you must, set theory terms (http://en.wikipedia.org/wiki/Set_theory) are fine.

Do you want to create Z such that every distinct item in A and B is in Z? Or maybe Y which contians all elements in A and matching data in B? X would be the disjoint elements (elements in A or B, but not A and B).

What do you want? How do you intend to use it? For what purpose?

 

[Onestar]

Hi Plog, thanks for not giving up on me yet!

Purpose: keep a clean database of students attending a college.

Basically, the end outcome is to have a nice form which will display the fields: Social security number (PK), first name, last name, address and email address.

As students enter the college their main details will be recorded into table A, however the field (email address) may be left blank.

People will go out sourcing email addresses from students and therefore their email addresses will be put into Table B.

I need to find a solution whereby I can link these records via a PK (social security number).

But the issue is that there would be X (disjoint elements) which would create the issue of a violation of referential integrity. I need to find a solution to establish a relationship - where I can run a query and pick up all the available email addresses from table B and match them to the records in Table A (where email address cells are blank).

The reason for keeping a table of email addresses separate is that email addresses will be added, modified and deleted constantly- so I think it will be better to keep them in a separate table.

 

[plog]

You're killing me with jargon. I get it, you read a book on databases. But in solving this issue your words are getting in the way of your ideas.

1. It sounds like A is the main table. If someone is in B but not A, you don't care about that data. Right?

2. How does data get into A and B? It sounds like users directly enter data into A but B is like a mailing list you buy and import.

Honestly, it sounds like you just need a LEFT JOIN query like I initially mentioned.

 

[Onestar]

1. It sounds like A is the main table. If someone is in B but not A, you don't care about that data. Right?]

Yes A is the main table. Yes absolutely right don't care if someone is in B but not in A and vice versa.

2. How does data get into A and B? It sounds like users directly enter data into A but B is like a mailing list you buy and import.

Yes you could say that!

I think you're right on the left join query!! I'm going to research it properly tomorrow