Retrieve First Value From Each Group

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi

I have historical data joined by a Union query.

The data shows who occupied a particular location and the order in which
they lived there. The person with the highest History number is the current
resident. I need to retrieve only the current residents in my report. My
union query is as follows:

SELECT Member, strDistrict, strLandLocation, bytHistory
FROM tblResVotingListTemp

UNION ALL

SELECT Member, strDistrict, strLandLocation, bytHistory
FROM tblBusVotingListTemp

ORDER BY strLandLocation ASC, bytHistory DESC;

I'm not sure if I should be trying to accomplish this with the query (my
preference) or as a filter of some sort in the report. I don't know how to
do either......thus my question!

Thanks
CJ
 
CJ said:
(...) I need to retrieve only the current residents in my report. (...)
Click to expand...

I will assume that strLandLocation identifies the location. This means
that there cannot exist two different locations with the same value of
strLandLocation attribute. The primary key of both tables is
( strLandLocation, bytHistory ).

I will assume that if a location is in the table tblResVotingListTemp
then it cannot be in tblBusVotingListTemp, and vice versa.

SELECT
Member
, strDistrict
, strLandLocation
, bytHistory
FROM
tblResVotingListTemp AS T1
WHERE
bytHistory = (
SELECT
MAX(bytHistory)
FROM
tblResVotingListTemp AS T2
WHERE
T2.strLandLocation = T1.strLandLocation
)

UNION ALL

SELECT
Member
, strDistrict
, strLandLocation
, bytHistory
FROM
tblBusVotingListTemp AS T3
WHERE
bytHistory = (
SELECT
MAX(bytHistory)
FROM
tblBusVotingListTemp AS T4
WHERE
T4.strLandLocation = T3.strLandLocation
)

ORDER BY
strLandLocation ASC
, bytHistory DESC
;

If the same location can be in both tables, then you could store your
original query (named as "Q"), and do this:

SELECT
Member
, strDistrict
, strLandLocation
, bytHistory
FROM
Q AS T1
WHERE
bytHistory = (
SELECT
MAX(bytHistory)
FROM
Q AS T2
WHERE
T2.strLandLocation = T1.strLandLocation
)

Hope I got it right - I tested this on some simple test data in Acc
2002. Don't know about the performance of such query.

Tom
 
Excellent, thanks Van.

A couple of questions regarding the two methods.

Method 1: How do you add a Criteria statement in a Union Query? Would that
be a Where statement?

Method 2: The code uses a SearchTable called Orders. Would this work through
my query or do I need to turn it into a make table query and then search the
table?

Thanks again Van
 
Hi Tom, really close, but not quite.

What happens when I try both methods is the following:

Method 1: The land location can be in both tables, so the query returns the
highest bytHistory for each location in each table. So, I might still end up
with two listings for each location.

Method 2: Returns just one record, but it has the highest bytHistory of all
of the records in both tables.

CJ
 
Never mind, I got it!

Thanks
CJ said:
Excellent, thanks Van.

A couple of questions regarding the two methods.

Method 1: How do you add a Criteria statement in a Union Query? Would
that be a Where statement?

Method 2: The code uses a SearchTable called Orders. Would this work
through my query or do I need to turn it into a make table query and then
search the table?

Thanks again Van
Click to expand...
 
Hm... I am a bit confused on what do you acctually need. What do "Res.
voting list" and "Bus voting list" mean? What is wrong with this result,
and how should correct result look like:

tblResVotingList := ( memb, distr, loc, hist )
{
M1 D1 L1 1
M1 D1 L1 2
}

tblBusVotingList := ( memb, distr, loc, hist )
{
M3 D1 L1 3
}

Method 2 result set
{
M3 D1 L1 3
}


It seems you have the alternate solution, but if you still want help
with "SQL only" solution, I'd need this explained.

Tom
 
Back
Top