Query to show latest review only

[Valerie Wong]

Hi, I am stuck with the problem in Query and would very much appreciate any
help.

I have two tables, one showing transaction data such as transaction size;
the other shows the review of the transactions, such as performance. Every
now and then we do a review on a transaction, but the number of reviews and
date of reviews for each transaction vary.

In a query, I want to show the transaction size as well as the latest
performance, so how do I avoid showing previous reviews? (One (latest) review
per transaction)

My tables have the following fields:

Transaction data:
- Transaction ID (Primary Key)
- Investment Name
- Transaction Size

Review:
- Review ID
- Transaction ID
- Investment Name
- Date of review
- Current Performance

Many thanks again.

Valerie

 

[Michel Walsh]

You can make two queries, one which find the max, per TransactionID:

SELECT TransactionID, MAX(DateOfReview) AS maxDate
FROM review
GROUP BY TransactionID


then,


SELECT whatever
FROM reviews INNER JOIN previousQuery
ON reviews.TransactionID = previousQuery.TransactionID
AND reviews.DateOfTransaction = previousQuery.maxDate



You can add the relevant data from the first table after that, if required.



Vanderghast, Access MVP

 

[Valerie Wong]

Hi,

Many thanks for the help, it is very useful.

What is the purpose of the line

"AND reviews.DateOfTransaction= previousQuery.maxDate"

?

From my understanding, wouldn't it be

"AND reviews.DateOfReview = previousQuery.maxDate"

?

however it didn't work. Why does it ask me to enter parameter value? HOw come?

 

[Michel Walsh]

There is probably a typo, if it asked for it. Maybe your field name is with
spaces, in such case

AND reviews.[date of review] = previousQuery.[max date]


The easiest way would be to define the second query in the graphical query
editor, where you bring your table reviews and the new query, then, JOIN
them with a drag and drop from reviews.DateOfTransaction onto
previousQuery.maxDate. Do the same for the two transactionID fields.

The goal is to pick only the maximum value for the date (the latest date)
for each transaction ID.


Vanderghast, Access MVP

 

[Michel Walsh]

I should have said: Since the first query already give the latest date, by
transactionID, the last query goal is to get the other FIELDS associated to
these latest dates, given each transactionID.


Vanderghast, Access MVP