Newby w form problems

[Guest]

All patients ever cared for is one table. One of the fields is ?current
patient?
All nurses ever employeed is another tbl. One field is ?current RN?
I've successfully made queries that make current RNs and current Patients.

My intent is to create a table that shows what current RN took care of what
current patient today and what the acuity of the patient was (1-7). I wish
to populate this table via a form that can have drop downs of current RNs and
current patients as choices and my inputting the date and acuity. I can't
for the life of me get the drop downs to work. I'm working Access 2002. TIA
for your assistance.

Sam Beardsley

 

[Guest]

For simplicity, let's call the two main tables Nurses and Patients. These
are in a many-to-many relationship--a nurse has many patients, and a patient
many nurses. This is a cue that a 3rd table is needed to represent the
many-to-many into two one-to-many relationships. Let's call it CareHistory.

Nurses
-----------------
NurseID AutoNumber or Integer (Primary Key)
FName Text
LName Text
Phone Text
etc.

Patients
-----------------
PatientID AutoNumber or Integer (Primary Key)
FName Text
LName Text
etc.

CareHistory
----------------
CareHistoryID AutoNumber (Primary Key)
NurseID Integer (Foreign Key to Nurses)
PatientID Integer
CareDate Date/Time

For the combo boxes, either use the wizard (turn on by View, Toolbox, and
depressing the button with the wand and stars), or create it manually.
Presumably, you'd wish to display the patient and nurse's names, but store
their ID number in the table.

Nurse Combo Box:
RowSource
SELECT Nurses.NurseID, [LName] & ", " & [FName] AS NurseName FROM Nurses
ORDER BY [LName], [FName];
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";x" (where x is a number large enough to display the longest
name)
ControlSource: NurseID (in CareHistory)

Patient Combo Box:
RowSource:
SELECT Patients.PatientID, [LName] & ", " & [FName] AS PatientName FROM
Patients ORDER BY [LName], [FName];
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0";x"
ControlSource: PatientID (in CareHistory)

To implement it, you can either have a continuous form with both combo
boxes, based on CareHistory, a main form based on Nurses & a continuous
subform based on CareHistory, linked by the NurseID, which lets you enter all
the patients for that particular nurse, or a main form based on Patients & a
continuous subform based on CareHistory, linked by the PatientID.

Hope that helps.
Sprinks