SQL query question

[vangundy]

My table shows as:

Book Author DateBought
Tom Sawyer C Dickens 01/01/01
Spiderman S Smith 04/04/05
Tom Sawyer C Dickens 02/02/02
Spiderman S Smith 04/05/05

As you can see the Book and Authors are the same but not the DateBought. I want to have a query that will display the Book, Author and DateBought one time (the one with the date closest to today) So the end result looks like this:

Book Author DateBought
Tom Sawyer C Dickens 04/05/05
Spiderman S Smith 04/04/05


Is this possible with one query?

 

[vangundy]

RESOLVED - I changed the realtionship to 2.... with your query worked like a charm...

Thank you

 

[Chopstik]

Out of curiosity, then, what was the final table setup and the query you used? I think I am somewhat confused as to what you are/were doing...

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[bslintx]

crap...missed the wanting to list all records w/o reserve date...bummer....i too am curious to what the final query was van...please post resolution code so others can learn by example. thanks and congrats on the fix.

Brian

 

[vangundy]

I used your query but used a LEFT JOIN...

Quick question bslintx... Lets say I want to add two more fields from the Inventory table? I loose the distinct grouping? Lets say we want to add Inventory.ISBN and an Expression... How can I incorporate this into the query...

Again I tried but the query removed the distinct grouping...

Thank you for your help thus far..
Van

 

[bslintx]

van,

quick ? for you....are you ensured that each book WILL have an ISBN #...in other words no older books? If so, you could use this as the PRI key for InvetoryID since ISBNS are unique, this would take care of one of the fields..what do you mean by the expression?

 

[vangundy]

Instead of ISBN lets say field PagesCnt as for the expression I entered it into the statement. I just wanted to add two fields to the query you posted..

SELECT Inventory.BookName, Max(Reservation.DateOut) AS Closest_To_Today, IIf([dateout]=Date(),"available","Reserved") AS Expr, Inventory.PagesCnt
FROM Inventory LEFT JOIN Reservation ON Inventory.BookName = Reservation.BookName
WHERE (((Reservation.DateOut)<=Date() Or (Reservation.DateOut) Is Null))
GROUP BY Inventory.Bookname, IIf([dateout]=Date(),"available","Reserved"), Inventory.PagesCnt
HAVING (((Inventory.BookType)="HardCover"))
ORDER BY Max(Reservation.DateOut) DESC;

This will still show as:
BookName DateOut
Under Sea 04/07/2005
Tom Sawyer 04/07/2005
Tom Sawyer 04/12/2005
Rogers Ltd 04/07/2005