Return records based on a certain value being reached.

[Mike D via AccessMonster.com]

Greetings and thanks for taking the time to read this post.


I am trying to create a query in access (2003) that will return the record
when at least 13 firefighters get on to a fire scene.

The data that is returned from my query qryEffectiveForce is shown below:


Incident # / Response Time (secs) / At Scene Time / Unit / # of FFs

1000 100 13:12:00 engine 4
1000 120 13:12:20 rescue 2
1000 130 13:12:30 engine 4
1000 160 13:13:00 ladder 4
1000 180 13:13:20 engine 4
1000 190 13:13:30 chief 1


With this example then I’d like the record with the ladder’s data to be
returned.

The problem is that 13 could be arrived at by any number of combinations
including 2 rescues, and three engines 2, or 2 engines, 2 rescues, and a
chief. These combinations prohibit me from always querying for the time of
the fourth unit or something like that.

qryEffectiveForce - is sorted by IncidentNumber and ATST (which at least puts
the units arrival sequence in order) and returns the following fields:

Incident Number
ETAS (Response Time in seconds)
ATST (At scene time “hh:nn:ssâ€)
Unit (type)
Personnel (# of FFs)


My question is can I do this in the query designer or is this a vb query
either way I would truly appreciate some insight as to the right direction.
My biggest obstacle with the vb code is determining how to write code that
says for each IncidentNumber count Personnel until Personnel >= 13 return
this record and move to the next incident. I know I am taking a risk by not
displaying any code, but this query is driving me nuts and I can't get a
handle on it. It is not that I havn't tried.


Thanks, Mike

 

[John Spencer]

No one else has answered, so I'll take a crack at this. It looks as if you
are going to need some coordinated subqueries to work this out.

Can't test this, so I'm not sure it will work.

SELECT I.*
FROM [IncidentTable] as I
WHERE I.[At Scene Time] IN
(SELECT TOP 1
[At Scene Time]
FROM [IncidentTable] as I2
WHERE I2.[Incident #] = I.[Incident #] AND
13 <= (SELECT SUM (# of FFs)
FROM [Incidenttable] as I3
WHERE I3.[incident #] = I.[Incident #]
and I3.[At Scene Time] <= I2.[At Scene Time)
ORDER BY I2.[At Scene Time])

By the way, field names with spaces and special characters are a PAIN to
work with.

 

[Guest]

Tested - had to edit a little --
SELECT I.*
FROM IncidentTable AS I
WHERE (((I.[At Scene Time]) In (SELECT TOP 1
[At Scene Time]
FROM [IncidentTable] as I2
WHERE I2.[Incident #] = I.[Incident #] AND
13 <= (SELECT SUM ([# of FFs])
FROM [Incidenttable] as I3
WHERE I3.[incident #] = I.[Incident #]
and I3.[At Scene Time] <= I2.[At Scene Time])
ORDER BY I2.[At Scene Time])));

No one else has answered, so I'll take a crack at this. It looks as if you
are going to need some coordinated subqueries to work this out.

Can't test this, so I'm not sure it will work.

SELECT I.*
FROM [IncidentTable] as I
WHERE I.[At Scene Time] IN
(SELECT TOP 1
[At Scene Time]
FROM [IncidentTable] as I2
WHERE I2.[Incident #] = I.[Incident #] AND
13 <= (SELECT SUM (# of FFs)
FROM [Incidenttable] as I3
WHERE I3.[incident #] = I.[Incident #]
and I3.[At Scene Time] <= I2.[At Scene Time)
ORDER BY I2.[At Scene Time])

By the way, field names with spaces and special characters are a PAIN to
work with.

 

[Mike D via AccessMonster.com]

John and Karl,

Thanks for your help you guys are brilliant, I never could have come up with
something like this. I have tried the SQL statement and so far it is causing
a fatal error in the Access, (no error message, just the dialog that says it
wants to send an error report to Microsoft).

However, now that I have an idea of how to tackle this problem I will work
with the query and see if I can't get it to work and understand it! I have
included the query as I've reworked it to fit with my real field names. I
figured it would be easier for perspective readers to have common language
rather than the codes, but that may have been a bad idea, sorry John.


SELECT I.*
FROM qryaDC_EFPU AS I
WHERE (((I.[atst]) in (SELECT TOP 1 [atst] FROM [qryaDC_EFPU] AS I2
WHERE I2.[ufirs] = I.[ufirs] AND 13 <= (SELECT SUM([appstaffing]) FROM
[qryADC_EFPU] AS I3 WHERE I3.[ufirs] = I.[ufirs] AND I3.[atst] <=I2.[atst])
ORDER BY I2.[ATST])));

qryADC_EFPU: returns all incidents with at aleast 13 FF that made it to the
scene
[atst] : is the time the unit arrived at the scene (time)
[ufirs] : is the incident number (text)
[appstaffing] : is the number of FFs on a particular piece of apparatus
(integer)


Again, thanks for your insight and time.

Mike

Tested - had to edit a little --
SELECT I.*
FROM IncidentTable AS I
WHERE (((I.[At Scene Time]) In (SELECT TOP 1
[At Scene Time]
FROM [IncidentTable] as I2
WHERE I2.[Incident #] = I.[Incident #] AND
13 <= (SELECT SUM ([# of FFs])
FROM [Incidenttable] as I3
WHERE I3.[incident #] = I.[Incident #]
and I3.[At Scene Time] <= I2.[At Scene Time])
ORDER BY I2.[At Scene Time])));

No one else has answered, so I'll take a crack at this. It looks as if you
are going to need some coordinated subqueries to work this out.

[quoted text clipped - 63 lines]

 

[Guest]

I see a typo ---- AND 13 <= (SELECT SUM
to be AND I3 <= (SELECT SUM
That is the letter "I" & number '3' not thirteen.

John and Karl,

Thanks for your help you guys are brilliant, I never could have come up with
something like this. I have tried the SQL statement and so far it is causing
a fatal error in the Access, (no error message, just the dialog that says it
wants to send an error report to Microsoft).

However, now that I have an idea of how to tackle this problem I will work
with the query and see if I can't get it to work and understand it! I have
included the query as I've reworked it to fit with my real field names. I
figured it would be easier for perspective readers to have common language
rather than the codes, but that may have been a bad idea, sorry John.


SELECT I.*
FROM qryaDC_EFPU AS I
WHERE (((I.[atst]) in (SELECT TOP 1 [atst] FROM [qryaDC_EFPU] AS I2
WHERE I2.[ufirs] = I.[ufirs] AND 13 <= (SELECT SUM([appstaffing]) FROM
[qryADC_EFPU] AS I3 WHERE I3.[ufirs] = I.[ufirs] AND I3.[atst] <=I2.[atst])
ORDER BY I2.[ATST])));

qryADC_EFPU: returns all incidents with at aleast 13 FF that made it to the
scene
[atst] : is the time the unit arrived at the scene (time)
[ufirs] : is the incident number (text)
[appstaffing] : is the number of FFs on a particular piece of apparatus
(integer)


Again, thanks for your insight and time.

Mike

Tested - had to edit a little --
SELECT I.*
FROM IncidentTable AS I
WHERE (((I.[At Scene Time]) In (SELECT TOP 1
[At Scene Time]
FROM [IncidentTable] as I2
WHERE I2.[Incident #] = I.[Incident #] AND
13 <= (SELECT SUM ([# of FFs])
FROM [Incidenttable] as I3
WHERE I3.[incident #] = I.[Incident #]
and I3.[At Scene Time] <= I2.[At Scene Time])
ORDER BY I2.[At Scene Time])));

No one else has answered, so I'll take a crack at this. It looks as if you
are going to need some coordinated subqueries to work this out.

[quoted text clipped - 63 lines]

Thanks, Mike

 

[Guest]

OOPS! no typo. It works as is.

I see a typo ---- AND 13 <= (SELECT SUM
to be AND I3 <= (SELECT SUM
That is the letter "I" & number '3' not thirteen.

John and Karl,

Thanks for your help you guys are brilliant, I never could have come up with
something like this. I have tried the SQL statement and so far it is causing
a fatal error in the Access, (no error message, just the dialog that says it
wants to send an error report to Microsoft).

However, now that I have an idea of how to tackle this problem I will work
with the query and see if I can't get it to work and understand it! I have
included the query as I've reworked it to fit with my real field names. I
figured it would be easier for perspective readers to have common language
rather than the codes, but that may have been a bad idea, sorry John.


SELECT I.*
FROM qryaDC_EFPU AS I
WHERE (((I.[atst]) in (SELECT TOP 1 [atst] FROM [qryaDC_EFPU] AS I2
WHERE I2.[ufirs] = I.[ufirs] AND 13 <= (SELECT SUM([appstaffing]) FROM
[qryADC_EFPU] AS I3 WHERE I3.[ufirs] = I.[ufirs] AND I3.[atst] <=I2.[atst])
ORDER BY I2.[ATST])));

qryADC_EFPU: returns all incidents with at aleast 13 FF that made it to the
scene
[atst] : is the time the unit arrived at the scene (time)
[ufirs] : is the incident number (text)
[appstaffing] : is the number of FFs on a particular piece of apparatus
(integer)


Again, thanks for your insight and time.

Mike

Tested - had to edit a little --
SELECT I.*
FROM IncidentTable AS I
WHERE (((I.[At Scene Time]) In (SELECT TOP 1
[At Scene Time]
FROM [IncidentTable] as I2
WHERE I2.[Incident #] = I.[Incident #] AND
13 <= (SELECT SUM ([# of FFs])
FROM [Incidenttable] as I3
WHERE I3.[incident #] = I.[Incident #]
and I3.[At Scene Time] <= I2.[At Scene Time])
ORDER BY I2.[At Scene Time])));

No one else has answered, so I'll take a crack at this. It looks as if you
are going to need some coordinated subqueries to work this out.
[quoted text clipped - 63 lines]

Thanks, Mike

 

[Mike D via AccessMonster.com]

Thanks Karl!

I just received your reply that says that there was no typo.

When running this it is still giving me a fatal error. As a way to try and
understand this query, I have been running different parts of this query on a
copy of the db. The portion of the statement that seems to be causing the
fatal error is

AND 13 <= (SELECT SUM([appstaffing]) FROM [qryADC_EFPU] AS I3 WHERE I3.
[ufirs] = I.[ufirs] AND I3.[atst] <=I2.[atst])

When this is removed from the query the query run and returns the first unit
that arrived on the scene for each incident number. But when this porition
is included it causes a fatal error and closes access.


Again, Thanks for you time

Mike

I see a typo ---- AND 13 <= (SELECT SUM
to be AND I3 <= (SELECT SUM
That is the letter "I" & number '3' not thirteen.

John and Karl,

[quoted text clipped - 45 lines]

 

[John Spencer]

Try it this way
AND (SELECT SUM([appstaffing]) FROM [qryADC_EFPU] AS I3 WHERE I3.
[ufirs] = I.[ufirs] AND I3.[atst] <=I2.[atst]) >= 13

Try running the subquery by itself.

SELECT SUM([appstaffing]) FROM [qryADC_EFPU] AS I3
WHERE I3. [ufirs] = _____ AND I3.[atst] <= ____

Putting valid literal values into the two blanks.

It may be that the source query in combination with the subqueries is the
cause of the problem.

Thanks Karl!

I just received your reply that says that there was no typo.

When running this it is still giving me a fatal error. As a way to try
and
understand this query, I have been running different parts of this query
on a
copy of the db. The portion of the statement that seems to be causing the
fatal error is

AND 13 <= (SELECT SUM([appstaffing]) FROM [qryADC_EFPU] AS I3 WHERE I3.
[ufirs] = I.[ufirs] AND I3.[atst] <=I2.[atst])

When this is removed from the query the query run and returns the first
unit
that arrived on the scene for each incident number. But when this
porition
is included it causes a fatal error and closes access.


Again, Thanks for you time

Mike

I see a typo ---- AND 13 <= (SELECT SUM
to be AND I3 <= (SELECT SUM
That is the letter "I" & number '3' not thirteen.

John and Karl,

[quoted text clipped - 45 lines]

Thanks, Mike