Lowest bids summed

[seekinganswers]

I can't seem to get my head around this one. A public school sends out requests for bids. The vendors fill them in and return them. Upon return from the various vendors each bid is compared line item by line item. If a vendor bids low on line items 1 and 6 that's all the vendor is awarded. My problem is the report that must be generated for the Board. Following is an example.

BidNo Vendor AmtBid AmtAwarded

AmtBid must the total that the vendor bid for that particular BidNo. However, the AmtAwarded must be the total of those line items awarded to the vendor. I can view the results I want in a pivot table using MIN, but I can't seem to figure out how to put this in a report. I've manipulated the data with crosstab after crosstab never achieving the desired result.

Can anyone help?

 

[cmmrfrds]

This is a try but it is untested.

select A.Vendor, C.BidNo, A.AmtBid,
(select sum(AmtBid) from tab as B
where A.Vendor = B.Vendor and C.AmtBid = B.AmtBid)
as AmtAwarded
From tab as A Inner Join
(select Min(AmtBid), BidNo from tab
group by BidNo) as C
On C.BidNo = A.BidNo
Order by A.Vendor

 

[seekinganswers]

Thanks for the reply. I'm fooling with this. No success yet. I'll try again tomorrow.

 

[seekinganswers]

I've tried manipulating what you sent me without success. Would you be willing to take a look at the database? I can zip it and email it.

 

[cmmrfrds]

Try this first, put together a brief example of what you expect for output maybe a few lines. Also, show an example of the data in your table maybe 6 to 8 lines that way others will see it and may have a ready solution.