Query: Returning 3 most recent records for each parent record...

[jcarroll]

Hi,<br>&nbsp;<br>I am having trouble constructing a query which will pull out the three most recent child records for each parent record in a table.&nbsp;&nbsp;I tried using the top statement, however, this only succeeds in pulling out the three most recent records in the entire child table, not the three most recent for each parent.&nbsp;&nbsp;I'm thinking this might have something to do with a query within a query but I don't know.<br>&nbsp;<br>Any and All help would be greatly appreciated.<br>&nbsp;<br>Justin

 

[rochelle]

I think I would try creating a temporary column which counts what number child this is for each parent -- make sure the table is sorted in descending order by date so that the most recent record is numbered 1.&nbsp;&nbsp;This query should then be the basis for a second query which selects only this records where the new number field equals 1, 2, or 3.<br><br>You can get a new count from 1 for each parent by creating a new column whose value is 1 in the Totals query and setting the Total row to Sum.<br><br>I haven't tried it, but this is the general direction I would try.

 

[jcarroll]

Thanks rochelle, I was eventually able to solve this problem however I did not get around to trying your solution.&nbsp;&nbsp;Instead I ended up using a MS KB article Q153747, which detailed a custom function as a criteria for my query.<br><br>Justin