tricky nested repeat problem - how to show one field from another table?

[Newsgroup_User]

Hi. I have a tricky problem which I'm struggling to work out. I'm using DW
8.0.2 and VBScript and Access.

I have two tables in my Access database:
tableAuction (auctionID, auctiontitle, startingprice)
tableBids (bidID, auctionID, bidderID, bidamount)

I have a recordset on one of my ASP pages which displays a list of available
auctions. This is held in a repeat region should there be more than one
auction running at any one time.
However, I would like to be able to display the highest bid amount for each
auction on this page, but it appears, because I'm trying to insert this data
within an existing recordsets repeat region, that it isn't possible? I've
tried the following SELECT statement, but the problem with it is that it
lists the full auction details for every bid there has been. I don't appear
to be able to "GROUP BY" to prevent this from happening. Here's what I
tried:

SELECT qryAuction.auctionID, auctiontitle, qryBids.auctionID,
qryBids.bidamount
FROM qryAuction, qryBids
WHERE qryAuction.auctionID = qryBids.auctionID

Is it possible to group, by auctionID, the list of auctions produced by the
above statement so that only one occurance of any auction appears in the
recordset? I also need to ensure that this one occurence brings, with it,
the highest bid amount from the bids table?

Or am I going about this ALL wrong? <hides>

Would really appreciate some guidance, thanks.

Nath.

[Newsgroup_User]

"tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
news:f1uqvc$omg$1@forums.macromedia.com...
> SELECT qryAuction.auctionID, auctiontitle, qryBids.auctionID,
> qryBids.bidamount
> FROM qryAuction, qryBids
> WHERE qryAuction.auctionID = qryBids.auctionID
>
> Is it possible to group, by auctionID, the list of auctions produced by
> the above statement so that only one occurance of any auction appears in
> the recordset? I also need to ensure that this one occurence brings, with
> it, the highest bid amount from the bids table?
>

Why do you think you can't GROUP BY?
(I also noticed you selected auctionID twice, so I removed that little
redundancy.)

SELECT qryAuction.auctionID, auctiontitle, MAX(qryBids.bidamount) AS
HighestBid
FROM qryAuction, qryBids
WHERE qryAuction.auctionID = qryBids.auctionID
GROUP BY qryAuction.auctionID, auctiontitle

You will have a problem, though. An auction with no bids will be left out
of the above query.
Access should understand this:

SELECT qryAuction.auctionID, auctiontitle, MAX(qryBids.bidamount) AS
HighestBid
FROM qryAuction LEFT OUTER JOIN qryBids ON qryAuction.auctionID =
qryBids.auctionID
GROUP BY qryAuction.auctionID, auctiontitle

If not, then in your WHERE, add a * next to the equal sign
(qryAuction.auctionID *= qryBids.auctionID).
The * might go on the other side.... I can never remember. :)

Hope this helps.