a complicated linkage

[Scott B]

Greetings,

I did not quite know how to label this subject, but I think I can explain
it.

1. I have one table, let's call it persons. I have a form for data entry
based on a query that is based on the the persons table.
2. I have another table, let's call it calls. I have a form for data
entry based on a query based on the calls table.
3. The tables are linked via numeric fields.
4. Persons contains data on active firefighters, non-active members of
the fire department and 911 callers (users of department services). Callers
may be firefighters or non-active AND firefighters and non-active may be
callers.
5. I need a mechanism to track the 911 caller.
6. I currently use a form to track what we call the run sheet or call
(calls table). It contains data on the time and date, type of call (i.e.
EMS, Fire, etc.) and has subforms that keep track of firefighters who
respond to the call and the vehicles that respond to the call.
7. I have form that I want to use to log the personal info of the 911
caller to the persons table.
How do I open the form to log the personal info I have filled and then save
it to the persons table with a link to the calls table so I can see who the
caller was on the run sheet or call? I am also unsure how to deal with the
problem of callers being firefighters and firefighters being callers?
Won't this create dupicate records?

Thanks,
confused Scott B

 

[Guest]

Please clarify the following:
"Callers may be firefighters or non-active AND firefighters and non-active
may be
callers."
To my way of reading it you are making the same statement twice, yet
presumably you intended some sort of distinction. Does this database have
anything to do with recording calls by people not affiliated with emergency
services? If so, I think you will want two different tables, one for callers
and one for responders. How much information about callers are you
recording? Is each call associated with a single caller? Do you want to
check a list when a new call comes in to see if that person has placed other
calls in the past?

 

[Scott B]

Bruce,

This database tracks both members of a fire dpepartment (both active and
non-active) including their personal info, equipment and training as well as
the calls they respond to. The part of the database that tracks the calls
they respond to also tracks other pieces of information about the call such
as the date and time, dispatch location, type of call (i.e. fire or EMS,
auto accident), vehicles responding and who the 911 caller is (or who the
patient is for EMS or homeowner for fire, etc.) Anyone can call 911. A
civilian or a member of the department. It seemed silly tme to have a table
of callers and a table of department members. I would be duplicating
entries because I would have a record for each member and then a new record
of the same person if they (as has happened) called 911. I need to be able
to take data from a form that is bound to the persons table and which data
will go into the persons table with a link to the calls table so I can see
who called 911. Ideally I would like to have the form that takes the
personal info on the caller of 911 open from the calls form and deposit a
copy of the name on the form to remind me of the callers name later as well
as put the info in the persons table as a 911 caller.

I currently have 13 tables. Several are used to look up data so I don't
store redundant data. For example, I have table that lists the type of
people in the perons table (Active Firefighters, Exempt/Life Department
Members, Non-Active Firefighters, 911 callers) so I can differentiate
between who trains, has equipment, responds to call and calls 911 that is
linked to the persons table. The rest store individual types of data like
training, equipment, vehicles, etc.

Your questions:
1.Does this database have anything to do with recording calls by people not
affiliated with emergency
services? YES, callers to 911 who are calling from within our fire
district and therefore will get our assistance.
2.How much information about callers are you recording? First name, last
name, addresses (local and permanent), phone, age and DOB, SSN, local
physician if any for EMS calls.
3.Is each call associated with a single caller? Each call is associated with
a single 911 call and for our purposes we assign a single person to each
call. Either the homeowner if a fire, the registered owner of car if a MVA,
or the patient's name if an EMS call.
4.Do you want to check a list when a new call comes in to see if that person
has placed other calls in the past? Ideally, YES.

Does that make it any clearer?

Thanks you very much. I appreciate the help very much. I have all the
other pieces of this application nailed down. I think Iam just being very
dense right now. Everything else I have been able to figure out.

Scott B

 

[Guest]

Comments inline

Bruce,

This database tracks both members of a fire dpepartment (both active and
non-active) including their personal info, equipment and training as well as
the calls they respond to.

I suggest that there should be a members table, and separate training and
equipment tables. If you keep track of training in the members table, how
many fields (different types of training or training session) will you allow?
Five? Ten? If you allow ten, what do you do if somebody has training in
eleven areas? Similarly with equipment, I would think. With training the
simplest scenario is that there is a one-to-many relationship between each
member and the training sessions. Each employee attends several training
sessions. However, if some training sessions are attended by several
members, that becomes a many-to-many relationship (some members attend many
sessions, and each session may be attended by several members), and a third
table is needed to resolve the relationship.

The part of the database that tracks the calls
they respond to also tracks other pieces of information about the call such
as the date and time, dispatch location, type of call (i.e. fire or EMS,
auto accident), vehicles responding and who the 911 caller is (or who the
patient is for EMS or homeowner for fire, etc.) Anyone can call 911. A
civilian or a member of the department.

How much information do you need to record about callers? If all you need
is the name of the person who placed the call, you could just store the name
in the Calls table. If a person calls and subsequently changes their name,
which name do you want associated with the call?

It seemed silly tme to have a table
of callers and a table of department members.

How much information do you need to track for callers? How about for
members? Five years from now I expect the list of callers will grow out of
proportion to the list of members. That may not be the best way to handle
this.
One approach may be to select callers from a combo box based on the Members
table, but allow for entries that are not on the list. Whatever you do, a
table of names should contain each name once only.

I would be duplicating
entries because I would have a record for each member and then a new record
of the same person if they (as has happened) called 911.

You need to create a record before you can select it. Do you need to create
a record for each new caller? What if the same person calls twice? If you
link from the calls table to the persons table, you will first need a way of
getting the record of a new caller into the person's table.