Creating a design with 3 identifiers

[ant123]

Hi,

I was wondering if someone could help me?

I have a database with 3 tables. In each table, columns A, B & C contain idenitifers and Columns D.... etc... contain other information unique to that table. A, B and C must be used together to identify a unique record. Column A is the main identifier i.e. unique to each person (but can be reapeated in any table), and column B & C are secondary identifiers (but are not unique to each person).

e.g.

Column A Column B Column C

1 M65 D52
2 M75 D52
3 M89 D89
1 M89 D01
5 M65 D23

However, not all unique records are in every table (e.g. 1-M65-D52 might be in table 1 and 2 but not in 3)

I need to create relationships between the identifiers in A, B and C so that a query will search always using these identifiers together. Please could someone let me know how to do this?

So far I have created a 4th table that contains all possible Column A identifiers (without duplicates). I have linked this to all other tables



e.g. Table 4, Colulm A - Table 1, Column A
Table 4, Colulm A - Table 2, Column A
Table 4, Colulm A - Table 3, Column A

but I don't know how to linked Column B and C identifiers in??

Thank you :-)

[orange]

I suggest you step back and describe to readers WHAT you are trying to accomplish. Use simple, plain English to describe "the business issue/opportunity". Forget tables and columns for the moment. Once readers understand WHAT you are trying to do, I'm sure some options/advice/suggestions will be posted.

Just curious --why did you opt for 64bit Access?

[ant123]

OK sorry for the confusion! I have 3 sets of data: inpatients, A&E and transfers. A patient is indentified via 3 identifiers - A, B, & C.

There are several scenerios for the patient:

1. Attend A&E and leave - in which case an A, B and C will be created for the A&E table along with speicifc information about that attendance
2. Attend A&E and be admitted as an inpatient - in which case the same A, B & C would be in the emergency and inpatient tables
3. Attend A&E, be admitted as an inpatient and get transferred through several wards - in which case the the same A, B & C would be in the emergency, inpatient and transfers tables
4. Be admitted OR admitted and transferred without attending A&E

Each patient journey i.e. one of the above, is identifed by a unique 'patient number' (A) and two more idenitifers (B) and (C) which denote the 'journey'. The time period of the data set is a year so the patient could have several journeys ('A' could therefore be repeated numerous times in the tables but the corresponding 'B' and 'C' would be unique.

However, although 'A' is unique to the patient, 'B' and 'C' are not.

I would like to pull out different bits of information from each table - however when I created the query I keep getting duplicate/mixed up records (i.e. the A&E data for one journey is joined horizontally for the inpatient data for another). I think this might be because my tables are not linked in the correct way.

I hope this helps explain abit further - please let me know if you need more details.

[ant123]

Also, I am not sure where I have 32bit or 64bit Access

[orange]

No --farther back yet.

You deal with people/patients. What's an A&E? You mention inpatient so I'm thinking some sort of facility/hospital??
Readers only know what you tell them, so make it as simple as possible. Once they understand, they will ask more focused questions if required.
Pretend you're in the line at McDonalds; the person behind you - who doesn't know you, nor Access, nor database at all - says
a) what business are you in, and
b) what are you working on.

And your response....

[rpeare]

I'm guessing he wants to view ALL patients, regardless of which table they appear in and show their data accordingly.

The only way I can think to accomplish that (if you don't have a 'master' list of client identifiers) is to create an aggregate union query of all the identifiers and then use that to pull data from the tables as desired.

something like

SELECT ColA, ColB, ColC FROM (SELECT ColA, ColB, ColC FROM TableA
UNION ALL
SELECT ColA, ColB, ColC  FROM TableB
UNION ALL 
SELECT ColA, ColB, ColC  FROM TableC) GROUP BY ColA, ColB, ColC

This would give a complete list of clients then you can link this query to any of your tables and retrieve the values you want.

[ssanfu]

I'm kinda curious what the table structure/relationships look like.

[rpeare]

me too, I was just assuming it was external data coming to him and he didn't have direct access to the tables themselves.

[ant123]

Hi All, Please accept my appologies for not replying sooner. I'm afraid I have very little Access experience so I have now got a friend to go through some of it with me. I will make sure my questions are abit more specific next time! Thanks for trying to help!! :-)

[orange]

Glad you're back.

This was my previous question to you.

Pretend you're in the line at McDonalds; the person behind you - who doesn't know you, nor Access, nor database at all - says
a) what business are you in, and
b) what are you working on.
What do you say?