• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Query required

Hi Experts,

I have an Access database containing the table tblAuctionBids, the fields are as follows:

Description (description of the item being bid on)
BidTime (date/time a bid was received)
CloseTime (date/time the auction closes)
BidAmount (amount being bid)

I need a query that will return rows of opening bids on all currently active auctions. By opening bid I mean the first/earliest bid taken on an item and by currently active auctions I mean auctions that have not closed yet. Thanks.
0
DColin
Asked:
DColin
  • 8
  • 4
  • 3
  • +1
1 Solution
 
pdebaetsCommented:
Try this:

SELECT Q.Description, Q.BidAmount
FROM tblAuctionBids As Q INNER JOIN
        (SELECT Description, Min(Bidtime) As MinBidTime
         FROM tblAuctionBids
         GROUP BY Description)  As T
        ON Q.Description=T.Description AND Q.BidTime = T.MinBidTime AND Q.CloseTime < Now();

Open in new window

0
 
DColinAuthor Commented:
pdebaets:

I get the error message 'Join expression not supported'. Is this an Access problem?
0
 
Bill RossCommented:
Hi,

Post a sample of your database and I'll show you how.  It does not appear that you have a primary key in your table.

Regards,

Bill
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
MurpheyApplication ConsultantCommented:
SELECT Description, BidAmount, Min(BidTime)
FROM tblAuctionBids
Where CloseTime = 0
Order by BidTime
0
 
DColinAuthor Commented:
murphey2,

I am getting the error message:

You tried to execute a query that does not include the specified expression 'Description' as part of an aggregate function.

When I run your query. It seems to be Min(BidTime) that is causing the problem because when I remove the BidTime references it will run without error.
0
 
DColinAuthor Commented:
BillDenver,

Here is my test db. I do not have a primary key.
Auction.mdb
0
 
Bill RossCommented:
Hi,

See attached.

The OpenBidsQ gets the time of the last bid for Open Bids.  This query is used in MaxOpenBidQ to get the actual bid that matches the time.

Regards,

Bill
Auction1.mdb
0
 
Bill RossCommented:
Hi,

BTW - it's good practice to put a primary key in ALL access tables.  You can use a counter (Autonumber) field.  

Best regards,

Bill
0
 
DColinAuthor Commented:
BillDenver,

Thanks for your reply. I need the first bid for all open auctions not the last bid.

I am using Visual Basic to query the database and use the sql query to populate a DataTable object. Does your solution provide me with an sql query?
0
 
pdebaetsCommented:
Thanks for the file. This should work:

SELECT Q.Description, Q.BidAmount
FROM tblAuctionBids As Q INNER JOIN
        (SELECT Description, Min(Bidtime) As MinBidTime
         FROM tblAuctionBids
         GROUP BY Description)  As T
        ON Q.Description=T.Description AND Q.BidTime = T.MinBidTime WHERE Q.CloseTime < Now();

Open in new window

0
 
DColinAuthor Commented:
Apologies there is an entry error in the in the test database, attached is the correct db.
Auction.mdb
0
 
DColinAuthor Commented:
pdebaets

Apologies there is an error in the test db. Please see my previous post for the corrected db.
 
My test db contains 3 auctions, two current and one expired. When I run your query I get one current and one expired auction returned rather that two current.
0
 
pdebaetsCommented:
I think I had the Where clause set up wrong... Please try this:

SELECT Q.Description, Q.BidAmount
FROM tblAuctionBids As Q INNER JOIN
        (SELECT Description, Min(Bidtime) As MinBidTime
         FROM tblAuctionBids
         GROUP BY Description)  As T
        ON Q.Description=T.Description AND Q.BidTime = T.MinBidTime WHERE Q.BidTime < now() AND Q.CloseTime > Now();
0
 
DColinAuthor Commented:
pdebaets,

It now returns only one of the two current auctions.
0
 
pdebaetsCommented:
There is only one auction open. The one with the description "B221".
0
 
DColinAuthor Commented:
You're correct sorry about that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now