Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL query question

Status
Not open for further replies.
vangundy

vangundy

Programmer
Jan 29, 2005
38
CA
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?
 
Sort by date Sort by votes
you could try something like

select book,author,max(datebought)
from tblname
group by author

not sure if i'm missing something, doing it quickly from head
 
Upvote 0 Downvote
Just make sure you also group by the book as well as the author. Otherwise, it'll produce an error.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)
 
Upvote 0 Downvote
Even though the table contains data related to a book you have to account for the fact an author can write MANY books so....

Since an author may write many books a one-to-many relationship may be used. Although your set up will work...it's not "normalized", it's a "flat" database. Join then use stevens/chopsticks qry

tblBooks tblAuthors
------------- ---------------
BookID(Pri) ->1 AuthorID(Pri)
AuthorID(F)8-----------/ Author
Title
DateBought

 
Upvote 0 Downvote
As an aditional thought, you might want to set-up 3 tables..

1. Books
2. Authors
3. SalesHistory (or purchases or whatever you fancy..)

So as not to confuse individual books with the many sales of those books. (One Author can write many books, each book can have many sales)

Assuming you set-up three tables:

Code: 
  SELECT Books.Book, Authors.Author, Max(SalesHistory.DateBought) AS LastPurchaseDate, Count(*) AS TotalPurchased
  FROM Authors 
    RIGHT JOIN 
          ( SalesHistory RIGHT JOIN Books 
            ON SalesHistory.BookID = Books.BookID ) 
          ON Authors.AuthorID = Books.AuthorID
  GROUP BY Books.Book, Authors.Author;

Hope this helps.
Damian

A smile is worth a thousand kind words. So smile, it's easy! :)
 
Upvote 0 Downvote
yup..is true is true...i was gonna suggest that too but i doubt he goes that way...i imagine his databse is aleady filled w/ data...but in the future van try to normalize the database structure...this is extremely important...good luck!

BSL
 
Upvote 0 Downvote
FIRST I THANK YOU ALL FOR YOUR SUGGESTIONS BUT NO LUCK AS OF YET!!

I tried this as an example: (Here I have two tables; Inventory and Reservation)

Inventory table contains all the Books and Reservation table contains reservations made on that book:

the relationship between the two tables is BookName and I am using a (Include All Records From Inventory and only those records from Reservation where the joined fields are equal.) This is option 2 in Join Properties (Access 2000).

This will display everything in Table1 (Inventory and will display all reservations in the Reservation table. IF THERE ARE NO RESERVATIONS I STILL WANT ALL THE BOOK NAMES TO APPEAR. SO IF THERE ARE 22 BOOKS then the query should display the results with 22 Books...

Here is my query I tried using your suggestion:


SELECT Reservation.BookName, Reservation.Date_Return, Max(Reservation.Date_Out) AS MaxOfDate_Out
FROM Inventory LEFT JOIN Reservation ON Inventory.BookName = Reservation.BookName
GROUP BY Reservation.BookName, Reservation.Date_Return;


I had no luck.... there must be a way with one query!!

Man oh man...



 
Upvote 0 Downvote
try this to display all books and reservations and no reservations

SELECT Inventory.BookName, Reservation.Date_Return,
FROM Inventory
LEFT JOIN Reservation
ON Inventory.BookName = Reservation.BookName
 
Upvote 0 Downvote
This is the query I originally started off with... only problem is it will display all the Books and all the reservations.. If one book has three reservations,

BookName DateOut
Under Sea 04/06/2005
Tom Sawyer 04/12/2005
Rogers Ltd 04/07/2005
Tom Sawyer 04/01/2005
Tom Sawyer 04/06/2005


As you can see Tom Sawyer is reserved 3 times. I want to display the reservation which is the first >= todays date so it is displayed as follows:

BookName DateOut
Under Sea 04/06/2005
Rogers Ltd 04/07/2005
Tom Sawyer 04/06/2005

* I dont care about Tom Sawyer on the 01 of April because that day has passed. And the 6th of April comes before the 12th..

Any ideas?

 
Upvote 0 Downvote
In this case if I use max it would display
Tom Sawyer 04/12/2005
 
Upvote 0 Downvote
use max(MaxOfDate_Out) in a where clause...this is why you are getting more than 1 result
 
Upvote 0 Downvote
select bookname,max(dateout)
from tblname
group by bookname
having dateout <= date()
 
Upvote 0 Downvote
bslintx what he is saying is that max(dateout) may pick a date that is greater than today, he needs max up to today - 4/12 is in future so its wrong
 
Upvote 0 Downvote
van,

didn't see a reply so i went ahead and tested my where clause and it worked...just had to plug-in the date

see it here:
sql statement
Code: 
strSQL="SELECT Inventory.BookName, Max(Reservation.DateOut) AS MaxOfDateOut " & _
         "FROM Inventory INNER JOIN Reservation ON Inventory.InventoryID = Reservation.InventoryID " & _
         "WHERE (((Reservation.DateOut)<=Date())) " & _
         "GROUP BY Inventory.BookName " & _
         "ORDER BY Max(Reservation.DateOut) DESC;"

hope it's what you needed..good luck

Brian
 
Upvote 0 Downvote
It actually resolves the issue with the same book multiple times, but if there is no reservation for a book it should still show up in the query..
 
Upvote 0 Downvote
Then you should probably LEFT JOIN your tables together so that you pick up all books regardless of the reservation. This may have other problems for you, though.

Another thought is that you do a separate query for the books without a reservation date and then UNION the two together to create your recordset.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
389
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
fso Question 1
Replies
3
Views
141
Offera
Offera
leifoet
  • Locked
  • Question
ASP Classic - SELECT query does not work
Replies
11
Views
313
guitarzan
guitarzan
leifoet
  • Locked
  • Question
How to compare dates in a SELECT query
Replies
12
Views
246
guitarzan
guitarzan
leifoet
  • Locked
  • Question
How do I save a list of the screen created with a select query
Replies
1
Views
100
ChrisHirst
ChrisHirst

Part and Inventory Search

Sponsor

Back
Top