[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Query required

Posted on 2014-01-20
16
Medium Priority
?
353 Views
Last Modified: 2014-01-20
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
Comment
Question by:DColin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 12

Expert Comment

by:pdebaets
ID: 39793698
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
 

Author Comment

by:DColin
ID: 39793873
pdebaets:

I get the error message 'Join expression not supported'. Is this an Access problem?
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39794043
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 39794065
SELECT Description, BidAmount, Min(BidTime)
FROM tblAuctionBids
Where CloseTime = 0
Order by BidTime
0
 

Author Comment

by:DColin
ID: 39794457
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
 

Author Comment

by:DColin
ID: 39794474
BillDenver,

Here is my test db. I do not have a primary key.
Auction.mdb
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39794512
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
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39794521
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
 

Author Comment

by:DColin
ID: 39794551
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
 
LVL 12

Expert Comment

by:pdebaets
ID: 39794615
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
 

Author Comment

by:DColin
ID: 39794663
Apologies there is an entry error in the in the test database, attached is the correct db.
Auction.mdb
0
 

Author Comment

by:DColin
ID: 39794669
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
 
LVL 12

Accepted Solution

by:
pdebaets earned 2000 total points
ID: 39794850
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
 

Author Comment

by:DColin
ID: 39794877
pdebaets,

It now returns only one of the two current auctions.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39794969
There is only one auction open. The one with the description "B221".
0
 

Author Comment

by:DColin
ID: 39794991
You're correct sorry about that.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question