Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Lowest Bidder Query


(!)

Dkomer's Avatar
Dkomer Dkomer is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Beginner
20-Jun-2012, 08:24 AM #1
Lowest Bidder Query
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
20-Jun-2012, 08:55 AM #2
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's Avatar
Dkomer Dkomer is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Beginner
20-Jun-2012, 09:05 AM #3
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
20-Jun-2012, 11:57 AM #4
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.
__________________
OBP
I do not give up easily
Dkomer's Avatar
Dkomer Dkomer is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Beginner
20-Jun-2012, 12:30 PM #5
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
20-Jun-2012, 12:42 PM #6
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's Avatar
Dkomer Dkomer is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Beginner
20-Jun-2012, 12:48 PM #7
GOT IT!!!! Now is there a way for me to get the specific bidder included into that query???
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
20-Jun-2012, 12:55 PM #8
You should probably create another query based on that one and then add the Bidder, that is to avoid Grouping by bidders.
Dkomer's Avatar
Dkomer Dkomer is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Beginner
20-Jun-2012, 01:01 PM #9
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
20-Jun-2012, 01:30 PM #10
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's Avatar
Dkomer Dkomer is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Beginner
20-Jun-2012, 01:37 PM #11
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
21-Jun-2012, 05:17 AM #12
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's Avatar
Dkomer Dkomer is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Beginner
21-Jun-2012, 07:23 AM #13
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
21-Jun-2012, 07:40 AM #14
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's Avatar
Dkomer Dkomer is offline
Member with 12 posts.
THREAD STARTER
 
Join Date: Jun 2012
Experience: Beginner
21-Jun-2012, 08:33 AM #15
It wont let me put the picture in....It turns to a bunch of mumbo jumbo when i put it in...Any Suggestions???
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
access 07

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑