I am trying to do the following but it does not work:
SELECT TOP count(SELECT plot_details.[Design Code]
FROM plot_details
WHERE plot_details.[Design Code] = "ET2")
CountOfET2.[CountOfPlot Number] bids.[Bid Amount], bids.[Design Code], bids.Username
FROM bids
ORDER BY bids.Username DESC;
The query that provides the SELECT TOP value returns a list of 6 records and so I would like to use the number of records (6) in the SELECT TOP statement.
Both queries are working properly individual but I am not sure of the syntax to combine them to do what I want.
Edit: As per dwo's comment, I have provided more information in the hope that someone can advise me on an alternative way to solve my problem.
I am building a basic bidding database/application with MS Access. Users bid on houses that are part of a new building development. However, users do not bid on a specific house, instead they bid on a 'plot type' (plot types have unique 'design codes') which correlates to the type of house that they would like to buy. The non-functioning query above is my attempt at a query that gets all the currently succesfull bids.
A succesfull bid is determined by the following criteria:
- Meets the reserve price for the plot type
- If there are 6 individual houses of a particular plot type, then the bid must be one of the 6 highest.
In my query above I have ignored the first criteria for the time being in an attempt to simplify the problem.
To summarise: I would like to retrieve a list of the highest bids for a particular plot type and then limit the result to a variable number of records.