Not sure how to structure this query 2

[solun]

This seems like it ought to be simple, but I'm having trouble with it...

Table consists these fields
BoxID (index/autonumber)
TicketName (Lookup to a list of names & types of tickets)
BoxDate (manually entered short date when the box of tickets was opened)
BoxSerial (manually entered serial number for box)
BinBox (a lookup to the possible bins the ticketbox could be in)


I need to know the most recent date in BoxDate for that BinBox, for all bins in use.

Do I need to structure my data differently?

Thanks much!
Heidi

 

[rjoubert]

SELECT Max(TableName.BoxDate) AS MaxOfBoxDate, TableName.BinBox
FROM TableName
GROUP BY TableName.BinBox

 

[solun]

Many thanks. I need to practice my SQL.

 

[solun]

Ooops, I'm sorry, I need to know what BinID that each is. When I add BinID to the query, it gives me all the records b/c BinID is unique.

 

[lespaul]

SELECT A.BinBox, B.MaxOfBoxDate
FROM TableName A
INNER JOIN (SELECT Max(BoxDate) AS MaxOfBoxDate, BinBox
FROM TableName
GROUP BY TableName.BinBox) B ON A.BinBox = B.BixBox

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[solun]

I interpreted that as....

SELECT TicketBoxes.BinID, TicketBoxes_1.MaxOfBoxDate, TicketBoxes.BoxID
FROM TicketBoxes
INNER JOIN (SELECT Max(BoxDate) AS MaxOfBoxDate, BinID
FROM TicketBoxes_1
GROUP BY TicketBoxes_1.BinID) ON TicketBoxes_1.BinID = TicketBoxes.BinID;

... which results in "Syntax error in JOIN operation"

 

[PHV]

Perhaps this ?
SELECT A.BinID, A.BoxDate, A.BoxID
FROM TicketBoxes AS A INNER JOIN (
SELECT Max(BoxDate) AS MaxOfBoxDate, BinID FROM TicketBoxes GROUP BY BinID
) AS B ON A.BinID = B.BinID AND A.BoxDate = B.MaxOfBoxDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[solun]

Beautiful!!! Thank you!!

 

[solun]

Okay, I'm using this:

SELECT A.BoxID, A.BinID, A.TicketID, A.BoxSerial, A.BoxDate
FROM TicketBoxes AS A INNER JOIN (
SELECT Max(BoxDate) AS MaxOfBoxDate, BinID FROM TicketBoxes GROUP BY BinID )
AS B ON (A.BoxDate=B.MaxOfBoxDate) AND (A.BinID=B.BinID)
ORDER BY A.BinID;


Access will run it whether it's saved in design view or SQL, but I can't use the query as a lookup in a table or form - as soon as I do, the SQL changes to:

SELECT A.BoxID, A.BinID, A.TicketID, A.BoxSerial, A.BoxDate
FROM TicketBoxes AS A INNER JOIN [SELECT Max(BoxDate) AS MaxOfBoxDate, BinID FROM TicketBoxes GROUP BY BinID ]. AS B ON (A.BoxDate = B.MaxOfBoxDate) AND (A.BinID = B.BinID)
ORDER BY A.BinID;


and i get the error:
"The Microsoft Jet database engine cannot find the input table or query 'SELECT Max(BoxDate) AS MaxOfBoxDate, BinID FROM TicketBoxes GROUP BY BinID'. Make sure it exists and that its name is spelled correctly."

Why??? What can I do to make it stop? How can I use the query in a lookup for a form or table?

Thanks much,

Heidi

 

[PHV]

Weird bug of the SQL grid ...
Create a query named, say, qryLastBin:
SELECT Max(BoxDate) AS MaxOfBoxDate, BinID
FROM TicketBoxes
GROUP BY BinID

And now your query:
SELECT A.BoxID, A.BinID, A.TicketID, A.BoxSerial, A.BoxDate
FROM TicketBoxes AS A INNER JOIN qryLastBin AS B ON (A.BoxDate = B.MaxOfBoxDate) AND (A.BinID = B.BinID)
ORDER BY A.BinID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886