Group by query question

[Joseph Greenberg]

I have a query which is supposed to (and does) return the earliest record
from a table that might have multiple records per "person" (famno). "dis is
a calculated field in a pervious query that calculate how many days until a
certain event (so a person could have 2 or more records, each with a
different value for dis). It works properly:

SELECT yahr_dist_from_today_greg.FAMNO, Min(yahr_dist_from_today_greg.dis)
AS MinOfdis, Min(Date()+[dis]) AS NextYZ
FROM yahr_dist_from_today_greg
GROUP BY yahr_dist_from_today_greg.FAMNO;

I have another field in the query, Relp, that I want to simply bring over to
the final query output (it's a number field). When I add Relp to my query,
it adds records to the output. When I am doing a GROUP BY, it seems to not
like having an associated variable come in from the record. Any ideas? Does
this make any sense to anyone?

Joseph

 

[KARL DEWEY]

The problem is that there is not a single Relp that can be assoicated with
your Min(yahr_dist_from_today_greg.dis) AS MinOfdis and
Min(Date()+[dis]) AS NextYZ.

How can you relate that field with the date calculation?

 

[KenSheridan via AccessMonster.com]

Joseph:

By using a subquery to find the MIN(dis) value for each famno you can
restrict the outer query to the rows with this value per famno and include
any or all columns from that row:

SELECT famno, relp, dis
FROM yahr_dist_from_today_greg AS YDTG1
WHERE dis =
(SELECT MIN(dis)
FROM yahr_dist_from_today_greg AS YDTG2
WHERE YDTG2.famno = YDTG1.famno);

Note how each instance of yahr_dist_from_today_greg is distinguished by the
aliases YDTG1 and YDTG2, enabling the subquery to be correlated with the
outer query on the famno column.

Ken Sheridan
Stafford, England

I have a query which is supposed to (and does) return the earliest record
from a table that might have multiple records per "person" (famno). "dis is
a calculated field in a pervious query that calculate how many days until a
certain event (so a person could have 2 or more records, each with a
different value for dis). It works properly:

SELECT yahr_dist_from_today_greg.FAMNO, Min(yahr_dist_from_today_greg.dis)
AS MinOfdis, Min(Date()+[dis]) AS NextYZ
FROM yahr_dist_from_today_greg
GROUP BY yahr_dist_from_today_greg.FAMNO;

I have another field in the query, Relp, that I want to simply bring over to
the final query output (it's a number field). When I add Relp to my query,
it adds records to the output. When I am doing a GROUP BY, it seems to not
like having an associated variable come in from the record. Any ideas? Does
this make any sense to anyone?

Joseph