Query cross two table

[Jeff]

Hi,
I have two tables, tbAdmission and tbCode. In my tbAdmission, I have Code1,
Code2 and Code3. In my tbCode, I have Code, Description1 and Description2.
In my Form, frmAdmission, I have txtCode1, txtCode2 and txtCode3 that are
all bounded to tbAdmission. And txtDescription1Code1, txtDescription2Code1,
txtDescription1Code2, txtDescription2Code2, txtDescription1Code3 and
txtDescription2Code3 that are unbounded and only for displaying the
descriptions.
txtCode1, txtCode2 and txtCode3 all refer to Code in tbCode to retrieve
Description1 and Description2 for displaying in the unbounded txtboxes.
I need your help to build a query to do this job for frmAdmission. Thank you
in advance.

 

[Allen Browne]

Jeff, what you've done here is a very common mistake: you've built a
spreadsheet in Access, instead of a relational database structure.

If one student?/patient? can have multiple admissions, you need:
a) a table of persons (one record for each person)
b) a table of codes (one record for each, with its description)
c) a table of admissions (one record every time someone is admitted)

The 3rd table will have fields like this:
- PersonID who was admitted
- CodeID what they're admitted for/in
- AdmitDate when this person was admitted into this code.
- AdmissionID primary key

 

[Jeff]

Hi Allen,

Thank you for your quick response. I do have tbPatient that stores personal
data for each patient and tbAdmission that stores admission record. tbPatient
and tbAdmission are one to many in relation. I also have tbCode that has 3
fields, Code for diagnostic codes, Description1 for diagnostic names in
English, Description2 for diagnostic names in Chinese.

On my frmAdmission, I have 3 txtBoxes, txtCode1, txtCode2 & txtCode3 that
are bounded to Code1, Code2 & Code3 in tbAdmission resspectively. That is, we
allow doctor to enter up to 3 diagnostic codes for each admission. However,
other staff will not always remember the diagnostic codes, so I need 2
txtBoxes for diagnostic name in English and in Chinese for each code entered.
In order to save space in tbAdmission, I don't want to store these names in
it and only want Access to retrieve these names from tbCode and show them on
txtDescription1Code1, txtDescription2Code1...........

Does it make sense? Perhaps, I need to do it in another way. Any idea?

 

[Jeff]

Hi Allen,
I read your reply again and found that I have done as you suggested. The
problem is I need 3 Codes for each admission record. I think this is not
unusual in a data base like that.

 

[Allen Browne]

Okay, great: so you have set it up so one person can have many admissions.

Now you're saying that, for any admission, you need multiple codes. This
suggests a 4th table, with fields:
- AdmissionID relates to your table of admisssions
- CodeID relates to your table of codes.

Assuming you have a form where they enter the admissions, it will have a
subform where they can enter the associated codes. The subform will be in
Continuous form view, so you can enter as many rows as you need for the
admission in the main form.

In this way, you no longer need Code1, Description1, Code2, Description2 etc
in your admissions table. Repeating fields like that always mean that you
need to put the data into a related table.

If I read you correctly, the descriptions are associated with the codes,
i.e. any code should show the same description for all cases. To do that,
you just put the descriptions in the codes table. Use a combo in the
subform. You can bind the subform to a query that uses both the admissions
table and the codes table, and therefore it can return the description field
for you. (I suggest you set the Locked property of the description field to
Yes so it doesn't accidentally get changed.)

Your Admissions form will look something like this:

PatientID [ v] (combo box)
Admission Date [ ]
Details (subform):
| Code: [ v] description [shows automatically] |
| Code: [ v] description [shows automatically] |

 

[Jeff]

Thank you so much, Allen. I need sometime to 'digest' what you told and let
me come back here later.
--
Jeff


"Allen Browne" 來函：

Okay, great: so you have set it up so one person can have many admissions.

Now you're saying that, for any admission, you need multiple codes. This
suggests a 4th table, with fields:
- AdmissionID relates to your table of admisssions
- CodeID relates to your table of codes.

Assuming you have a form where they enter the admissions, it will have a
subform where they can enter the associated codes. The subform will be in
Continuous form view, so you can enter as many rows as you need for the
admission in the main form.

In this way, you no longer need Code1, Description1, Code2, Description2 etc
in your admissions table. Repeating fields like that always mean that you
need to put the data into a related table.

If I read you correctly, the descriptions are associated with the codes,
i.e. any code should show the same description for all cases. To do that,
you just put the descriptions in the codes table. Use a combo in the
subform. You can bind the subform to a query that uses both the admissions
table and the codes table, and therefore it can return the description field
for you. (I suggest you set the Locked property of the description field to
Yes so it doesn't accidentally get changed.)

Your Admissions form will look something like this:

PatientID [ v] (combo box)
Admission Date [ ]
Details (subform):
| Code: [ v] description [shows automatically] |
| Code: [ v] description [shows automatically] |

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I read your reply again and found that I have done as you suggested. The
problem is I need 3 Codes for each admission record. I think this is not
unusual in a data base like that.
--
Jeff


"Allen Browne" 來函：

.

 

[John W. Vinson]

I read your reply again and found that I have done as you suggested. The
problem is I need 3 Codes for each admission record. I think this is not
unusual in a data base like that.

Just to agree with Allen and maybe phrase it a different way:

Sure, you need three codes.

Someday you might need FOUR codes. Or five!

Your design makes that either very awkward, or downright impossible.

"Fields are expensive, records are cheap" - if you have a one (admission) to
many (codes) relationship, in the real world, then model it in your database
with a one to many relationship between two tables.