sub query problem (1 Viewer)

[remorhaz]

Hi there - working on a laptop checkout system. Three tables only named:

Computers
Checkout
Checkin

Primary key on computers is subkey on checkout and checkin, one to many relationship (because a single computer can be checked out and checked in multiple times).

When doing a checkin I need to grab the information from the most recent row checkout for a say laptop number 20. Here is my query for this...

SELECT * FROM Checkout
WHERE 'Checkout.dateissued' IN (SELECT MAX('Checkout.dateissued')
FROM Checkout WHERE 'Checkout.computerID' = '16') AND 'Checkout.computerID' = '16';

The '16' is the primary key in the Computers table and foreign key in the Checkout/Checkin table. This is in Access 2007.

Here is a sample from the checkout table...

Name_ dateissued____ datereturned__ComputerID
Joe____8/11/2009____ 8/19/2010_______16
Tina___ 8/21/2010____________________16

What I want is the info for Tina. Both Joe and Tina have checked out the lappie but I want the last one with no date returned. Although it should NOT happen I have been ordered to not simply check for null on date returned and filter from this as if someone forgets to checkin the lappie then I would get 2 results. Can someone please give me the *exact* SQL to use here? I have switched out the single quotes and stuff and keep getting no records returned.

Name is text
dateissued is date type
datereturned is date type
computerID is Number (Integer) and foreign key from ID primary key on Computers table

Thank you all!

 

[remorhaz]

I should add this:

SELECT * FROM Checkout
WHERE Checkout.dateissued IN (SELECT MAX(Checkout.dateissued)
FROM Checkout WHERE Checkout.computerID =16) AND Checkout.computerID = 16;

When I run this in SQL mode I get a Enter parameter value popup labeled "Query1.Expr1000" asking for a value. No matter WHAT i put into that dialog box I get the row set I am looking for but I do not understand why I am getting the popup...

 

[remorhaz]

Ugh... never mind..... just found the stupid problem. I was "helped" by Access in that the 'Order by' field in the properties section had a Expr.1000 sitting there. I never wrote that but as soon as I deleted it the query worked.