By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,510 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,510 IT Pros & Developers. It's quick & easy.

Min of two different fields

Missionary
P: 30
For simplicity's sake, I'll make a basic example of what I'm trying to do:

Supose you have a table that lists vendor bids in two collumns [VendorA] and [VendorB]. In my query I want a field that shows the minimum value between the two fields in the table.
Is this possible?

Thanks
Aug 28 '08 #1
Share this Question
Share on Google+
8 Replies


Expert Mod 2.5K+
P: 2,545
The simplest approach is to use IIF to test whether one bid is less than the other. Insert a new column in the Access query editor and add a calculated field:
Expand|Select|Wrap|Line Numbers
  1. Min Bid: IIF([VendorA] < [VenderB], [VendorA], [VendorB])
This has the disadvantage that it does not identify equal bids. This can be overcome by adding another calculated field:

Expand|Select|Wrap|Line Numbers
  1. Equal Bids: IIF([VendorA] = [VendorB], "Yes", "No")
IIFs can slow queries down if too many are used or they are used with complex criteria, but for simple selections they can be very useful.

-Stewart
Aug 28 '08 #2

P: 77
I came up with this...

Expand|Select|Wrap|Line Numbers
  1. SELECT Min(VendorA) AS MinBid
  2. FROM (SELECT DISTINCT (Vendor.VendorA) from Vendor union select distinct(Vendor.VendorB)
  3. FROM Vendor)
It works for me... Pls check and let us know :-)

For simplicity's sake, I'll make a basic example of what I'm trying to do:

Supose you have a table that lists vendor bids in two collumns [VendorA] and [VendorB]. In my query I want a field that shows the minimum value between the two fields in the table.
Is this possible?

Thanks
Aug 28 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi. Yaara has pointed out a way to find the minimum for all the records. Looking again at this I may have misinterpreted what you wrote, as what I provided was not the minimum for all records but the lower of the two values within a record. If what you need is a minimum taking into account multiple bids then Yaara's method is a solution to this and mine isn't.

It is almost certain that you would need to include other fields to group by in finding the minimum however, as there are likely to be other criteria you are not mentioning - bid submission dates, or bid item references, for example.

-Stewart
Aug 28 '08 #4

NeoPa
Expert Mod 15k+
P: 31,476
Stewart's first offering is the perfect solution if the question is exactly as stated (My inclination is that you expressed clearly exactly what you wanted in fact).

Possibly it was a simpler solution than you'd anticipated ;)
Aug 28 '08 #5

Missionary
P: 30
Well, I'm not great with SQL, so the IIF statment works great. Now what if you have four different bid 'vendor columns? I suppose you could make a layered IIF statement like this:

IIF (VendorA < (VendorB Or VendorC Or VendorD), VendorA, IIF (VendorB < (VendorC Or Vendor D), VendorB, IIF ...

Is there an easier way? Is there some Least of() operator?

Thanks alot.

PS there is no problem if they are equal, I just need the value.


I came up with this...

Expand|Select|Wrap|Line Numbers
  1. SELECT Min(VendorA) AS MinBid
  2. FROM (SELECT DISTINCT (Vendor.VendorA) from Vendor union select distinct(Vendor.VendorB)
  3. FROM Vendor)
It works for me... Pls check and let us know :-)
Aug 28 '08 #6

Expert Mod 2.5K+
P: 2,545
The four-bid case is quite different to the two-bid case you started with. It is not simply a case of layering IIFs; it is far too complex a statement to write for that. You would need to write a custom function in VBA instead.

I would point out that retaining four bid values in a single row of a table is poor relational table design - it breaches first normal form rules on removing repeating groups into separate tables. If you had each bid recorded in a separate row along with the vendor ID etc you could have used the SQL min function to find the minimum easily.

I would suggest you read the article on Database Normalisation and Table Structures in our HowTo section.

-Stewart
Aug 28 '08 #7

Missionary
P: 30
You're right, I'm cutting a corner to avoid a subform and also because I want a null value for each vendor if there is no bid, and I don't want to have to automatically make a record for each of the four vendors for each job. Anyway,
perhaps I can make a union query for those fields, then find the minimum of that.
Thank you all for the help! This form has saved me so many times.
Aug 28 '08 #8

Expert Mod 2.5K+
P: 2,545
I understand that you might want to take shortcuts - but they work out longer as they are not optimal of time and resources, and they constrain you badly in some respects (the Min calculation is just one example).

As for always including the four vendors even if there is no bid from one or more, SQL has different types of table join for this. A LEFT (or Outer) join between a Vendor table and a Bid table will show all vendors regardless of whether or not a bid has been placed. The fields recording specific bids would automatically be null if there is no actual bid involved, but all vendors would be listed in the query.

I would urge you to implement proper design - there are no disadvantages in designing it right from the start, and SQL is much more powerful and capable when it comes to meeting your needs than you may be giving it credit for.

-Stewart
Aug 28 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.