Need help with patient database

[Vududude]

Hi, I am a newb at databases and Access, I work in a hospital that does not yet have an electronic medical record, so I am trying to make a simple database for our trainees to input patient information and print out daily progress notes. There is no money to buy one that is already made, and I'm very much interested in learning the basics of database design.

Our system currently has 12 teams defined by colors (red, blue, green etc.), each with 2 interns (intern 1 and intern 2).

Currently my 3 tables are:

Teams:
Team_Id (autonumber, PK)
Team_Color (text)

Interns:
Intern_Id (autonumber, PK)
Team_Id (number)
Intern_number (text)

Patients:
Patient_Id (autonumber, PK)
Intern_Id (number)
Last Name:
First Name:
.
.
.
etc.

Teams are set up 1 to many with interns which is set up 1 to many with patients. I've already inputted all the team colors (red, blue, green etc.) and the interns (since there is a set number of these)

I'm having a few problems

1) on a basic note, how would you go about assigning a patient first a team color, then an intern. The way it is set up now I can assign them an intern who is already assigned a team. But then I have to pick through 24 interns (12 teams x 2 interns) to select the intern. Ideally I want to first select a team, which then narrows it down to only 2 interns.

2) is there a way on a form to display text in a combo box, but have the database enter a number in the actual database. The way I have it set up above, everything is assigned an autonumber. I want to set up an easy to use form for interns to enter patient info. If I want to assign a patient an intern I would like the combo box to say (intern 1, intern 2). However each intern is assigned an autonumber so in the form it lists autonumbers 1-24 (since there are 24 interns overall)

Sorry if I am not explaining this very well. Any help would be greatly appreciated!

 

[Sum Guy]

Is there any special reason why you are assigning your teams a number?
I ask because you have already assigned them a colour.
Likewise, the doctors have names, why use a number to identify them?

1. Set up your entry form with two combo boxes. The first selects the team. This will narrow down the doctors in the second combo box to those that are assigned to the team you selected in the first combo box.

2. Have the doctor's form with 2 combo boxes. The first combo box selects the doctor, the second restricts the patient selection to the doctor chosen in the first combo box.

If you are unsure how to do this, do a search on restricting Combo boxes

 

[Vududude]

Is there any special reason why you are assigning your teams a number?
I ask because you have already assigned them a colour.
Likewise, the doctors have names, why use a number to identify them?

The only reason I assigned them an autonumber is from a suggestion of a friend who said it's just usually better to assign an autonumber to each table. But I was thinking of just using the team color as the PK.

Also each physician goes on a different rotation each month and at the end of each year there different MD's come in and some leave, so i figure it's just easier to assign a generic intern 1 or intern 2 as each MD already knows their team color and what intern # they are.

thanks for the suggestions, I will look up the restricted combo boxes and give them a try.

 

[Vududude]

Hi, I'm still having a few problems. I looked up cascading comboboxes and I think I have them working. I initially created 2 unbound comboboxes and they are working correctly. problem is that I can't get it to actually fill in data into the datasheet. I select the team color, then the intern number, but the intern_id doesn't get filled in in the patient database. I can't quite figure out how to bind the control to that field.

 

[ColinEssex]

You've not created a Medical Record - only a patient database.

What about the drugs / treatment /notes / physio / follow up / xray / path reports / histology / haematology / microbiology etc etc etc?

Col

 

[Vududude]

You've not created a Medical Record - only a patient database.

What about the drugs / treatment /notes / physio / follow up / xray / path reports / histology / haematology / microbiology etc etc etc?

Col

hmm...if you look above that was my "..." in my patient table. that stuff works fine, i'm having problems with assigning patients to at team and intern.

 

[jdraw]

hmm...if you look above that was my "..." in my patient table. that stuff works fine, i'm having problems with assigning patients to at team and intern.

Here's a reference to setting up database structures. I think it would be useful to you.
http://r937.com/relational.html

 

[ColinEssex]

hmm...if you look above that was my "..." in my patient table. that stuff works fine, i'm having problems with assigning patients to at team and intern.

That's the point - 'that stuff' should not go in a patient table. It needs separate tables.

Col