SQL in MS Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL in MS Access

Author

Topic

mrmicke
Starting Member

1 Post

Posted - 2002-11-06 : 14:57:28

Hi there
I´m having a bigproblem that I hope someone could help me with. My SQL-question (in Access) won´t work the way I want.

I have 3 tables:

BID
*Pnr (text) (foreign key from Person-table
*BookID (Number) (foreign Key from Book-table)
-Bid (Number)

BOOK
*BookID (number)
-Author (text)
-Title (text)

PERSON
*Pnr
-Firstname
-Lastname

What I want from the SQL question is to get a list over every book that a certain Pnr (= Personalnumber, a swedish way to make a person unike) has made a bid on where his bid is the highest. Containing the following columns:
BookID, Author, Title, Max-bid, Firstname, Lastname (for the three last columns I want the bid and names for the highest bidder)

OR

A list over every book that a certain pnr has made a bid on and the persons bid is lower that highest book on that bid. The list would contain:
BookID, Author, Title (and if possible the highest bid)

AND

A list over every book that a certain pnr has made a bid on and the persons bid is the highest bid on the book. The list would contain:
BookID, Author, Title (and if possible the highest bid).

I´ve torn my hair over this but can´t get it to work. Can someone please help me.

I´ve got one answear on another forum but that code couldn´t help me. Maybe someone else can see what´s wrong with it and thereby help me.
---
select BOOK.BookID, Author, Title, BID.Bid as MaxBid , Firstname, Lastname
from PERSON inner join BID on PERSON.Pnr = BID.Pnr
inner join BOOK on BID.BookID = BOOK.BookID
where PERSON.Pnr = '12345'
and BID.Bid =
(select Max(Bid)
from BID
where BookID = BOOK.Bookid);

---

Plz help me.

/Micke

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-06 : 15:32:12

Access has extremely limited support for subqueries. Therefore I think you'll need to create a stand-alone query that finds the maximum bid for each title:

SELECT BookID, Max(Bid) AS MaxBid
FROM BID
GROUP BY BookID

Put that in it's own query and name it something like MaxBids, for example. Then you can use it in the following:

select BOOK.BookID, Author, Title, M.MaxBid as MaxBid , Firstname, Lastname
from PERSON inner join BID on PERSON.Pnr = BID.Pnr
inner join BOOK on BID.BookID = BOOK.BookID
INNER JOIN MaxBids AS M ON M.MaxBid=BID.BID AND M.BookID=BID.BookID
where PERSON.Pnr = '12345'

The same applies for finding bids that are lower than the max bid, you just need to change the join criteria:

INNER JOIN MaxBids AS M ON M.MaxBid > BID.BID AND M.BookID=BID.BookID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources