Fire department forms

[pentabarf]

Hi,
I haven't used Access in about 10 years, so I am relearning as I go.

I am a member of a local volunteer fire department and we want to use Access to track everything that we have go on.

I have already created the members table/form. I have also created a query to give me a list of current active members based on a field in the members table (as we have members retire or leave for whatever reason). I am trying to create a form to track the calls that we get. But I need someway to show the names from the active members query, and then be able to tag that they were at the call and a few other yes/no questions.

What would be the best way to accomplish this?



Thanks.

[Rawb]

I'd recommend adding two Tables. The first should contain the Call Log details (the call itself as well as a unique number that can be used to identify the call). The second table will just have two fields: A Call ID and a Member ID.

Then every time you get a call, you add a record to the Calls Table and then, for every Member that responded to that call, add a record to the other table with both their Member ID and that call's Call ID.

So you'd have a Table setup something like the following. . .

Members:

MemberID|MemberName
1|Rawb
2|pentabarf
3|TheShabz
4|pbaldy

Calls:

CallID|Address|Time
1|123 Street St.|09/09/2010 14:21.23

Responders:

CallID|MemberID
1|1
1|2
1|3

In the above example, Rawb, pentabarf, and TheShabs all responded to the call to 123 Street St. (while pbaldy's wife was called to watch both him and the phones).

[pbaldy]

while pbaldy was left to watch the phones

Anybody around here will tell you I can't be left alone to watch anything.

[ajetrumpet]

Anybody around here will tell you I can't be left alone to watch anything.

not even the iowa football games!? they smashed the opp's last weekend. whohooo!

[pentabarf]

Rawb,
Thanks for the information. That is pretty much what I have already done. The problems that I am having seem to be from this point forward. On the members table, there is a lookup field which has two options: Active and Inactive. From there I run a query to provide me with a list of only the active members.

I have then created a form for data entry of the call information. This is for the less computer literate of our members who will often times be entering the call information.

Using your example, the responders table needs additional fields to note what each member did on the fireground (driver, pump operator, etc).

I need the form to be able to pull in the list of active members from the query and be able to note each member their activity. (I am hoping to use Yes/No fields for each fireground activity).

I was attempting to do this with a sub-form, but don't seem to be able to get it to work properly.




The problems that I am having using the above example is that when I move to the next record the subform data does not change.

Any thoughts, or am I going about this the wrong way?

Thanks.

[Rawb]

Can the responders perform multiple tasks/functions? Using my example above, can I (Rawb), be both a driver and a pump operator? Or can I only perform one task (be a driver OR a pump operator, but not both)?

I would agree that a Subform would be the way to go with this, but I'd suggest using a Combo Box instead of a Yes/No field for the responder's duty(s).

As for the duties themselves, I'd recommend adding another table called "Duties" (or whatever) that has two fields: An autonumber ID field (DutyID), and a Text field - no more than 50 chars or so - for the name of the duty (DutyName/DutyDesc)

DutyID|DutyName
1|Driver
2|Pump Operator

Your Sub Form would then pull your Members Query for one Combo Box and the contents of this Table for the other.

[Rawb]

Update: Sorry penta, for some reason the image didn't show up for my earlier response.

I'll take another look at it when I get back to work (I have a test MDB set up there) and see if I can get it to do what you're wanting.

[pentabarf]

Rawb,
Yes, each member can perform different tasks for the same call (driver can also be pump operator, etc, but may not always be).

I wanted to go with the Yes/No check boxes for the forms, since some of the users that will be entering data are not always very computer literate. I want to keep things as simple for them with their data entry as I can get it.

I already have a system done up with an excel spreadsheet which should work excellent for us. But since there is the ability to type things in, people type in whatever they want, and then the formulas don't calculate things like they should. The less options I can give them, the better. That's why I am using so many Yes/No boxes.

Thanks again for the help.

[Rawb]

OK, well the picture still doesn't show up for me. Most likely they've blocked content from ImageShack. . .

I basically remember what it looks like though, and that's all I really need.

The biggest problem you're going to run into (which you would run into anyway since you have so many duties) is that it's going to be very difficult to display those back to the user in a Form. At least they way I'm thinking of doing it. . .

Based on your needs, I's recommend you edit your "Responders" table to include a Yes/No Field for each possible Duty. That way you can use just a single Record to record all the Duties someone performs on an individual Call.

As for the data entry Form, I'm thinking you should use a Pop Up Form inside your Sub Form: When the user selects a Responder's name, it automatically opens up a new Form where they can select the Duties that Responder performed. When they're done, they just close the Pop Up Form.

The downside to this is that there's no "at a glance" way to see what each Responder's Duties were without opening up the Pop Up Form for each Responder.

Anyway, I'm (mostly) caught up on what needs to be done here at work so I'll see if I can steal a couple of minutes here and there to fiddle around with things.

[pbaldy]

FWIW, I'm not a fan of the Yes/No checkboxes for each duty. You're going to get this all working well, and then the powers-that-be are going to come up with a new duty. That will force you to change the table, plus every form and report that uses it. A properly normalized structure would allow users to add a new record to a "duties" table and it would flow through without any design changes to the db. I'd probably use either a subform or a multiselect listbox for the user.

[Rawb]

FWIW, I'm not a fan of the Yes/No checkboxes for each duty.

I agree with Mr. Baldy (teehee, baldy!) on this one. The reason I suggested making the Table change to facilitate penta's Checkboxes was because he'd already gone to the trouble of putting together a Form with all the items in it.

Now that I've had a chance to look into Multi-select List Boxes however, I'd strongly recommend using that instead of the Checkbox method. Not only does it keep you from having to update your Table Design/Layout whenever a Task is added or removed (which, admittedly probably won't be all that often), but you don't have to make any changes to the Forms either. With a List Box, it should automatically update to include any new Tasks you can come up with (as well as removing deleted Tasks automatically).

See here to read about using Multi-select List Boxes.