Selecting Specific Records in Group Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query on 2 tables where
Table 1 (tblTAZ-SKIMS) = ALL start and ending nodes (arcs) with their travel
times (3 fields)
Table 2 (tblFacilities) = ending nodes of specific places of interest (1
field)

My query (thus far) is able to pull all the arcs for just my places of
interest using an outer join. The SQL is:

SELECT [tblTAZ-SKIMS].StartTAZ, [tblTAZ-SKIMS].EndTAZ,
[tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities ON [tblTAZ-SKIMS].EndTAZ =
tblFacilities.TAZ

However, (and the crux of my question) I would like to now only display, for
each StartTAZ, the EndTAZ with the minimum TravelTime, rather than all of the
travel times for each combination. To visualize this in output terms, my
original query output looks like this (with only 3 places of interest):

StartTAZ EndTAZ TravelTime
1 1 .23
1 3 5.8
1 7 10.2
2 1 2.6
2 3 5.9
2 7 13.2
3 1 6.2
3 3 .32
3 7 8.9
.... ... ...

And I want the output to look something like this:

StartTAZ EndTAZ TravelTime
1 1 .23
2 1 2.6
3 3 .32
....

Can I implement this in the SAME query, without having to write a second
stage query?
 
Use these two queries unless you know how to use subqueries --

JMSatMetro_1
SELECT JMSatMetro.StartTAZ, Min(JMSatMetro.TravelTime) AS MinOfTravelTime
FROM JMSatMetro
GROUP BY JMSatMetro.StartTAZ;

SELECT JMSatMetro.StartTAZ, JMSatMetro.EndTAZ, JMSatMetro.TravelTime
FROM JMSatMetro INNER JOIN JMSatMetro_1 ON (JMSatMetro.TravelTime =
JMSatMetro_1.MinOfTravelTime) AND (JMSatMetro.StartTAZ =
JMSatMetro_1.StartTAZ);
 
One method would be to use a correlated sub-query in a where clause.

SELECT [tblTAZ-SKIMS].StartTAZ
, [tblTAZ-SKIMS].EndTAZ
, [tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities
ON [tblTAZ-SKIMS].EndTAZ = tblFacilities.TAZ
WHERE TravelTime =
(SELECT Min(TravelTime)
FROM [tblTAZ-SKIMS] as T
WHERE T.StartTaz = [tblTAZ-SKIMS].StartTaz




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Dang missed a closing parenthesis.

SELECT [tblTAZ-SKIMS].StartTAZ
, [tblTAZ-SKIMS].EndTAZ
, [tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities
ON [tblTAZ-SKIMS].EndTAZ = tblFacilities.TAZ
WHERE TravelTime =
(SELECT Min(TravelTime)
FROM [tblTAZ-SKIMS] as T
WHERE T.StartTaz = [tblTAZ-SKIMS].StartTaz)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


John said:
One method would be to use a correlated sub-query in a where clause.

SELECT [tblTAZ-SKIMS].StartTAZ
, [tblTAZ-SKIMS].EndTAZ
, [tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities
ON [tblTAZ-SKIMS].EndTAZ = tblFacilities.TAZ
WHERE TravelTime =
(SELECT Min(TravelTime)
FROM [tblTAZ-SKIMS] as T
WHERE T.StartTaz = [tblTAZ-SKIMS].StartTaz




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a query on 2 tables where
Table 1 (tblTAZ-SKIMS) = ALL start and ending nodes (arcs) with their
travel times (3 fields)
Table 2 (tblFacilities) = ending nodes of specific places of interest
(1 field)

My query (thus far) is able to pull all the arcs for just my places of
interest using an outer join. The SQL is:

SELECT [tblTAZ-SKIMS].StartTAZ, [tblTAZ-SKIMS].EndTAZ,
[tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities ON [tblTAZ-SKIMS].EndTAZ
= tblFacilities.TAZ

However, (and the crux of my question) I would like to now only
display, for each StartTAZ, the EndTAZ with the minimum TravelTime,
rather than all of the travel times for each combination. To
visualize this in output terms, my original query output looks like
this (with only 3 places of interest):

StartTAZ EndTAZ TravelTime
1 1 .23
1 3 5.8
1 7 10.2
2 1 2.6
2 3 5.9
2 7 13.2
3 1 6.2
3 3 .32
3 7 8.9
... ... ...

And I want the output to look something like this:

StartTAZ EndTAZ TravelTime
1 1 .23
2 1 2.6
3 3 .32
...

Can I implement this in the SAME query, without having to write a
second stage query?
Click to expand...
Click to expand...
 
John, thank you. When I implement your suggestion below, I get close, but
off just a bit. My output, with your subquery routine on the WHERE clause,
looks like:

StartTAZ EndTAZ TravelTime
1 1 min time
2 3 ...
2 1 ...
3 3
5 5

The query duplicated StartTAZ #2, simply because my underlying test data had
equal travel times to EndTAZ 3 and 1.

It skipped StartTAZ #4, though. Do you know why?

John Spencer said:
One method would be to use a correlated sub-query in a where clause.

SELECT [tblTAZ-SKIMS].StartTAZ
, [tblTAZ-SKIMS].EndTAZ
, [tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities
ON [tblTAZ-SKIMS].EndTAZ = tblFacilities.TAZ
WHERE TravelTime =
(SELECT Min(TravelTime)
FROM [tblTAZ-SKIMS] as T
WHERE T.StartTaz = [tblTAZ-SKIMS].StartTaz




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a query on 2 tables where
Table 1 (tblTAZ-SKIMS) = ALL start and ending nodes (arcs) with their travel
times (3 fields)
Table 2 (tblFacilities) = ending nodes of specific places of interest (1
field)

My query (thus far) is able to pull all the arcs for just my places of
interest using an outer join. The SQL is:

SELECT [tblTAZ-SKIMS].StartTAZ, [tblTAZ-SKIMS].EndTAZ,
[tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities ON [tblTAZ-SKIMS].EndTAZ =
tblFacilities.TAZ

However, (and the crux of my question) I would like to now only display, for
each StartTAZ, the EndTAZ with the minimum TravelTime, rather than all of the
travel times for each combination. To visualize this in output terms, my
original query output looks like this (with only 3 places of interest):

StartTAZ EndTAZ TravelTime
1 1 .23
1 3 5.8
1 7 10.2
2 1 2.6
2 3 5.9
2 7 13.2
3 1 6.2
3 3 .32
3 7 8.9
... ... ...

And I want the output to look something like this:

StartTAZ EndTAZ TravelTime
1 1 .23
2 1 2.6
3 3 .32
...

Can I implement this in the SAME query, without having to write a second
stage query?
Click to expand...
Click to expand...
 
Not really.

Does StartTax 4 have a record?
Does it have a related recore in tblFacilities?

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John, thank you. When I implement your suggestion below, I get close, but
off just a bit. My output, with your subquery routine on the WHERE clause,
looks like:

StartTAZ EndTAZ TravelTime
1 1 min time
2 3 ...
2 1 ...
3 3
5 5

The query duplicated StartTAZ #2, simply because my underlying test data had
equal travel times to EndTAZ 3 and 1.

It skipped StartTAZ #4, though. Do you know why?

John Spencer said:
One method would be to use a correlated sub-query in a where clause.

SELECT [tblTAZ-SKIMS].StartTAZ
, [tblTAZ-SKIMS].EndTAZ
, [tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities
ON [tblTAZ-SKIMS].EndTAZ = tblFacilities.TAZ
WHERE TravelTime =
(SELECT Min(TravelTime)
FROM [tblTAZ-SKIMS] as T
WHERE T.StartTaz = [tblTAZ-SKIMS].StartTaz




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a query on 2 tables where
Table 1 (tblTAZ-SKIMS) = ALL start and ending nodes (arcs) with their travel
times (3 fields)
Table 2 (tblFacilities) = ending nodes of specific places of interest (1
field)

My query (thus far) is able to pull all the arcs for just my places of
interest using an outer join. The SQL is:

SELECT [tblTAZ-SKIMS].StartTAZ, [tblTAZ-SKIMS].EndTAZ,
[tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities ON [tblTAZ-SKIMS].EndTAZ =
tblFacilities.TAZ

However, (and the crux of my question) I would like to now only display, for
each StartTAZ, the EndTAZ with the minimum TravelTime, rather than all of the
travel times for each combination. To visualize this in output terms, my
original query output looks like this (with only 3 places of interest):

StartTAZ EndTAZ TravelTime
1 1 .23
1 3 5.8
1 7 10.2
2 1 2.6
2 3 5.9
2 7 13.2
3 1 6.2
3 3 .32
3 7 8.9
... ... ...

And I want the output to look something like this:

StartTAZ EndTAZ TravelTime
1 1 .23
2 1 2.6
3 3 .32
...

Can I implement this in the SAME query, without having to write a second
stage query?
Click to expand...
Click to expand...
Click to expand...
 
Yes, StartTAZ does have a record in the tblTAZ-SKIMS table, however, does NOT
have a corresponding EndTAZ key in the tblFacilities table. But neither does
StartTAZ 2, and the query is appropriately pulling this TAZ into the query
output. For some reason, it's just skipping #4.

Any suggestions beyond here would be much appreciated. I can send you the
small mdb file with two tables and the one query if you'd like?

Again, I appreciate your expertise and help in the matter.

Joel

John Spencer said:
Not really.

Does StartTax 4 have a record?
Does it have a related recore in tblFacilities?

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John, thank you. When I implement your suggestion below, I get close, but
off just a bit. My output, with your subquery routine on the WHERE clause,
looks like:

StartTAZ EndTAZ TravelTime
1 1 min time
2 3 ...
2 1 ...
3 3
5 5

The query duplicated StartTAZ #2, simply because my underlying test data had
equal travel times to EndTAZ 3 and 1.

It skipped StartTAZ #4, though. Do you know why?

John Spencer said:
One method would be to use a correlated sub-query in a where clause.

SELECT [tblTAZ-SKIMS].StartTAZ
, [tblTAZ-SKIMS].EndTAZ
, [tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities
ON [tblTAZ-SKIMS].EndTAZ = tblFacilities.TAZ
WHERE TravelTime =
(SELECT Min(TravelTime)
FROM [tblTAZ-SKIMS] as T
WHERE T.StartTaz = [tblTAZ-SKIMS].StartTaz




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


JMSatMetro wrote:
I have a query on 2 tables where
Table 1 (tblTAZ-SKIMS) = ALL start and ending nodes (arcs) with their travel
times (3 fields)
Table 2 (tblFacilities) = ending nodes of specific places of interest (1
field)

My query (thus far) is able to pull all the arcs for just my places of
interest using an outer join. The SQL is:

SELECT [tblTAZ-SKIMS].StartTAZ, [tblTAZ-SKIMS].EndTAZ,
[tblTAZ-SKIMS].TravelTime
FROM [tblTAZ-SKIMS] INNER JOIN tblFacilities ON [tblTAZ-SKIMS].EndTAZ =
tblFacilities.TAZ

However, (and the crux of my question) I would like to now only display, for
each StartTAZ, the EndTAZ with the minimum TravelTime, rather than all of the
travel times for each combination. To visualize this in output terms, my
original query output looks like this (with only 3 places of interest):

StartTAZ EndTAZ TravelTime
1 1 .23
1 3 5.8
1 7 10.2
2 1 2.6
2 3 5.9
2 7 13.2
3 1 6.2
3 3 .32
3 7 8.9
... ... ...

And I want the output to look something like this:

StartTAZ EndTAZ TravelTime
1 1 .23
2 1 2.6
3 3 .32
...

Can I implement this in the SAME query, without having to write a second
stage query?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top