how do i get the last 5 for all assets

[Striker3070]

Need some help with an Access2007 query? Have a database of servers; there
is a linked table of maintenance records. I need to select the top 5 (last
5 maintenance records) for each server.

So for each asset, I need the last 5 maintenance records. I can easily
select Top5, but not sure how to do it for each asset.

Tbl ASSETS
IP address (KEY)
AssetName=txt (DNS name)
LOC=text


Tbl ASSETMaintRec
IP address (Linked to ASSETS)
Date time completed (date/Time) field
Maintenance notes

 

[MGFoster]

Need some help with an Access2007 query? Have a database of servers;
there is a linked table of maintenance records. I need to select the
top 5 (last 5 maintenance records) for each server.

So for each asset, I need the last 5 maintenance records. I can easily
select Top5, but not sure how to do it for each asset.

Tbl ASSETS
IP address (KEY)
AssetName=txt (DNS name)
LOC=text


Tbl ASSETMaintRec
IP address (Linked to ASSETS)
Date time completed (date/Time) field
Maintenance notes

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably this:

SELECT A.AssetName, A.ip_address, A.LOC, M.completion_date
FROM Assets As A INNER JOIN AssetMaintRec As M
ON A.ip_address = M.ip_address
WHERE M.completion_date IN (SELECT TOP 5 completion_date
FROM AssetMaintRec
WHERE ip_address = A.ip_address
ORDER BY completion_date DESC)
ORDER BY A.AssetName, M.completion_date DESC

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSo2lnYechKqOuFEgEQJfOwCfWW3jZGk5o0d1Vd5wZKTbP2kAPQ8AnApU
xAU3Xp5kBqvvWPrzDi5nECSb
=7AF/
-----END PGP SIGNATURE-----