CODEWITHSUNDEEP
ms-accesssubquery
Micah
Micah

Reputation: 514

MS Access 2007 Subquery only returning one result

I'm having trouble with Microsoft Access. I have a table of transactions for a video store, which includes the video # and individual copy # of a particular movie, as well as when the movie was checked out and returned. When I run a subquery on it, I'm looking to find the list of all movies rented most recents (for example one customer came in and picked out 4 DVDs and rented them).

This code only returns one result:

SELECT VideoTitle, RentDate, Rent
FROM Rentals
WHERE RentDate IN (SELECT Max(RentDate) FROM RENTALS;);

But in the data set, there are indeed 4 movies with the same rent date. Any thoughts on why it's not giving me 4 results?

Upvotes: 1

Views: 429

Answers (1)

Fionnuala
Fionnuala

Reputation: 91326

Are you sure you do not have a time attached to that date?

SELECT VideoTitle, RentDate, Rent
FROM Rentals
WHERE DateValue(RentDate) IN (SELECT Max(DateValue(RentDate)) FROM RENTALS;);

Upvotes: 3

Related Questions