Do While... problems

  • Thread starter Thread starter Hans Kristian Aas
  • Start date Start date
H

Hans Kristian Aas

I have made a sub which returns all the winning bids from an auction.
The content in the table is the product, the customers number, winning bid,
the lowest allowed bid and finally if the winning bid is higher than the
lowest bid allowed (yes/no).

In an other worksheet I would like to view a total payment-preview for the
customers.
(Customer, Total amount)

How do I do this? I have used a Do While .. Loop command to return the first
table, but I do not know how to do this in the new table.

Any ideas? The problem the way I see it is that the code will have to loop
until it finds everyone with customer number, say 1001, and then return the
total amount for the customer. In the next loop it will have to find a new
customer and add all the buys for him/her as well, and so on. The bid will
of course have to be higher than the lowest bid allowed (then it says "yes"
in the column) to qualify as a buy.

I appreciate all the help I can get!

Thanks!

Hans Kristian Aas
(e-mail address removed)
 
One option would be to run a query similar to this one:

SELECT Table1.CustomerNumber, Sum(Table1.BidAmount) AS SumOfBidAmount
FROM Table1
GROUP BY Table1.CustomerNumber, Table1.BidExceedsMin
HAVING (((Table1.BidExceedsMin)=True));

This will return 2 fields, customer number and the total of their bids. The
only parameter at the moment is whether or not the bid exceeds the minimum
bid, but others could be added.

Another option would be to run a loop as you mention and use the DSum
function. This will total a bidders bids and you can put parameters on the
DSum function to limit which bids qualify for the summing.
 
Back
Top