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.

14 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

example data?

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

Could you provide a small example? I'm not sure I understand. Does your table look something like this?

Responder01 Responder02 ... Responder15
Fred Bob ... George

Or am I missing something? :)

Collapse -

Yes

by toddmehl In reply to example data?

LocoLobo,
Yes, my table has fields with Responder01 to Responder15. My primary key is the call ID number, as dictated by the 911 center.

The data for the Responder01 to Responder15 fields will be as a result of the person entering data by clicking on the appropriate name in a combo box. The combo box is filled from a query that looks at info and determines which members are active.

I have a little typed up based upon what Tony has posted, as well as other info found on other sites out there. I will post it later on when it works/I get too frustrated to continue.

Thank you for your interest in helping me!

Todd

Collapse -

Two ways

by Tony Hopkinson In reply to Help with querying a data ...

To get number of calls from your current design, you'll have to do a fairly complex query.

Call Responder1, Responder2, Responder3
1 Fred Bill Jim
2 Bill Jim Pete
3 Fred Pete Russell

select Responder1 are responder, count(*) from calls group by Responder1
union
select Responder2 are responder, count(*) from calls group by Responder2

....

select Responder15 are responder, count(*) from calls group by Responder15

would give you
Responder CallCount
Bill 1
Fred 2
Bill 1
Jim 1
Pete 1
Jim 1
Pete 1
Russell 1

then
select Responder,Sum(CallCount) from CallCounts Group By Responder

would give you what you want.

The other way would be to take reponders out of calls and have another table
Call Responder

That would be one simple select count, but would require some UI changes.

HTHs

Collapse -

I will give that a try

by toddmehl In reply to Two ways

Tony,
The way you set it up the first time around is exactly how I set my table up. Sadly enough, I look for to going to work tonight, only to see if your solution works. I will let you know.

Thanks to the both of you who replied!

Collapse -

It's a git this one

by Tony Hopkinson In reply to I will give that a try

Responders 1 to 15 is simple, your general call information doesn't require a join.

Against it however, it uses more resources, it's fragile (I need sixteen responders !!!)
and it's a mess to analyse.

Collapse -

What if...

by toddmehl In reply to It's a git this one

Tony,
What I was thinking was if I had a call where only 3 members responded, there would be no names to put into the remaining 12 fields. Would the query recognize that they were blank and not do anything with them, or will it have a nervous breakdown? Thanks!

Todd

Collapse -

I am frustrated

by toddmehl In reply to What if...

This is what I have written and played with for a bit:

SELECT Responder01 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder01
UNION ALL
SELECT Responder02 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder02
UNION ALL
SELECT Responder03 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder03
UNION ALL
SELECT Responder04 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder04
UNION ALL
SELECT Responder05 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder05
UNION ALL
SELECT Responder06 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder06
UNION ALL
SELECT Responder07 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder07
UNION ALL
SELECT Responder08 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder08
UNION ALL
SELECT Responder09 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder09
UNION ALL
SELECT Responder10 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder10
UNION ALL
SELECT Responder11 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder11
UNION ALL
SELECT Responder12 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder12
UNION ALL
SELECT Responder13 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder13
UNION ALL
SELECT Responder14 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder14
UNION ALL
SELECT Responder15 as RespondingFF
COUNT(*) From Responders
GROUP BY Responder15

SELECT RespondingFF
Count(*) as NumberOfCalls
GROUP BY RespondingFF;


I keep getting an error stating:
"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

I have looked at numerous sites and cannot discern where to go from here. Any help would be great!

Todd

Collapse -

Hmmm

by Tony Hopkinson In reply to I am frustrated

You might want to do
UNION ALL
SELECT Responder15 as RespondingFF
COUNT(*) as NumberOfCalls From Responders
GROUP BY Responder15

If all that is saved as a query, or into a maketable then to get the numbers you want you neeed to sum the individual counts from each column


SELECT RespondingFF
Sum (NumberOfCalls) as NumberOfCalls
From mycallunionquery
GROUP BY RespondingFF;

To cut out the unused responder fields

UNION ALL
SELECT Responder15 as RespondingFF
COUNT(*) as NumberOfCalls From Responders
Where Responder15 is not null and Responder15 <> ''
GROUP BY Responder15

should do the job.

You can do the entire job in one query by the way
Select ResponderFF,Sum(NumberOfCalls) as NumberOfCalls
From (***) dummyalias
Group by ResponderFF

Insert the nasty union query where *** is

You need dummyalias by the way otherwise the SQL command parser belches with incorrect syntax near ")"

Collapse -

I suggest you change the response table

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

First table will be tblFirefighter, you should have following fileds:
FirefighterID (AutoNumber, Primary Key), FirstName(Text), LastName(Text), and other fields you need...
You can combine FirstName and LastName into 1 Name field if you want to.

Second table will be tblResponse, you need following fields:
responseID(AutoNumber, Primary Key), responseDate(Date/Time), firefighterID(Number Long, friend key to tblFirefighter table), other fields you need...

Once you have these two tables build up, fill them up. Each time you get a call, just add 1 record in response table with date, firefighterID (use a combox to select by name) and other necessary info.

Here is the sample annual reponses report query for year 2006 based on the two tables above:

SELECT [FirstName] & " " & [LastName] AS Firefighter, Count(tblResponse.ResponseID) AS TotalResponse
FROM tblFirefighter INNER JOIN tblResponse ON tblFirefighter.FirefighterID = tblResponse.FirefighterID
WHERE (((Year([ResponseDate]))=2006))
GROUP BY [FirstName] & " " & [LastName]
ORDER BY Count(tblResponse.ResponseID) DESC;

Collapse -

I see where you are going

by toddmehl In reply to I suggest you change the ...

I guess I have left too vague of a description of what is being done. This is where I am:

I already have the following tables made (I did make them into a better format than how they are actually written below):
-Member information- name, address, phone...
-Responders for each call
-Information obtained for every call- address, dates, run number...

That is then related to the following tables:
-Specific table for medical calls
-Specific Table for car accidents
-Specific Table for HAZMAT/other situations
-Common info for all fires- ignition source, amount of water used, % damage...
The common table for fires then relates to specific tables for:
-Car fires
-Brush fires
-Structure fires

The responders table will be filled out from a combo box on the main form. The combo box will have results from a query of the member information tables, listing only the active members of the department.

With that mouthful said, I will HOPEFULLY be able to set up a form that incorportates all of those little subforms. All you need to do is click on an expand icon to fill out the pertinent sections of the run report.

At the end of the year, I want it so the Chief has to click on one button and it prints out the summary of the year- from number of fires, to number of responders for calls, to which parts of the district have the most activity... basic compilation of data.

Hopefully, I haven't bored you too much with the details. Thank you for your time!

Todd

Back to Software Forum
14 total posts (Page 1 of 2)   01 | 02   Next

Software Forums