need help on building a query

[John Doe]

I have two tables:

Table 1 = Documents
Table 2 = Reviews

Documents is related one-to-many to Reviews. (Reviews is the many
side.) The Documents table contains the information about a document.
The Reviews table contains information about each review performed on
a document. So a document can have many reviews.

One field in the Reviews table is the date that the review was
performed. So Document X could have, let's say, 4 reviews, each on a
different date.

I want to display in a list box a listing of each document with a
specific status and the last review for each document that has that
status.

It seems I can get the SQL query to get the reviews for all documents
with that status, but it lists ALL the reviews for each document. That
is, if Document A has 3 reviews and Document X has 2 reviews, I get
the following listing:

Document A Review 1
Document A Review 2
Document A Review 3
Document X Review 1
Document X Review 2

I want the document listed only ONE time with the LAST review:

Document A Review 3
Document X Review 2

Here is the SQL I have:

SELECT Documents.DocumentTitle, Documents.DocumentStatus,
Reviews.ReviewDate
FROM Documents INNER JOIN Reviews ON Documents.idDocument =
Reviews.idDocument
WHERE (((Documents.DocumentStatus)=1))
ORDER BY Documents.DocumentTitle;

I know this is really simple but I can't figure it out because I don't
know SQL very well yet.

Any suggestions?

 

[MGFoster]

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

Try:

SELECT D.DocumentTitle, D.DocumentStatus, R.ReviewDate
FROM Documents D INNER JOIN Reviews R ON D.idDocument = R.idDocument
WHERE D.DocumentStatus = 1
AND R.ReviewDate = (SELECT MAX(ReviewDate) FROM Reviews
WHERE idDocument = D.idDocument)
ORDER BY D.DocumentTitle

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQeR/boechKqOuFEgEQIMvQCg8RmPsJdAniO2ffy4GM44lxMxZNkAn1TN
4JyN+GGoR83Jnr8hSoSaIPIQ
=3Jck
-----END PGP SIGNATURE-----

 

[John Doe]

YES!!

It worked!!

Thank you very much.