If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Many members, many events
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 |
#2
|
|||
|
|||
Many members, many events
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. -- rpw "Microsoft" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access 2000: No events when hiding & reopening dialog box-a glitch in attempt to speed up app | Matt Jaffey | Using Forms | 11 | June 11th, 2004 09:09 PM |
events in calander showing up as invited events after import | Duane | Calendar | 0 | May 21st, 2004 03:39 AM |
All-day Events Imported to Outlook2003 Now Cover 2-Days | John-P | Calendar | 1 | May 19th, 2004 11:11 PM |
Any way to sort All Day Events? | me | Calendar | 1 | May 7th, 2004 01:39 AM |