Query for an Auction

[worldbadger]

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

[Nick 'The Database Guy']

Hi Worldbadger,

Think is that I would have my doubts about using an access application
with a front end application that can have potentially unlimited users.

Access can only handle, at the very most (and I am open to correction
if anybody has other opinions) 20 users.

Good luck

Nick

worldbadger wrote:

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

[Larry Linson]

"Nick 'The Database Guy'" wrote

Think is that I would have my doubts
about using an access application
with a front end application that can
have potentially unlimited users.

Access can only handle, at the very most
(and I am open to correction
if anybody has other opinions) 20 users.

There is no such limitation in Access/Jet -- the physical limitation is 255
concurrent users, but not many people will contend that you can actually
support 255 users. However, there are many reports of significantly more
than 20 concurrent users. How many users can be supported with good response
and performance depends on a number of factors, and the factors are
different if you are using a Jet database with .asp code, as this poster
described.

Depending on the way the website is coded, it is possible that no matter how
many people are using the website, there will only be a single user of the
Jet database (see other threads, re: Access vs. Jet -- you are using a Jet
database). If you expect heavy traffic, and many people to be using your
site concurrently, I'd suggest you use a server DB, instead of Access, but
if it will only have a modest number (in the tens, say) of concurrent users,
Access may serve your purposes.

If I were doing in Access what you describe, there'd be multiple Queries,
with one or more being data source for the final Query. As you will be, no
doubt, want one Query with subqueries, I'll leave that exercise to someone
else who enjoys writing SQL from scratch.

Or as an alternative, get a copy of Access, build your basic Queries in the
Query Builder, and copy/merge the SQL.

Larry Linson
Microsoft Access MVP

[Lyle Fairfield]

Nick 'The Database Guy' wrote:

Hi Worldbadger,

Think is that I would have my doubts about using an access application
with a front end application that can have potentially unlimited users.

Access can only handle, at the very most (and I am open to correction
if anybody has other opinions) 20 users.

ASPs can't maintain a connection [and there are no pills for that].
They connect for a few millionths of a second. They get some data. They
write some html. They send the html to client's browser. They close.
Any connection they may have had is gone forever, even if the ASP
writer didn't explicitly close it.
The cleint may modify delete or add something in the html form. Then
it's submitted to an asp file. The ASP file connects for a few
millionths of a second to effect the changes. It may write and send
some more html. Then it closes. The connection is history.
There are no Concurrent Users with ASP, or it's just a freak if there
are.
A lotta people can use an ASP page. Probably the Web Server is going to
choke before the Access DB does.