Software

Ask something
Question Locked

Help with querying a database in Microsoft Access

0 helpful
14 replies

by toddmehl

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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!

14 replies

by locolobo

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? :)

by toddmehl

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

by tony hopkinson

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

by toddmehl

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!

by tony hopkinson

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.

by fwang

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;

by toddmehl

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

by toddmehl

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

by toddmehl

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

by tony hopkinson

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 ")"

by toddmehl

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...

by jwhite

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

This discussion is closed to new replies.