This is the last query I am struggling with in my project. I am using MS Access 2007. I am a newbie and thus will try my best to explain my problem.
My system is like eBay but only sells books. Customers register, place order for a book, choose whether to directly buy or place bid, are then taken to submit bid if they chose to bid. Internally as soon as they placed their bids, a database populates all the bids for books.
The query I am struggling with intends to do this - selecting highest bid for each book and display three columns - 1) Customer, 2) Book, 3) Bid amount (highest) so we know the winner.
I have two tables - 1) customerordersbook - contains customerID, bookID, and a flag that decides if it is a bid or a direct buy (obviously we are talking about when the flag is set. and 2) bid - that has the bidamount, bidtime, etc.
FROM CustomerOrdersBook LEFT JOIN Bid ON CustomerOrdersBook.cobID=Bid.bidCobOrderID /* join is made on the two entries I kept common in two tables */
WHERE ((CustomerOrdersBook.cobIsThisABid)=True) /* only chooses the bid checked flag valueds */
AND Bid.bidAmount = (SELECT MAX(Bid.bidAmount) FROM Bid);
The query works, but only displays the HIGHEST amount in the bidAmount column. If I insert GROUP BY statement, the best I have gotten so far is two columns - bookID, and highest price. But then the customerID is left out