473,320 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Min of two different fields

Missionary
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
8 17901
Stewart Ross
2,545 Expert Mod 2GB
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
yaaara
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
Stewart Ross
2,545 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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
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
Stewart Ross
2,545 Expert Mod 2GB
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
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
Stewart Ross
2,545 Expert Mod 2GB
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

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

Similar topics

2
by: Max | last post by:
Hi, Is it possible to have different Select Expert formula for different column or Fields at once? I can't figure out how to calculate totals for different fields such as count and total of...
2
by: Branden | last post by:
hi guys, i was wondering if it is possible to extract selected words in a field to be put in different fields automatically. Do i have to write the code in vb? This is what im trying to do....
2
by: Yahya | last post by:
Dear Sirs, I would like to run the below code using other credentials than the user running it, how can I use different credentials? Thank you, Yahya Dim msgobj
16
by: Joe Fallon | last post by:
I have a C# class that works correctly. I translated it to VB and now it runs differently. The C# class evaluates the Public properties and then executes MyBase.New. So default values are set...
0
by: WebMatrix | last post by:
Hello, I have a data list that displays data (over 20 fields) comprised from different tables. Right now when user clicks edit button, all fields turn into textboxes, so user can change values....
1
by: herman404 | last post by:
Hi everyone, I have some trouble writing a SQL stored procedure that can do the following: We have data in one table in numeric form, but we want to sum the data in this table based on the...
59
by: tdb | last post by:
I am trying to calculate the session time for logon and logoff datetime fields, but the fields are in different records and different columns. Is this possible with a subquery? Here's a sample of the...
3
by: iht | last post by:
Say I have a database with types of car driven by people living in different cities. I made several queries to separate out the database according to city, then made queries to count out numbers...
6
by: Mangler | last post by:
Say I have to different recordsets that have the fields: rsA.Fields.Item("A").Value ,rsB.Fields.Item("B").Value How would I add those to fields? It may be my inexperience but when I tried...
14
by: Phil Stanton | last post by:
I have 1 FE database which can be linked to a number of BE databases. The BEs are all different Clubs e.g. Sailing Club, Yacht Club, Bridge Club, Family etc. Problem is if I change anything in...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.