[Dkomer]

I am designing a database to hold and store all of our estimate/bid information. I have designed the tables, forms and most of the report. The one thing I need help with is trying to sum up the lowest bids that we put out. For example:

Project A
Bid 1-$75
Bid 2-$60
Bid 3-$85
Project B
Bid 1-$40
Bid 2-$39

I need help on how to add up ONLY the lowest bids of all of our open bids. Please ask questions!!! Any Help will be GREATLY appreciated!!!

[OBP]

Dkomer, welcome to the Forum.

Create a query with Bid and price fields.
Add Grouping but in the price Choose the Min function.
To total it either use a totalling query or do so in a report or form.

[Dkomer]

Im new to this whole query/SQL stuff......I designed my database on the premise of staying away from the programming side of Access.

I have my first query, and it looks like this:

SELECT DISTINCT [Open/Contract Bid Query 1].Project_Name, [Open/Contract Bid Query 1].Bid_To, [Open/Contract Bid Query 1].Bid_Price
FROM [Open/Contract Bid Query 1]
WHERE ((([Open/Contract Bid Query 1].Status)="Open"));

Can you direct me step by step from here??? I am extremely horrible at access.....and thanks, im hoping this forum will help me solve my many access problems.

[OBP]

Open the Query in Design View and on the right hand side of the main menu you will see a large Summation symbol with the word Totals underneath.
Click on the summation symbol and Access will add a "Grouping & Totals" Row to the query and set all the Fields to the Default setting of "Group By".
You need to click on the Price column Group by and change it using the drop down list to "Min" which will only show the Minimum value record for that field.

[Dkomer]

Ok, I know how to get the very minimum price.....thats easy!! Now is there a way for me to get the minimum bid price for EACH project with multiple bids.

EXAMPLE:
I have this....
Project A
Bid 1-$75
Bid 2-$60
Project B
Bid 1-$43
Bid 2-$47
Project C
Bid 1-$45
Bid 2-$53

I would like to have this.....
Project A
Bid 2-$60
Project B
Bid 1-$43
Project C
Bid 1-$45

That is the end result that I am hoping for.....I hope this makes sense too???

[OBP]

Yes you make the Project name one of the Fields that is Grouped with the price set to Min.
You should only get one Mine Record per Project

[Dkomer]

GOT IT!!!! Now is there a way for me to get the specific bidder included into that query???

[OBP]

You should probably create another query based on that one and then add the Bidder, that is to avoid Grouping by bidders.

[Dkomer]

I guess i dont really need the Bidder, because im really only wanting the total sum of the lowest bids. Is there a way that I can make a sum field in a report based off of those low bid numbers OR is that gonna be another query as well??

[OBP]

Use the Report Wizard to do it for you, go through each step carefully to find that part of it, I think it is Summary.

[Dkomer]

I already made the report....I just need to figure out how to put in a text box that is bound to the specific piece of information....Any ideas???

[OBP]

You need an Unbound Field in the either a Group or the Report Footer (It doesn't usually work anywhere else).
In the field's Control Source you type in
= Sum([field name to be summed])
where field name to be summed is the actual name of the field.
You can also have a Running Sum field and refer to that if you want.

[Dkomer]

I tried typing it into the control source of the box, but when i view the report, it asks me for a parameter value for that sum value that i want. Do i have to set it up so that it runs the queries every time i open the report, or is there something that I am doing wrong???

[OBP]

I would suggest using the Wizard to create the report with total to ensure you get the correct syntax.
Can you show a screen print of the Report in Design view and showing the total field?

[Dkomer]

It wont let me put the picture in....It turns to a bunch of mumbo jumbo when i put it in...Any Suggestions???