Question

Locked

Help with querying a database in Microsoft Access

By toddmehl ·
Hello, I am trying to create a database to store information about calls for my local fire department.

My current task/issue is creating a a means of storing the names of firefighters who responded to various calls over the years. I have already created a table, that is filled from a form, in which you can select active members from a combo box. Each of the names will be stored in one of 15 fields, labeled Responder01-Responder15 (small department, few active members, no real need for more than that). That part is done...

My problem is, at the end of the year, I want to be able to tally up how many times each member responded to calls. I want to be able to create some statistics for the Chief. I have tried the whole "union" thing, and the "group by" with no luck. Is there something simple that I am missing?

So far, this is my first project with Access, and any wisdom would be appreciated. Thanks in advance!

This conversation is currently closed to new comments.

13 total posts (Page 2 of 2)   Prev   01 | 02
Thread display: Collapse - | Expand +

All Answers

Collapse -

I got it!!! I got it!!! I got it!!! I got it!!! I got it!!! I got it!!

by toddmehl In reply to Help with querying a data ...

First of all, thanks to those of you who wrote to me with advice! It is greatly appreciated! With the help of Tony and another website, I was able to piece it together. Here it is:

Query #1
"CountRespondersGrouping"

SELECT Responder01 AS RespondingFF
FROM Responders
WHERE Responder01 is not null
UNION ALL
SELECT Responder02 AS RespondingFF
FROM Responders
WHERE Responder02 is not null
UNION ALL
SELECT Responder03 AS RespondingFF
FROM Responders
WHERE Responder03 is not null
UNION ALL
SELECT Responder04 AS RespondingFF
FROM Responders
WHERE Responder04 is not null
UNION ALL
SELECT Responder05 AS RespondingFF
FROM Responders
WHERE Responder05 is not null
UNION ALL
SELECT Responder06 AS RespondingFF
FROM Responders
WHERE Responder06 is not null
UNION ALL
SELECT Responder07 AS RespondingFF
FROM Responders
WHERE Responder07 is not null
UNION ALL
SELECT Responder08 AS RespondingFF
FROM Responders
WHERE Responder08 is not null
UNION ALL
SELECT Responder09 AS RespondingFF
FROM Responders
WHERE Responder09 is not null
UNION ALL
SELECT Responder10 AS RespondingFF
FROM Responders
WHERE Responder10 is not null
UNION ALL
SELECT Responder11 AS RespondingFF
FROM Responders
WHERE Responder11 is not null
UNION ALL
SELECT Responder12 AS RespondingFF
FROM Responders
WHERE Responder12 is not null
UNION ALL
SELECT Responder13 AS RespondingFF
FROM Responders
WHERE Responder13 is not null
UNION ALL
SELECT Responder14 AS RespondingFF
FROM Responders
WHERE Responder14 is not null
UNION ALL SELECT Responder15 AS RespondingFF
FROM Responders
WHERE Responder15 is not null;


Query #2
"CountRespondersTotals"

SELECT RespondingFF, COUNT(*) AS NumOf
FROM CountRespondersGrouping
GROUP BY RespondingFF;


I am sure my syntax or naming convention can be picked apart, but I made it simple to understand for my simple mind. I know I am ecstatic over a small query, but I am happy it finally worked out. Not bad for an electrician...

Collapse -

Different solution to mine but top marks

by Tony Hopkinson In reply to I got it!!! I got it!!! ...

that will work fine.

Collapse -

How's your database working for you?

by jwhite In reply to I got it!!! I got it!!! ...

Greetings,

Don't know if you will see this being ~9 months have passed. Am also a Fire Fighter and we use the FireHouse application, and contemplating writing a custom "add-in".

The suggestions that were offered to you are indicative of a Non-Normalized Database Structure. With tables properly defined, you wouldn't have needed all those UNIONs.

Just curious how your databaes is geting along.

Stay Safe,

John, CFD 63.72, North Carolina

Back to Software Forum
13 total posts (Page 2 of 2)   Prev   01 | 02

Related Discussions

Related Forums