Design

[Guest]

I asked this question earlier, however, I'd like to take a new approach.

I need to create a database given this info. All in a spreadsheet format

1. A list of patients first and last names
2. A spreadsheet listing 1-31(however, looks like this 1,1,1,2,2,3,3,3,4,5,5)
those numbers represent days of the month
3. Above the days of the month, a list of activites, sometimes repeated
throughout
the month, like day 1 Bingo, day 5 Bingo, day 21, Bingo
4. There is a separate spreadsheet for each floor.

I need specifics on the best way to set up a database, the relationship(s),
and if form is based on a query, how to set up the query. Also, do the
fields go in the
Detail section or someplace else. Of course, I'm trying to have the fields
link if possible, and type the least amount necessary.

Please, please help. Thanks

 

[Guest]

Your 'spreadsheet' approach will not work based on the information you
supplied.

You did not relate the patients to anything. How are they related to your
event calendar and floors?
This does not make sense. You first say 1-31 then repeat 1 three times, 2
two times, 3 three times, etc.
Do you mean that everyone does the same thing on a given day? No Bingo and
Schuffle Board on the same day?

 

[Guest]

oops, I meant the info given me is in spreadsheet format. I want to use
Access and create tables, form, query which will pull in the patient's name
and the activity they attended on a given day.

The spreadsheet given to me lists the days of the month/activity across the
top,like day1/Bingo, day1/Mass,day1/Movie,day2/Ceramics/Day2/Mass, etc.
There are usually 3 activities for each day of the month. Each patient can
choose which activity to attend, so next to their name and under the activity
column is a check mark.

Once Access database is up and running, the info will be input into Access
and do away with the paper spreadsheet being used now.

I hope this makes sense now.

 

[Guest]

Patients ---
PatientID – autonumber – primary key
LName - text
FName – text
Preferred - Text
Floor - text
Room – text
Admitted – Datetime
Discharged – Datetime
Remarks – memo

Activities ---
ActivityID – autonumber – primary key
Name – text -- include something like ‘None’ or ‘No selection’ so as to
fill all the squares and indicate there was no omission when someone wished
not to make a selection.
Single – Yes/No – solitary activity

PatientAction ---
PatientID – autonumber – foreign key - create an index of these three and
set to unique
ActivityID – autonumber – foreign key
ActDate – Datetime

Set a one-to-many relationship from Patients to PatientAction on PatientID.
Set a one-to-many relationship from Activities to PatientAction on
ActivityID.

Determine how you will schedule the activities. By patient – date – activity
OR by date – patient – activity. Use a form/subform to do the scheduling.

 

[Guest]

I errored in the PatientAction table. It should read like this ---
PatientAction ---
PatientID – number - integer – foreign key - create an index of these three
and
set to unique
ActivityID – number - integer – foreign key
ActDate – Datetime

 

[Guest]

Thank you Karl. I appreciate the thoroughness of your reply.
I'll be working on this over the next few days.

 

[Jamie Collins]

Patients ---
PatientID - autonumber - primary key
LName - text
FName - text
Preferred - Text
Floor - text
Room - text
Admitted - Datetime
Discharged - Datetime
Remarks - memo

Patients ---
PatientID - autonumber - primary key
LName - text
FName - text
Preferred - Text
Floor - text
Room - text
Admitted - Datetime
Discharged - Datetime
Remarks - memo

You seem to be suggesting that when the same person is readmitted they
get a new identifier (PatientID); that effectively makes every patient
a 'John Doe', doesn't it? Person identifiers are tricky but I don't
think ignoring the problem is good advice. As regards a newsgroup
post, I'd recommend leaving the identifiers to the OP i.e. keep the
definition of PatientID intentionally undefined rather than
recommending an autonumber.

Because you have a start date and end date pair in the same row
(Admitted and Discharged respectively) you are modelling periods,
therefore the table requires a sequenced key e.g. constraints to
ensure no overlapping periods, only one row with a NULL end date (i.e.
the current state) for each patient, etc. Consider separate tables for
patients and their episodes (a.k.a. normalization).

Jamie.

--