3 tables and the relevant ID's

[YNWA]

Hi, this might be an easy question but my mind is blank.

I have 3 tables for a teen drop in service.

I know tblPatients is a 1-many relationship with tblReasonForVisit. But is tblOutcomeOfVisit a 1-1 relationship with rblReasonForVisit? As each visit only has 1 outcome? And if so, what FK do I need to put in the tblOutcomeOfVisit table to link with another table?

tblPatients

• ID
• DateOfVisit - would this be better in the ReasonForVisit table?
• Date of Birth
• Postcode
• Gender
• FirstVisitofYear - YES/NO outcome - would this be better in the ReasonForVisit table?

tblReasonForVisit - this details the reason for the patients visit

• ID (auto num)
• patientID (number) - linked to tblPatients ID field
• Smoking - Check Box
• Exercise - Checkbox
• MentalHealth Advice - Checkbox
• Relationship Advice - Checkbox

tblOutcomeOfVisit - this details the outcome of the Reason for Visit table info

• ID (auto num)
• Now this FK, does it need to be the tblReasonForVisit ID or the tblPatients ID?
• PregnancyTest
• Notes
• EHC
• CondomDistribution
• SignpostToOtherServices - this was going to be a drop down, but it can have more than 1 outcome, so would a few check boxes be better? As a patient can be signed to like a GP and a midwife etc...??

Basically, what would the relationships between the 3 tables be like and which FK fields need to be in each table?

Also on a side note, is it possible to select more than 1 outcome from a drop down or list box and have them stored in a table?

Cheers
Will

 

[PNGBill]

Don't use ID as a field name. PatientID, VisitID & OutcomeID is ok.

2nd field in Outcome table is VisitID - your link between Visit table and Outcome table.
2nd field on Visit table is PatientID.

What do mean about select an Outcome and store??

If you mean you want a Finite List to choose from for possible Outcomes, then this is common practice.
The list can be from another table - TblOutcomeOptions.

In your Form where you enter such data, you have the table as the record source for your List Box or Combo Box and the selected option is then stored in your outcomes table.

Each visit can have only one Outcome although a possible outcome could include an repeat visit.

 

[PNGBill]

I didn't pay too much attention to your post and went off track with the table names.

You appear to have a table missing - actual visit table.
This could be combined as one with the outcomes table although the name doesn't suggest this.

Your links are like a chain. Patient to Visit. Visit to Outcome. etc.

Try to avoid any repeat of fields. ie PatientID shouldn't need to be in the Outcomes table as it is in the visits table.

Your queries will link all of this so long as you have a line that eventually gets from A to D so to speak.

 

[YNWA]

What happens now is they fill out a paper form with all the details I list on it.

I thought that patients table then link to visit table (ReasonForVist) - This would be 1-many

Then once the visit is over, that same day they assign an outcome of visit, basically wht they gave the patient, where they sent them. So link to visit table and 1 visit only has 1 outcome. As if they came back, this would be a new visit which would then have a new outcome.

The reason for visit table is the actual visit table, its the date, base and a list of options the user selects for the reason the patient had the visit (there can be more than 1 reason so thats why they would be a list of check boxes, rather than a combo box linked to a lookup table of reasons).

Is there a reason not to use ID? I usually use ID as PK then patientID, visitID etc...as FK in other tables.

Is this wrong or just frowned upon?

For the Outcome and store comment, if I had a drop down menu with a list of outcomes, is it possible to select more than 1 outcome on a drop down menu and store those selections? Or would I need a few drop down menus in order to select each one and store in there own table field?

Cheers
Will

 

[PNGBill]

Is there a reason not to use ID? I usually use ID as PK then patientID, visitID etc...as FK in other tables.

Is this wrong or just frowned upon?

Access has Reserved Names - a long list of them. They include words like Date, Name Input. Don't know if ID is on the list but I wouldn't use it as it is too common, doesn't have any indication of where it is used.
A good practice is to use double words and this make sthe reserved word list a none issue - I think - and allows a bit of description.
PatientID, VisitID, OutcomeID all are none reserved words and mean something to you as the nuts and bolts person when say in 5 years you are working on a pce of code you instantly know what the data is. It may be in any table but it is always the Outcome Primary Key rather then any primary key.
Also, many applications require more then One Database to work together - If you have ID in one and ID in the other

For the Outcome and store comment, if I had a drop down menu with a list of outcomes, is it possible to select more than 1 outcome on a drop down menu and store those selections? Or would I need a few drop down menus in order to select each one and store in there own table field?

How do you want to store the outcome?? as any number of individual blocks of text or as one concatenated Memo Field ?
This is a little tricky and will require more posts to get it working but I imagine you could have some vba code that asked you what the outcome is and allow you to select an option from a list (ex outcome option table) and then ask if any more data required and if yes, produce the list again, maybe with the selected item not showing, and concatenate the 2nd choice to the first choice and continue the is process until you say stop or no more options in the list - unlikely.
You can also add user comments as the last option.

This would be "cleaner" then having a fixed number of text box controls on your form as you are bound to have either a messy form or not enough options to enter the required data.

Get your tables and forms working and then post with questions regarding this by then you will have your outcome options table setup and populated.
Include a Memo field for OutcomeMemo in your visit outcomes table to be populated by the vba code.
In your visit table or visit outcomes table you could have a field for Reason of visit and this uses a fixed list of options - Broken bones, stick in eye etc and the vba code uses this to give different option lists for the Memo Field above.
Skin rash doesn't include Setting and Plaster to broken bones in the list - eg