Tough Query

[Brennan]

Hello:

I have a database that tracks bids for an electrical
contractor. There is a table called Bids that contains
the following fields:

EstimatorID
Bid ID
Bid Date
Bid Amount
ContractorID

An estimator can can submit bids for the same jobs to
multiple general contractors but will never submit a
multiple bid for the same job to the same contractor.

I have been asked to create a report that sums the bid
amounts for the current year and 2 prior years. If an
estimator has submitted multiple bids for the same job to
different contractors, I have been asked to use only the
lowest of the bids when summing the bid amounts.

I have approached the problem by creating 3 queries for
each of the years. I used the following parameters for
each of the years:

Current Year: Between DateSerial(Year(Date()),1,1) And
DateSerial(Year(Date()),12,31)

Prior Year 1: Between DateSerial(Year(Date())-1,1,1) And
DateSerial(Year(Date())-1,12,31)

Prior Year 2:Between DateSerial(Year(Date())-2,1,1) And
DateSerial(Year(Date())-2,12,31)

Each of the queries returns the proper information
however, when I try to combine them by creating a report ,
I get the following error:

You have chosen fields from record sources which the
wizard can't connect. You may have chosen fields from a
table and from a query based on that table. If so, try
choosing fields from only the table or only the query.

If I create a query combining the 3 queries, I can add the
sum of the Bid amounts from each query, however the sums
of any total field I add after the first is incorrect.
The sum is too high.

And I still can't figure out how to take only the lowest
bid.

Any comments or suggestions on how to approach this
problem will be appreciated.

Brennan

 

[Dale Fye]

How do you know what Job, the bid is for? If you also have a JobID
field in your table, you can use something like:

SELECT T.JobID
, Format(T.BidDate, "yyyy") as BidYear
, SUM(T.BidAmount) as BidSum
FROM yourTable T
INNER JOIN (SELECT JobID
, EstimatorID
, Min(BidAmount) as MinBid
FROM yourTable
GROUP BY JobID
, EstimatorID) as M
ON T.JobID = M.JobID
AND T.EstimatorID = M.EstimatorID
AND T.BidAmount = M.MinBid
GROUP BY T.JobID, Format(T.BidDate, "yyyy")

This should give you a recordset that contains the JobID, Year, and
the sum of the lowest amounts from each estimator for each job. If
you really need this with the years as separate columns, you can use
this query as the source for a crosstab query.

--
HTH

Dale Fye


Hello:

I have a database that tracks bids for an electrical
contractor. There is a table called Bids that contains
the following fields:

EstimatorID
Bid ID
Bid Date
Bid Amount
ContractorID

An estimator can can submit bids for the same jobs to
multiple general contractors but will never submit a
multiple bid for the same job to the same contractor.

I have been asked to create a report that sums the bid
amounts for the current year and 2 prior years. If an
estimator has submitted multiple bids for the same job to
different contractors, I have been asked to use only the
lowest of the bids when summing the bid amounts.

I have approached the problem by creating 3 queries for
each of the years. I used the following parameters for
each of the years:

Current Year: Between DateSerial(Year(Date()),1,1) And
DateSerial(Year(Date()),12,31)

Prior Year 1: Between DateSerial(Year(Date())-1,1,1) And
DateSerial(Year(Date())-1,12,31)

Prior Year 2:Between DateSerial(Year(Date())-2,1,1) And
DateSerial(Year(Date())-2,12,31)

Each of the queries returns the proper information
however, when I try to combine them by creating a report ,
I get the following error:

You have chosen fields from record sources which the
wizard can't connect. You may have chosen fields from a
table and from a query based on that table. If so, try
choosing fields from only the table or only the query.

If I create a query combining the 3 queries, I can add the
sum of the Bid amounts from each query, however the sums
of any total field I add after the first is incorrect.
The sum is too high.

And I still can't figure out how to take only the lowest
bid.

Any comments or suggestions on how to approach this
problem will be appreciated.

Brennan