Help pulling out lowest bid then summing them by a criteria

[seekinganswers]

I need to find the lowest bid of a line item. Then I need to sum each vendors lowest bid for a report.

My Tables:
TblBid TblLineItems TblVendor TblBidTrack
BidID LineItemID VendorID VendorID
BidNo BidID VendName BidID
BidDate Desc LineItemID
TypeMaterial Quantity AmtBid
Requestor
Dept

I need to sum all of a vendors bid per bidno, then I need to sum all of a vendors LOWEST bids per bidno (that's the amount awarded). Example report follows:

BidDate

BidNo TypeMaterial/Requestor/Dept.

Amt. Bid Amt. Awarded

ABC Vending $1,096.00 $1.000

It's even more complicated then this though. If a Vendor doesn't bid, I need the text "No Bid" to appear. And, if a vendor doesn't bid on every line item, I need to show a 'P' next to their summed amount bid. I think I worked out the if statements for those if someone can help me figure out how to calculate Amt. Awarded.

Can anyone point me in the right direction?

 

[randysmid]

Hi,
I believe the first step is to create a query that retrieves all the data you need from the various tables. Make sure that the "join properties" are correct. You can do this by right-clicking the lines that connect the various tables. You will see 3 basic options: a) Only display records where the key in both tables match (this is probably NOT what you want, since you wish to show all vendors with your "No Bid" text), b) Show all records from the 1st table, and only those from the 2nd table that match, and c) Show all records from the 2nd table, and only those from the 1st table that match.

Once you have a decent query that retrieves all the necessary data, you can then create a report based on this information. In your explanation above, it sounds like you need one report to display all the detail information, and perhaps another report to show just the summary. Am I correct in this assumption? To make the report appear the way you want, you will use "sorting and grouping" (found under the View menu item).

Once you have your report with all detail running correctly, you can then save a version of it to only show the summary. You can turn off the display of the detail section of the report by setting its' Visible property to False.

Finding the lowest bid will be a bit trickier. The Query Object will let you find the "Top" values, but doesn't appear to provide the "bottom values". If I understand your database design, BidId represents a separate project that is being bid upon by a number of vendors. If this is true, then your report will have use BidId for grouping purposes. The Group Footer for BidId is where you can display the lowest bid. The way to check for the lowest bid in code is by adding 2 or more "dim" statements to the General Declarations section of the report. Here is what I might be placing there:
Dim txtVendorId as String
Dim curLowestBid as Currency
'these variables will be alive as long as the
' report is open

Then, in the "On Format" event for each vendor's footer, I would place this code:
If curLowestBid > txtVendorBid Then
txtVendorId = VendorId
curLowestBid = txtVendorBid
End If

The Group Footer for the bid will now have the VendorId and and the lowest bid amount for that particular BidId. You can simply add the necessary text boxes to the Group Footer, and assign the values,e.g.:
txtBidVendor = txtVendorId
txtBidLowest = curLowestBid
'reset the variables
txtVendorId = ""
curLowestBid = 0

Will this work for you? HTH,
Randy Smith
California Teachers Association

 

[seekinganswers]

Hi,

Thanks for getting back to me Randy. I don't think I explained the problem well enough. The report must be in the format of the current board agenda and look as follows:

12/12/2002
02-50 Athletic Equipment/Smith, J./Athletics

Amt. Bid Amt. Awarded
ABC Vending $1,096.00 P $1,000.00
APlus Vending $1,299.00 $ 500.00
Safeway Vending No Bid

Another example where a vendor bid low on every item:
BidDate
BidNo TypeMaterial/Requestor/Dept.
Amt. Bid Amt. Awarded
ABC Vending $800.00 $800.00*
Surething Vending $1,000.00 0.00

The asterisk indicates low bid on every item.

The Amt. Bid is the sum of all the line items bid by a vendor on a particlar bid number. And the Amt. Awarded is the sum of all the low bids by a vendor on a particular bid number.

 

[randysmid]

Hi,
I must be missing something. The steps I indicated should work correctly.
First, you have two lines for the bid Group Header. This includes the bid date, bid #, and "Type/Material/etc....." This group header will also have the column headings for AmtBid and AmtAwarded.

You will not have any detail lines printed!!! The detail lines will contain the individual lines for each vendor. What you are showing in your example is best described as a footer for the vendor, and that will contain the total amount of their bids for that particular bid #.

Now, the difficulty with your design is that you wish to show the winning bid before all the other vendor bids have been calculated. This can be done with VBA code, similar to what I described while checking for the lowest bid amount

Here is what your code might look like to pre-calculate the bid for each vendor.

This code should be placed in the OnFormat event for the bid header.
'open the database
Dim DbBID As Database
Dim rstBID As Recordset
Set DbBID = CurrentDb()
'following code assumes....
' fields in table needed to determine lowest bid is.
' BIDNO, VendorID, and BidAmt (and these are in
' the VendorBids table - of course, you need to
' replace names with those in your database/table
' Another assumption: the field in the group header
' containing Bid # is called txtBIDNO
Set rstBID = DbBID.OpenRecordset("Select BIDNO, , VendorID, BidAmt from VendorBids where BIDNO = ' & txtBIDNO & '", dbReadOnly)

'this code will loop through all the vendor bids for
' this BIDNO
Do Until rstBID.EOF
' Insert code to keep track of vendor and the total
' amount of his bid
' Insert code to keep track of the lowest bid
rst.MoveNext
Loop

' close the temporary copy of the database
Set DbBID = nothing

HTH,
Randy Smith
California Teachers Association

 

[seekinganswers]

Thanks Randy. I guess I misunderstood. I'll give it a try.