Eliminating Ties in a Min Query

[Brennan]

Hello:

I am using the following query to return a recordset of
outstanding bids for a project manager.

SELECT tblEstimators.tblEstimators_EstimatorID,
tblEstimators.tblEstimators_Name, Min
(tblJobs.tblJobs_JobID) AS MinOftblJobs_JobID,
tblJobs.tblJobs_JobName,
tblContractors.tblContractors_Contractor,
tblJobs.tblJobs_JobLocation, Min
(tblBids.tblBids_BidAmount) AS MinOftblBids_BidAmount,
tblBids.tblBids_BidDate
FROM (tblJobs INNER JOIN (tblBids INNER JOIN
tblEstimators ON tblBids.tblBids_EstimatorID =
tblEstimators.tblEstimators_EstimatorID) ON
tblJobs.tblJobs_JobID = tblBids.tblBids_JobID) INNER JOIN
tblContractors ON tblBids.tblBids_ContractorID =
tblContractors.tblContractors_ContractorID
GROUP BY tblEstimators.tblEstimators_EstimatorID,
tblEstimators.tblEstimators_Name,
tblJobs.tblJobs_JobName,
tblContractors.tblContractors_Contractor,
tblJobs.tblJobs_JobLocation, tblBids.tblBids_BidDate
HAVING (((tblEstimators.tblEstimators_EstimatorID)=5) AND
((tblBids.tblBids_BidDate) Between #1/1/2002# And
#12/31/2002#));

If a project manager has multiple bids for the same job
with different contractors, I only want to return the bid
with the lowest value. The query works but in some cases
the bid amounts of the multiple bids are equal and both
records are included in the recordset.

How can I exclude one of the records if the Bid Amount
and Job ID are the same?

Any help would be appreciated

Thanks
Brennan

 

[[MVP] S.Clark]

If you arbitrarily want to exclude one, then you can change the query to
return only distinct values.

But if there is some other factor that determines which should be returned,
then you should enhance the criteria of the where clause.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-