Inverse query - greatly puzzling!

[Richard Williamson]

Hi all,
I am trying to create a query which will select the inverse of the
records returned.

Scenario:
Member(MEMBERID,name, address,etc)
Video(VIDEOID, title, certificate, etc.)
Rentals(RENTALID, memberid, videoid, datedueback, returned)

The videos currently rented out are the ones where returned = no. How
can I do an inverse of this, ie. the ones which ARE in stock?

Regards
Richard W

[Allen Browne]

A video is still out if Returned = No for its most recent rental (based on
DateDueBack).

Use a subquery in the Where clause:

SELECT Video.* FROM Video
WHERE (SELECT TOP 1 Returned FROM Rentals
WHERE (Rentals.VideoID = Video.VideoID)
ORDER BY Rentals.DateDueBack, Rentals.RentalID ) = False;

If subqueries are a new idea, you can read more about them in:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

BTW, the above assumes that VideoID uniquely identifies a copy of a video.
You don't actually hire out a video title, but a particular instance (copy)
of the disk/tape.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard Williamson" <ri*****************@ntlworld.com> wrote in message
news:62*************************@posting.google.co m...

Hi all,
I am trying to create a query which will select the inverse of the
records returned.

Scenario:
Member(MEMBERID,name, address,etc)
Video(VIDEOID, title, certificate, etc.)
Rentals(RENTALID, memberid, videoid, datedueback, returned)

The videos currently rented out are the ones where returned = no. How
can I do an inverse of this, ie. the ones which ARE in stock?

Regards
Richard W