Query for an Auction
- From: "worldbadger" <worldbadger@xxxxxxxxx>
- Date: 16 Jul 2006 14:19:20 -0700
Hello,
I have an problem. I made a small auction using .asp and access2000
where the user bids and a table holds the following information;
table - bids
ID
auction_id - integer
usernumber - text
lot_no - integer
bid - currency
bidtime(mm/dd/yy hh:mm:ss) - date/time
There is a separate table for the auction lots
ID
auction_id - integer
lot_no - integer
description - text
minbid - currency
vendor - text
What I want is a query where all the following happens(selected from
specific auctionnumber):
1. All lots are selected whether there is a bid or not.
2. If no bids then "n/s" is put in bid column
3. Highest bid only is placed in bid column for multiple bids
4. For identical high bids, the earliest one is selected and a asterix
(*) is put to the right of the highest bid indicating tie bids
Here is what I have so far:
SELECT bids.auction_id, bids.lot_no, Max(bids.bid) AS MaxOfbid
FROM bids
GROUP BY bids.auction_id, bids.lot_no
HAVING (((bids.auction_id)=[Enter Auction No]));
The problem is that this does not give me all lots, does not put "n/s"
in unsold lots, and does not check for tie bids and place an asterix
(*) beside the tie bid amount.
Any help would be appreciated.
worldbadger
.
- Follow-Ups:
- Re: Query for an Auction
- From: Nick 'The Database Guy'
- Re: Query for an Auction
- Prev by Date: Re: INF: Cascade-to-Null relations are supported in JET 4
- Next by Date: Re: typing a VIN number into a combo box VS selecting a value from list
- Previous by thread: typing a VIN number into a combo box VS selecting a value from list
- Next by thread: Re: Query for an Auction
- Index(es):
Relevant Pages
|
