Multiple tables same field

[gilmania]

Hi everyone,

I'm a beginner and have what is probably a really stupid question. So I inherited a database from the person whose position I filled, so the database already has a good deal of tables. There is a code which identifies every person in this database (I'm in medical research), but I can't figure out how to have it show up in a table.



There is a table that I would like to have display this code, but I don't know how to link this code as it appears everywhere else in the database to this particular table. Nothing I seem to be doing makes it work at all.

I know this is probably a far cry away from being informative enough, but I don't know what details to include since I'm so new to Access.

Thanks for your patience...

[weekend00]

Not sure what's in you database or what you try to do.

[gilmania]

Not sure what's in you database or what you try to do.

I don't know if I can really say what is in it...

I'll try to describe it. The top table is the information for a patient, like name and demographics, where they are assigned a code. When a particular test is done for this patient, which is placed in a new table, the link between the two tables is the common code. My problem is that a third table is a sub-test of this test, and I want to again link it's results via this code. I can't figure out how to get this code to appear here again.

The common factor between table 2 and 3 is a sequential number that just says what number that test was (i.e. the 100th test). This number is meaningless to me, I want the link between all tables to be this code, which is listed firstly in table 1.

[weekend00]

I guess ( just guess) this code is the id for a patient.

do you have the sub-test table existing or you want to create a table?
if it's existing table, there should be a code(not the patientID, but the id for a test) to link to the test table, may or may not have a code as the patient ID. you don't need to add the paitient ID if it's not there.

[gilmania]

I guess ( just guess) this code is the id for a patient.

do you have the sub-test table existing or you want to create a table?
if it's existing table, there should be a code(not the patientID, but the id for a test) to link to the test table, may or may not have a code as the patient ID. you don't need to add the paitient ID if it's not there.

The table exists, and yes it is a patientID code that is used throughout the data. There is an ID for the test and subtest, but I really don't give a damn about those because they are just sequentially entered and have next to no value to me. I want the patient ID to appear on the sub-test form as well. It's waaaayyy too roundabout to have to open up the test table to find the matching test ID so I can finally know the patient ID when using the sub test table. That's just way too much redundancy.

[weekend00]

There is no sense to link the sub-test direct with patient table without link to test table.

but if you do want patient ID in sub-test table, just add it and populate it using a query like this:

update sub_test inner join test on sub_test.testID=test.testID set sub_test.patientID=test.patientID

[gilmania]

How does that not make sense?

What is a valid reason to NOT have the patient ID in that sub test table?

If you had data on someone, wouldn't you want a clear and easy way to know who that data was for?

For example, if you had a table that listed phone numbers, wouldn't you want to know the names of the people that had those numbers? The way it's set now is that it just counts what number that is in your list i.e. phone number 200 of 300 phone numbers on record.

If the way you described being able to do that in access is the simplest way, then there is something horribly wrong with access. This should be such an easy, simple, and less than a second task, and yet microsoft screwed that up too...

[slave138]

How does that not make sense?

What is a valid reason to NOT have the patient ID in that sub test table?

If you had data on someone, wouldn't you want a clear and easy way to know who that data was for?

*I* would like that data in context of the test it was a part of.

For example, if you had a table that listed phone numbers, wouldn't you want to know the names of the people that had those numbers? The way it's set now is that it just counts what number that is in your list i.e. phone number 200 of 300 phone numbers on record.

How about a different example: You have a table of phone numbers, a table of extensions and a table of names. Wouldn't you want to know which phone number the extension goes with for a given person instead of just having the extension?

If the way you described being able to do that in access is the simplest way, then there is something horribly wrong with access. This should be such an easy, simple, and less than a second task, and yet microsoft screwed that up too...

To me, a sub-test would be a logical 'child' of the test so it makes perfect sense to reference it from its test. That said, you can always add the patient ID to the sub-test table and then reference it directly (assuming you have the rights to change the table). If you would prefer it to work differently and have the rights to make the change, then what's stopping you?

I find you blaming Microsoft for your own (or someone else's) architecture choices to be a typical response for someone who doesn't know how to do something and doesn't want to take the time to properly learn.

For someone with half a clue, referencing the sub-tests though the parent test *is* an 'easy, simple, and less than a second task'. For those that expect Access to magically restructure itself based on what they think makes the most sense, I suppose it would be painfully inadequate.