linking up tables

[charlieb]

Just started learning access and I could use some help with linking more than 2 tables together in a form. I have Clients who from time to time go to the hospital. I want to keep track of my Clients hospital visits so I set up 3 tables. One for Clients, one for the Hospitals and one for Patient Information. An example of the tables looks like this (this is a short version):



Clients: ClientID - Autonumber
ClientFname - short text
ClientLname - short text

Hospitals: HospitalID - Autonumber
HospitalName - short text

PatientInfo: PatientInfoID - Autonumber
AdmissionDate - date/time
PatientNumber - short text

I figure that 3 tables are necessary because each client may go to more than one hospital and some or all may visit one hospital more than once. I have worked with junction tables before but not sure how to set up a form to capture the value of all 3 PK values and store them in one junction table. Any help please?

[ranman256]

Put a sub form in ur main form. This is 1 to many join, with the client as the master,
And patient and hospital as children.

[charlieb]

Put a sub form in ur main form. This is 1 to many join, with the client as the master,
And patient and hospital as children.

Thank you but I am not understanding. Would I need to put 2 subforms in the main form, one for Hospitals and one for PatientInfo? Do you know of any examples I could look at?

[charlieb]

Thank you but I am not understanding. Would I need to put 2 subforms in the main form, one for Hospitals and one for PatientInfo? Do you know of any examples I could look at?

Also, I was thinking that maybe I should add fk ClientID and HospitalID to PatientInfo table and store the values there instead of the Junction table. Would that work better?

[orange]

What's the difference between Client and Patient?
How many Hospitals are involved?
A patient is in 1 hospital? How do you account for the Hospital in your set up?

[charlieb]

the client table contains only information about the client ie: name, address, phone etc. (the tables shown above are just abbreviated to simplify) and the PatientInfo table holds information specific to the clients admission(s) to one or more hospitals. The number of hospitals involved could be any number. For example, if I have a client named Joe and he went to the ER at Mercy Hospital on 1/1/2000, then a week later he went into the ER again at Mercy, then one month later he went to Baptist Hospital, I need to keep track of the 2 admissions in Mercy and the one at Baptist. (unfortunately, I have many clients who require such emergency care). It seems simple when there are only 2 tables involved. I just use a form with a subform and create a junction table to hold the PK values. I am a bit confused as to how to get and store the values of the three PK in 3 separate tables

[charlieb]

If anyone knows of an example I could look at I would really appreciate that.

[ssanfu]

You have a table of clients. I client becomes a patient when he is admitted to a hospital.

This is how I would start the table structure (because I don't know all of the tables and fields.

tblClients
--------------
ClientID_PK - Autonumber
ClientFname - short text
ClientLname - short text

tblHospitals
--------------
HospitalID_PK - Autonumber
HospitalName - short text

tblAdmissions
------------------
AdmissionID_PK - Autonumber
ClientID_FK - Long
HospitalID_FK - Long
AdmissionDate - date/time
PatientNumber - short text


Relationships
Attachment 20097


In the demo dB, there are two forms, one to enter a lot of patients at the same time named "AdmissionsAll" and the other to enter admissions by Client named "AdmissionsByClient".

Good luck with your project

[orange]

I agree with Steve's approach. However, we don't know much of your "business". A few questions jump out immediately.
Do you really have Clients that are not Patients? When someone becomes a Patient (is Admitted), I would think that the Date and Time of admission would be important, as would the reason for the admission. If you are tracking Patients then Release Date (or whatever it is called) would also be of interest. Other things like Doctors etc may be in your scope, but until you tell us the 30,000 foot business rationale for the database, we won't know for sure.

Just a few things to ponder as your database evolves.
Good luck.

[charlieb]

Thank you. I can't tell you how much I appreciate your help and this forum.

[charlieb]

Since you ask........ Actually, I am an attorney practicing in workers compensation and personal injury. I have many clients who suffer from conditions that cause them to visit the ER frequently (reflex sympathetic dystrophy, herniated disks, etc..) and I need to keep track of admissions to get records and billing info. I am just starting out with Access 2013 but I have a rather ambitious goal. I want to build a practice management solution. I am learning a little at a time after work and on weekends. Ultimately, I want to build a database to keep track of all clients' information, doctors and hospitals they go to, insurance carriers, adjusters, defense attorneys and court/judge info re each case. If I am able to complete that task, I would like to use that database to automate tasks like document assembly, emails (interact with outlook for email and calendar) and build some calculators for work. There you go, that's the grand plan. At the moment, I am only on the one yardline. I know there are programs I can buy off the shelf but the ones I have seen do not combine all of the tools I need for work. Also, I want to learn how to do this.

[ssanfu]

Ambitious, to say the least.

Have you seen the tutorials at Rogers Access Library? You should work through them - they will give you some basics of dB design. Post back here when (notice I said wend, not if) you have questions.

http://www.rogersaccesslibrary.com/forum/forum46.html

Good luck....

[charlieb]

Thanks again for the resource, I'll head over there.

[ssanfu]

Also might check out Crystal's site
http://www.accessmvp.com/strive4peace/Index.htm

[charlieb]

I took a look, another great source, thanks