Update Query based on highest bid???? (1 Viewer)

[ldare2000]

Hi,
I'm really struggling with an A Level assignment. Would anyone be able to help?

One table contains the following fields
Bid Form Number (Key)
Car Registration Number
Trader Account Number
Trader Bid
Bid Date
Bid Status

Currently the first 5 fields are completed. There will only ever be on winning bid per Car Registration Number. Bid Status is currently blank.

Is it possible to run an update query that will place a word like highest or winner in the bid status field for the highest bid per registration number?

Thanks
Louis

 

[nerak99]

In the select query window you can choose to return a particular number of queries which you can set to one. You can also order your returns by the bid value and so cause that one to be the one returned.

However; There are two (soluble) problems you will encounter.

1) The choose only one record returned option will disappear when you make the query into an update query.
Never mind, do a select query that returns one record and then use that as a source for your update query.

2) If in your original query you had to sort the query results, you may find that the sorting defeats the return one record selection. (Even though "Top 1" will still appear in the sql view of your query (!).).
In this case, you will need three queries, each one feeding the next.
One to select and sort your bids but return all,
A second that is fed by the first that selects only the top record and finally,
Your update query. (Since that now has only one record to update it is simple to do. (Right?)).

Least ways, that is how I have solved this problem. I am using Access 2003 and would be interested in other non-sql, non VBA solutions to the above.

 

[pbaldy]

It could be argued that the status field is unnecessary, and in fact breaks normalization rules. It is a simple matter to return the highest bid for each car with a query, which would be more dynamic anyway (no need to run updates).

 

[ldare2000]

It could be argued that the status field is unnecessary, and in fact breaks normalization rules. It is a simple matter to return the highest bid for each car with a query, which would be more dynamic anyway (no need to run updates).

Hi,


Yes I think you're right. How could I create a Select Query to only return the highest bid?

Thanks
Louis

 

[pbaldy]

The first query here, though you'll probably end up using both:

http://www.baldyweb.com/LastValue.htm

 

[nerak99]

It could be argued that the status field is unnecessary, and in fact breaks normalization rules. It is a simple matter to return the highest bid for each car with a query, which would be more dynamic anyway (no need to run updates).

Such fields are sometimes needed because they are historic

 

[pbaldy]

There are certainly exceptions to every rule, but this did not appear to be one of them.

 

[nerak99]

If someone has 'won' a bid, then it is historic. Like an order that was delivered against an old price. IMHO this does look like one of them.

 

[pbaldy]

Price is a different matter. You store price in a transaction table because the price in a product table will change over time, and the price for that transaction is static. In this instance, the highest bid will always represent the winning bid (given the parameters of the question). Your described process is going to update this field based on highest bid, is it not? Why bother trying to keep this field updated when the query will always return the same records you were going to flag anyway?

 

[nerak99]

Well It looks like I am reading the question differently from you.

 

[gemma-the-husky]

after an auction is closed, i can think of two reasons to store the successful bid details

a) it saves you having to keep doing a dmax query
b) at some point you may want to delete the old bid history

But I think I would just be inclined to store the recordid of the successful bid, to avoid the requery involved in a) - it would give you an easy mechanism to delete all old unsuccessful bids, if you ever needed to. And it would have all the information about the bid itself - in addition to the amount.