Selecting only the most recent record in a child table

[headly]

My child table has many records for each item in the parent table; how would
i go about selecting only the most recent entry? (Note: each record has a
unique date)

 

[Michel Walsh]

Hi,


You can take any of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm. The first one, using two
queries, is probably the easiest one.



Hoping it may help,
Vanderghast, Access MVP

 

[headly]

That was a great link i hadn't seen before, thanks for showing me where to
find a helpful answer.
Now I've been asked to show the last two entries in the child table and am
not sure what methodology to pursue - Ideas greatly appreciated.

 

[Michel Walsh]

Hi,


A "TOP N By Group" kind of problem, such as finding the top 5
salesmen per country:

=================

SELECT a.country, a.salesman, a.TotalSales

FROM myTable As a

WHERE a.salesman = (SELECT TOP 5 b.salesman
FROM myTable As b
WHERE b.country=a.country
ORDER BY b.TotalSale DESC)

ORDER BY a.country, a.TotalSale DESC

==================



Another solution is to RANK BY GROUP, and keep those with a rank less or
equal to 5. A possible solution is:

=================

SELECT a.country, a.salesman, a.TotalSales, COUNT(b.country) As rank

FROM myTable As a LEFT JOIN myTable As b
ON a.country=b.country AND a.TotalSale < b.TotalSale

GROUP BY a.country, a.salesman, a.TotalSales

HAVING COUNT(b.country) <= 4

ORDER BY COUNT(b.country)

==================

where COUNT(b.country) returns the rank, within a given country, in this
context, but as a value from 0 to N-1, where N = number of salesmen in a
given country.


You can use other solution to compute the ranks, the idea is just to THEN
keep only the records with a rank <= a given fixed limit.



Hoping it may help,
Vanderghast, Access MVP

 

[headly]

That was incredibly useful advice.

When I try your code with TOP 1, my query runs okay, but when I use any
other number, like TOP 2, I get an error message

At most 1 record can be returned by this subquery

Could this be because some of my children only have 1 record, not multiple?
Is there an if statement workaround to that? I tried a few If statements with
no luck. Thoughts appreciated.

 

[Michel Walsh]

Hi,


Indeed, replace the = ( ) by IN ( ) :

.... WHERE a.salesman IN (SELECT TOP 5 b.salesman ...

rather than

.... WHERE a.salesman = (SELECT TOP 5 b.salesman ...


You can also use =ANY if salesman cannot be NULL:

.... WHERE a.salesman = ANY (SELECT TOP 5 b.salesman ...


but IN( ) won't suffer the potential problem we can get with =ANY( ) if
there is a possibility to have a NULL value



Hoping it may help,
Vanderghast, Access MVP