Design

[Dar]

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

 

[KARL DEWEY]

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?

 

[Dar]

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.

 

[KARL DEWEY]

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.

 

[KARL DEWEY]

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

 

[Dar]

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