1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Lowest Bidder Query

Discussion in 'Business Applications' started by Dkomer, Jun 20, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Dkomer

    Dkomer Thread Starter

    Joined:
    Jun 20, 2012
    Messages:
    12
    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!!!
     
  2. Sponsor

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,027
    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.
     
  4. Dkomer

    Dkomer Thread Starter

    Joined:
    Jun 20, 2012
    Messages:
    12
    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.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,027
    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.
     
  6. Dkomer

    Dkomer Thread Starter

    Joined:
    Jun 20, 2012
    Messages:
    12
    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???
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,027
    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
     
  8. Dkomer

    Dkomer Thread Starter

    Joined:
    Jun 20, 2012
    Messages:
    12
    GOT IT!!!! Now is there a way for me to get the specific bidder included into that query???
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,027
    You should probably create another query based on that one and then add the Bidder, that is to avoid Grouping by bidders.
     
  10. Dkomer

    Dkomer Thread Starter

    Joined:
    Jun 20, 2012
    Messages:
    12
    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??
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,027
    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.
     
  12. Dkomer

    Dkomer Thread Starter

    Joined:
    Jun 20, 2012
    Messages:
    12
    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???
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,027
    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.
     
  14. Dkomer

    Dkomer Thread Starter

    Joined:
    Jun 20, 2012
    Messages:
    12
    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???
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,027
    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?
     
  16. Dkomer

    Dkomer Thread Starter

    Joined:
    Jun 20, 2012
    Messages:
    12
    It wont let me put the picture in....It turns to a bunch of mumbo jumbo when i put it in...Any Suggestions???
     
  17. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1057819