Questions

Help with querying a database in Microsoft Access

+
0 Votes
Locked

Help with querying a database in Microsoft Access

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!
  • +
    0 Votes
    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? :)

    +
    0 Votes

    Yes

    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

    +
    0 Votes
    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

    +
    0 Votes
    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!

    +
    0 Votes
    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.

    +
    0 Votes
    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

    +
    0 Votes
    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

    +
    0 Votes
    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 ")"

    +
    0 Votes
    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;

    +
    0 Votes
    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

    +
    0 Votes
    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...

    +
    0 Votes
    Tony Hopkinson

    that will work fine.

    +
    0 Votes
    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

  • +
    0 Votes
    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? :)

    +
    0 Votes

    Yes

    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

    +
    0 Votes
    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

    +
    0 Votes
    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!

    +
    0 Votes
    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.

    +
    0 Votes
    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

    +
    0 Votes
    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

    +
    0 Votes
    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 ")"

    +
    0 Votes
    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;

    +
    0 Votes
    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

    +
    0 Votes
    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...

    +
    0 Votes
    Tony Hopkinson

    that will work fine.

    +
    0 Votes
    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