You're going to wonder why I'm writing about planning a database when you
just want the results. But without the planning, the whole thing will fail
very quickly and you will have to re-do any results which you get.
Here is a possible structure to get you started, depending on your needs.
You will think of other fields which you wish to add and possibly other
tables. The thing that takes the most thought is to decide which field
belongs to which table and when you need a seperate table for something.
TblPeople
an Autonumber primary key field PersId,
Firstnames,
Surname
DateOfBirth
You'll certainly need the above and may also need to consider in the table:
AliasName
Died (A yes/no field)
IDMarks (identifying marks)
SocSecNumber (social security number)
date of birth, some kind of way you identify them in the shelter possibly if
they have some kind of member number)
and any fields which apply only to that person and are not effected by
whether they attend or not.
TblAttendance
Autonumber Primary Key Field AttendID
StartDate (Date) (keep date and time separate to make it easier to filter by
date)
StartTime (Time field)
EndDate (date)
EndTime (time)
PersID (A number field)
AttendNotes
TblIncident
(to record any incidents when the person attended the shelter)
IncId (PrimaryKey)
AttendID (note that you don't need the PersID because you can get that from
the AttendID)
IncidentDateTime (a date time field)
IncidentNotes
You may also want (depending on your requirements.
TblNextOfKin
KinID
PersID
KinFirstName
KinSurname
KinAddress
Normally you would have a seperate table for the Kin but they are irrelevant
as individuals and only exist for you when they are connected to the person.
I've got a seperate table because you may want to add several next of kins
Note that, apart from the Foreign Key fields (ie personID in TblAttendance)
I am avoiding giving fields in different tables the same name so I have
IncidentNotes and AttendNotes.
Don't be tempted to shortcut your fields (eg don't put FirstName and Surname
in the same field) - you will almost certainly have to spend time seperating
them at a future date.
In the relationships window, link PersID from TblPeople to PersID in
TblAttendance & in TblNextOfKin
This gives you the structure.
To input your data, make a form based on TblPeople. Make its subform
TblNextOfKin
Make a Form based on TblAttendance. Use a combo box based on TblPeople to
let you add a person to the form.
When you've created this, we can help you to create queries to find out the
info you require.
Rather than asking for a whole step-by-step guide which involves a great
deal of typing (especially as you may well know the answers to 90% of what
the writer has spent time recording), you could ask a specific question
Here's an example of questions which will get answers:
My database tracks Attendance of People at a shelter. It has these tables
(list tables and explain any ambiguous sounding field names and how the
tables are linked).
I've made a query based on TblAttendance. How do I filter it to show all
those who attended between 2 dates?
or
I've created a query called QryAttendance based on my TblAttendance.
I want a button in my People Form so that when I press it, the query opens
showing all the times that this person attended the shelter. How do I do
this?
Here are some other tips for getting quick replies:
Try to ask 1 question at a time per email. Most people have the time to
tackle one question but few have the time to get stuck into a shopping list
Avoid using jargon to sound knowledgable unless you are prepared for the
respondant to answer in jargon too.
If nobody replies within a few days, don't give up, try asking again,
including the text from your original question.
Evi
