Eliminating Records in a Query

[Brennan]

Hello:

I have a query that returns data in the following format:

JobID JobName Contractor Location Bid
1638 Burger King Marsh Bronx $50,000
1638 Burger King Pilot Bronx $50,000

For the purposes of reporting, whenever there are 2 bids
on the same job for the same amount with different
contractors, I would like to eliminate one of the
records. I don't care which record it is so long as I
only end up with one record. I tried to use distinct but
the query still returned both records. I am completely
stumped on how to return only one value.

I have posted the code for the query below. Any comments
or suggestions would be greatly appreciated.

Thanks
Brennan

SELECT DISTINCT
tblContractors.tblContractors_ContractorID,
tblContractors.tblContractors_Contractor,
tblBids.tblBids_ContractorID, Min(tblBids.tblBids_JobID)
AS tblBids_JobID, tblJobs.tblJobs_JobName,
tblJobs.tblJobs_JobLocation, tblBids.tblBids_BidDate, Min
(tblBids.tblBids_BidAmount) AS tblBids_BidAmount,
tblBids.tblBids_Status, tblContacts.tblContacts_Contact,
tblContractors.tblContractors_Phone,
tblEstimators.tblEstimators_EstimatorID,
tblEstimators.tblEstimators_Name,
tblJobTypes.tblJobTypes_JobTypeID,
tblJobTypes.tblJobTypes_JobType, tblBids.tblBids_Active
FROM (tblJobTypes RIGHT JOIN tblJobs ON
tblJobTypes.tblJobTypes_JobTypeID =
tblJobs.tblJobs_JobType) INNER JOIN (tblEstimators INNER
JOIN (tblContractors INNER JOIN (tblContacts RIGHT JOIN
tblBids ON tblContacts.tblContacts_ContactID =
tblBids.tblbids_ContactID) ON
tblContractors.tblContractors_ContractorID =
tblBids.tblBids_ContractorID) ON
tblEstimators.tblEstimators_EstimatorID =
tblBids.tblBids_EstimatorID) ON tblJobs.tblJobs_JobID =
tblBids.tblBids_JobID
GROUP BY tblContractors.tblContractors_ContractorID,
tblContractors.tblContractors_Contractor,
tblBids.tblBids_ContractorID, tblJobs.tblJobs_JobName,
tblJobs.tblJobs_JobLocation, tblBids.tblBids_BidDate,
tblBids.tblBids_Status, tblContacts.tblContacts_Contact,
tblContractors.tblContractors_Phone,
tblEstimators.tblEstimators_EstimatorID,
tblEstimators.tblEstimators_Name,
tblJobTypes.tblJobTypes_JobTypeID,
tblJobTypes.tblJobTypes_JobType, tblBids.tblBids_Active
HAVING (((tblBids.tblBids_BidDate) Between [Forms]!
[frmReports]![txtStartDate] And [Forms]![frmReports]!
[txtEndDate]) AND
((tblEstimators.tblEstimators_EstimatorID)=5) AND
((tblBids.tblBids_Active)=True));

 

[JohnFol]

There is a "Find Duplicates" Query within Access that will give you a good
start.

 

[John Vinson]

Hello:

I have a query that returns data in the following format:

JobID JobName Contractor Location Bid
1638 Burger King Marsh Bronx $50,000
1638 Burger King Pilot Bronx $50,000

For the purposes of reporting, whenever there are 2 bids
on the same job for the same amount with different
contractors, I would like to eliminate one of the
records. I don't care which record it is so long as I
only end up with one record. I tried to use distinct but
the query still returned both records. I am completely
stumped on how to return only one value.

This is the only case I can think of in which you would want to use
the First() aggregate operator in a Totals query. It will pick the
first record in (the arbitrary and uncontrollable) disk storage order.
Try making this a Totals query grouping by JobID, JobName, Location
and Bid and selecting First as the aggregate function for Contractor.