PROXY BID QUERIES

[gsc123]

I need to build a query for a proxy bid situation, I have a bidhistory table and I need to find the highest bid below the much higher proxy bid... i have idproduct, bidamount, idcustomer and date to play with, any ideas please? I also have the product table that as the price incremented also

 

[Remou]

Do you mean something on the lines of:

SELECT * From BidHistory 
WHERE BidAmount In (
    SELECT Max(BidAmount) 
    FROM BidHistory
    WHERE BidAmount<9999)

9999 = proxy bid

http://lessthandot.com

 

[gsc123]

This is on the line of it but it has to be the next bid down but higher than and persons bid make sense?>

ie:
my bid - 1950

present bid - 1100
someones else bid is the 1100 so if they bid again its then

1110
... hope this is clear

 

[Remou]

Nope.

Perhaps you can illustrate with a little more sample data and some pseudo sql?

http://lessthandot.com

 

[gsc123]

Ok --

eg1
select bidAmount + 10 From BidHistory where bid is less than my bid(1950)

eg2
if bidder bids 1100 hes not higher bidder but I am still because of higher bid so the bids increments one another 10


eg3
So my proxy bid of 1950 is winning until someone else bids more - the query should select from bidhistory a bid less than the proxy but as high as my bid is ...

 

[Remou]

This is not the kind of thing you can do with Jet SQL. You need VBA or such like. However, it is still not clear. Why keep adding 10, when you know how much the person needs to bid to catch up with you?

You have not said what you want to happen, or what data you have.

'Reference: Microsoft DAO x.x Object Library
'Reference: Microsoft DAO x.x Object Library
Dim rs As DAO.Recordset
Dim curBid As Currency
Dim curNewBid As Currency

curBid = 9999
strSQL = "SELECT * From BidHistory " _
& "WHERE BidAmount = ( " _
& "   SELECT Max(BidAmount) " _
& "   FROM BidHistory " _
& "   WHERE BidAmount<=" & lngBid & ")"

Set rs = CurrentDb.OpenRecordset(strSQL)

rs.MoveLast

If rs!BidAmount = lngBid Then
   MsgBox rs.RecordCount & " records found with bids that match " & lngBid
Else
   curNewBid = rs!BidAmount + 10
   'Now what happens?
End If

http://lessthandot.com

 

[gsc123]

hi thxs - what is lngBid ?

 

[Remou]

A left over.

strSQL = "SELECT * From BidHistory " _
& "WHERE BidAmount = ( " _
& "   SELECT Max(BidAmount) " _
& "   FROM BidHistory " _
& "   WHERE BidAmount<=" & curBid & ")"

Set rs = CurrentDb.OpenRecordset(strSQL)

rs.MoveLast

If rs!BidAmount = curBid Then
   MsgBox rs.RecordCount & " records found with bids that match " & curBid
Else
   curNewBid = rs!BidAmount + 10
   'Now what happens?
End If

http://lessthandot.com

 

[gsc123]

does this cater for all bids -

'Now what happens? - this stage ur outbid

 

[Remou]

I am lost as to what you wish to do. You give three examples:

eg1
select bidAmount + 10 From BidHistory where bid is less than my bid(1950)

eg2
if bidder bids 1100 hes not higher bidder but I am still because of higher bid so the bids increments one another 10[/i]

Why do you add ten? How often? Automatically or manually? These two led to my comment above:

"However, it is still not clear. Why keep adding 10, when you know how much the person needs to bid to catch up with you? "

The whole thing seems to be lacking some manual level.

eg3
So my proxy bid of 1950 is winning until someone else bids more - the query should select from bidhistory a bid less than the proxy but as high as my bid is ...

Is your bid the proxy bid or not?

This example selects the highest bids less than or equal to a proxy bid of 9999

SELECT * From BidHistory
WHERE BidAmount = (
    SELECT Max(BidAmount)
    FROM BidHistory
    WHERE BidAmount<=9999)

You then talk about adding 10

http://lessthandot.com

 

[gsc123]

Why do you add ten? How often? Automatically or manually? These two led to my comment above:

"However, it is still not clear. Why keep adding 10, when you know how much the person needs to bid to catch up with you? "

...well keeps incrementing until the high bid id reached - bit like the crap world of ebay

 

[Remou]

...well keeps incrementing until the high bid id reached - bit like the crap world of ebay "

We are still not getting anywhere. What happens in between each increment? If nothing happens, why not just add the whole lot at once? If something happens, you are back to manual.

http://lessthandot.com

 

[gsc123]

If someone outbids you by proxy, it increments