Table Relationships 1

[rickyzicky]

What is the best way to do the following?

I am creating a database of firefighters and the run they attend locally.

I have one table called "members" with the profiles of each member.
I have another table called "runs" which detals all alarms called in to the Fire Dept.

I wish to be able to access the "runs" table and able to select all those "members" who attended the call.

I can see a way to do this, since there will be multiple firefighters attending the runs.

Any ideas or leads?

RZ

 

[RickSpr]

What you need is a third table, called MemberRuns perhaps, that associates a member with the runs he/she attends, and associates each run with the members who attend it.

You see, each member will attend many runs, and each run will be attended by many members. That makes the relationship between them a "many-to-many" relationship. This kind of relationship is most easily represented by a third table (called as "associative table&quot that contains the keys of the other two tables. A row in the associative table represents the association of one member with one run.

You can then determine which members attended the run by joining the Runs table with the MemberRuns table. Likewise, you can determine which runs a member attended by joining the Members table with the MemberRuns table.

If this is confusing, an experiment might help. Go ahead and create the MemberRuns table, and put some sample data in it. Then create the join queries I mentioned in the preceding paragraph, and open the queries in Datasheet View. I think you'll see what I mean.

Often, it's even better to put all three tables together in a single query, with both joins represented at the same time. You can sort the output of that query by member name to see which runs a member attended, or you can sort it by run to see which members attended each run.

The downside is that you have to get data into the associative table somehow. In your case, I assume you get some kind of list for each run, showing the members who attended it. For that, you'd want to build a main form/subform pair where the main form record source is the run table and the subform record source is the MemberRuns table. I'd suggest that you first display a form that updates the runs table by itself. After the new run data has been saved, this form should open up the main form/subform pair to allow the members who attended that run to be entered. When you've finished entering data for that run, you should close the second form and go back to the first one to enter data for the next run. (Using the same form to update both the runs table and the MemberRuns table is difficult, maybe even impossible.) Rick Sprague

 

[rickyzicky]

What fields are entered in the third table and which field do I join them with?
Also, would a record be created for each member that made the run? We have about 90 members, each with their own unique badge #.

 

[RickSpr]

Let's say you have a Members table with primary key BadgeNbr, and a Runs table with primary key RunNbr. Your MemberRuns table would then have two fields: BadgeNbr and RunNbr. The join between Members and MemberRuns would be on the BadgeNbr field, and the join between Runs and MemberRuns would be on RunNbr.

MemberRuns may not need a primary key at all, but if you decide it should at some point, set the primary key to the combination of both fields.

BTW, although you haven't indicated a need for other fields in the MemberRuns table, it's possible to have them. For instance, if you wanted to track member injuries at runs, you could add an InjuryType column to it. Then you'd be able to produce a report of who got injured on each run, and what the injury was.

Yes, you'll have one row in the MemberRuns table for each member on a given run. It sounds as if you might be dreading having to create so many records. With a basic form, there's no way around that. But if the effort required to enter so much data bothers you, there are a couple of ways you can reduce the work. One would be to create a main form/subform in which you select the run (which you entered previously on another form) in the main form, then enter the badge numbers in a list in the subform.

Alternatively, you could design a special form that lets you enter the run data and a list of badge numbers all on one form, but this method would require writing a good bit of VBA code. Rick Sprague