Many members, many events

[Microsoft]

Greetings,

I am using Access 2000 on W2K & XP Home machines. I am designing a db to
track calls for a volunteer fire department. I have a table for calls that
keeps track of the date and time of the call and the location of the call.
I have another table that keeps track of the people (victims, callers, etc.)
involved in the call. I want to introduce the ablity to keep track of the
firefighters that respond to the calls and the equipment used on the calls.
I have built 2 tables. One has a list of firefighters, their status (i.e.
interiror or exterior rated), department id number and if they are a chief
or not and a table that has list of equipment. Firefighters can respond to
many calls and a call can have many firefighters responding. The same with
equipment.

My Question: How do I keep track of the firefighters at a call and
equipment at a call?

Currently I have this setup and working:
tblCallType - list of types of calls
CallTypeID autonumber
CallType text

linked by CallTypeID to:

tblCalls: - list of calls
CallID autonumber
CallDate Date/Time
CallTime Date/Time
CallAddress1 Text
CallAddress2 Text
CallCity Text
CallState Text
CallPostalCode Number
CallRunSheet OLE Object
CallBFIR OLE Object
CallPCR OLE Object
CallECG OLE Object
CallMiscForm OLE Object
CallTypeID Number
CallOwnerPatientID Number

linked by CallOwnerPatientID to:

tblCallOwnerPatient: - list of the person(s) (not firefighters involved
in call)
CallOwnerPatientID autonumber
FirstName Text
MI Text
LastName Text
SndFirstName Text
SndMI Text
SndLastName Text
Address1 Text
Address2 Text
City Text
State Text
PostalCode Number
Phone Text

 

[Guest]

If you are only associating firefighters to calls and equipment to calls (and not firefighters to equipment) then you need to add a junction table for each M:M relationship:

tblFirefighters
FirefighterID
'other fields that describe firefighters

tblCallFirefighter
CallFireID 'optional - you can use the other two FK combined as primary
CallID
FirefighterID
'any other fields that describe this topic (such as time the firefighter was on the call, etc.)

tblEquipment
EquipmentID
'other fields that describe equipment

tblCallEquipment
CallEquipID 'optional - you can use the other two FK combined as primary
CallID
EquipmentID
'other fields that describe this topic

If you need to track which firefighters were on which firetruck (equipment) for which call, then the above tables are not correct. You would need to have and call-equipment/firefighter table:

tblCallEquipFighter
CallEquipID
FirefighterID

I imagine that the call main form would have a subform for assigning equipment and the subform would have a subform for assigning firefighters.

This works if "Equipment" can have many firefighters. Maybe that's not the case - maybe you have a table for Vehicles, and "Equipment" is the personal equipment the firefighters are assigned (e.g. masks, boots, axe, helmet, etc.)

Hope this is enough to get through to the next step.

Question for you: Why are firefighters and callownerpatients different tables? They could both be in one table as tblPeople. That way, if ever a firefighter was ever a call-patient there wouldn't be data duplication. Any fields that are unique to firefighters can be in a different table linked to the PeopleID. But they both have name, address, city, phone, etc.