Using Min as Formula in Query to Find Min values from range of fields (1 Viewer)

RC01

New member
Local time
Today, 18:38
Joined
Dec 9, 2019
Messages
7
Hi, i need some help here. I'm creating a query that could help me to check the lowest values from range of fields. I needed this query to compare cost of shipping to selected countries and weight from different shipping account no that i have. The query should check the lowest rates and return the shipping account name.

here is the screenshot of my current query result.


i'm using nested iif function but i ran to problem:banghead:, if one of the test actually False then the entire IIF is not returning the correct result.

***************************************
Carrier: IIf([DHLTCM_Rates]![Rates]>[Fedex_300541852_Priority]![Rates],"FDXMY_300541852_Priority",(IIf([DHLTCM_Rates]![Rates]>[Fedex_478457529_Priority]![Rates],"FDXMY_478457529_Priority",(IIf([DHLTCM_Rates]![Rates]>[UPSMY_9428EA_Rates]![Rates],"UPSMY_9428EA","DHLTCM")))))

Above is my expression to calculate. If you refer to my screenshot and based on above expression, the query return result as FDXMY_300541852_Priority as the lowest shipping account but in reality the lowest shipping account is UPSMY_9428EA. This happen because i place the comparison between DHLTCM_Rates and FDXMY_300541852 first before the rest.

i think Min would help in excel but i have no idea:confused::confused: how to do it in access. i'm still a beginner trying to solve my daily work problem more efficiently.I'm tired of checking multiple shipping rates everyday especially there are alot of parcel to ship to different countries.

please help....:(
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:38
Joined
Sep 21, 2011
Messages
14,038
It looks like your structure is all wrong.?
You should have a record for each shipper and destination I believe.

Then Min would work without any issues.?

HTH
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:38
Joined
Oct 29, 2018
Messages
21,357
Hi. I agree. Min() is used for evaluating records. To evaluate a series of values, you'll need a custom function like this one.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:38
Joined
Jan 23, 2006
Messages
15,361
Might be more helpful if you posted a copy of your tables and relationships.

Are there always the same number of fields to be compared?

Here's a function that may be helpful.
Code:
' ----------------------------------------------------------------
' Procedure Name: MyMinimum
' Purpose: Find minimum of 3 numeric values
' Procedure Kind: Function
' Procedure Access: Public
' Parameter a (Single):
' Parameter b (Single):
' Parameter c (Single):
' Return Type: Single
' Author: Jack
' Date: 19-Jul-17
' ----------------------------------------------------------------
Function MyMinimum(ByVal a As Single, _
                         ByVal b As Single, _
                         ByVal c As Single) As Single
    MyMinimum = a
    If b < MyMinimum Then
        MyMinimum = b
    End If

    If c < MyMinimum Then
        MyMinimum = c
    End If

End Function


Sample usage of the MyMinimum function
Code:
Sub testMyMin()
Dim a As Single, b As Single, c As Single
a = 87.92
b = 87.34
c = 23.46
Debug.Print MyMinimum(a, b, c)

End Sub


Result was 23.46
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:38
Joined
Sep 21, 2011
Messages
14,038
If you do insist on keeping that structure, I would choose to write a function that retrieved the record for destination and weight.
Then walk through the fields comparing one field with the next.
If the next is less, swap and update the a variable with the index of the new minimum field.
Then identify the name of the field from it's index and pass that back to the calling routine.

What happens if two carriers have the same price.?
 

RC01

New member
Local time
Today, 18:38
Joined
Dec 9, 2019
Messages
7
It looks like your structure is all wrong.?
You should have a record for each shipper and destination I believe.

Then Min would work without any issues.?

HTH

i have rates tables and zoning for each shipping account but couldn't figure out to to Min query for each line.
 

RC01

New member
Local time
Today, 18:38
Joined
Dec 9, 2019
Messages
7
Hi. I agree. Min() is used for evaluating records. To evaluate a series of values, you'll need a custom function this one.

Thanks for the link but how do i insert the code to my current query? Below is my SQL code from access. I'm building the code using access expression builder.. guess there is where my limitation is. I have no idea how to write a proper sql code.

#SELECT [IATA Code].IATACode, Weight.Weight, UPSMY_9428EA_Rates.Rates, Fedex_478457529_Priority.Rates, Fedex_300541852_Priority.Rates, DHLTCM_Rates.Rates, IIf([DHLTCM_Rates]![Rates]>[Fedex_300541852_Priority]![Rates],"FDXMY_300541852_Priority",(IIf([DHLTCM_Rates]![Rates]>[Fedex_478457529_Priority]![Rates],"FDXMY_478457529_Priority",(IIf([DHLTCM_Rates]![Rates]>[UPSMY_9428EA_Rates]![Rates],"UPSMY_9428EA","DHLTCM"))))) AS Carrier, IIf([SFexpress_Rates]![Rates]>[UPSMY_9428EA_Rates]![Rates],"UPSMY_9428EA",(IIf([SFexpress_Rates]![Rates]>[Fedex_478457529_Priority]![Rates],"FDXYMY_478457529_Priority",(IIf([SFexpress_Rates]![Rates]>[Fedex_300541852_Priority]![Rates],"FDXMY_300541852_Priority",(IIf([SFexpress_Rates]![Rates]>[DHLTCM_Rates]![Rates],"DHLTCM","SFexpressMY"))))))) AS carrier2
FROM (SFexpress_Zoning INNER JOIN ((((Fedex_300541852_Priority INNER JOIN ((((((([IATA Code] INNER JOIN ADS_Zoning ON [IATA Code].IATACode = ADS_Zoning.[IATA Code]) INNER JOIN ADS_Rates ON ADS_Zoning.Zone = ADS_Rates.Zone) INNER JOIN Weight ON ADS_Rates.Weight = Weight.Weight) INNER JOIN UPS_Zoning ON [IATA Code].IATACode = UPS_Zoning.[IATA Code]) INNER JOIN UPSMY_9428EA_Rates ON (Weight.Weight = UPSMY_9428EA_Rates.Weight) AND (UPS_Zoning.Zone = UPSMY_9428EA_Rates.Zone)) INNER JOIN Fedex_Zoning ON [IATA Code].IATACode = Fedex_Zoning.[IATA Code]) INNER JOIN Fedex_478457529_Priority ON (Fedex_478457529_Priority.Weight = Weight.Weight) AND (Fedex_Zoning.Zone = Fedex_478457529_Priority.Zone)) ON Fedex_300541852_Priority.Weight = Weight.Weight) INNER JOIN Fedex_Zoning AS Fedex_Zoning_1 ON (Fedex_Zoning_1.Zone = Fedex_300541852_Priority.Zone) AND ([IATA Code].IATACode = Fedex_Zoning_1.[IATA Code])) INNER JOIN DHLTCM_Zoning ON [IATA Code].IATACode = DHLTCM_Zoning.[IATA Code]) INNER JOIN DHLTCM_Rates ON (DHLTCM_Rates.Weight = Weight.Weight) AND (DHLTCM_Zoning.Zone = DHLTCM_Rates.Zone)) ON SFexpress_Zoning.[IATA Code] = [IATA Code].IATACode) INNER JOIN SFexpress_Rates ON (SFexpress_Rates.Weight = Weight.Weight) AND (SFexpress_Zoning.Zone = SFexpress_Rates.Zone)
WHERE ((([IATA Code].IATACode)="CA") AND ((Weight.Weight)=2));#
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:38
Joined
Aug 30, 2003
Messages
36,118
Post 7 was moderated, I'm posting to trigger email notifications.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:38
Joined
Jan 20, 2009
Messages
12,849
Thanks for the link but how do i insert the code to my current query? Below is my SQL code from access.

You don't. You immediately abandon the path you are on and restructure your data.

Your structure is even worse than was evident in your original post. The rates for all shippers need to be in one table.

Take a look at that SQL code. Do you really want to be trying to maintain stuff like that? You will be facing nightmares like this over and over again while you persist with inappropriate data structure. Imagine trying to add another shipper.

No matter how much work you have already done, it will be far less effort to fix the structure now than continue on your current path. Properly structured data is easy to work with.
 

RC01

New member
Local time
Today, 18:38
Joined
Dec 9, 2019
Messages
7
Might be more helpful if you posted a copy of your tables and relationships.

Are there always the same number of fields to be compared?

Here's a function that may be helpful.
Code:
' ----------------------------------------------------------------
' Procedure Name: MyMinimum
' Purpose: Find minimum of 3 numeric values
' Procedure Kind: Function
' Procedure Access: Public
' Parameter a (Single):
' Parameter b (Single):
' Parameter c (Single):
' Return Type: Single
' Author: Jack
' Date: 19-Jul-17
' ----------------------------------------------------------------
Function MyMinimum(ByVal a As Single, _
                         ByVal b As Single, _
                         ByVal c As Single) As Single
    MyMinimum = a
    If b < MyMinimum Then
        MyMinimum = b
    End If

    If c < MyMinimum Then
        MyMinimum = c
    End If

End Function


Sample usage of the MyMinimum function
Code:
Sub testMyMin()
Dim a As Single, b As Single, c As Single
a = 87.92
b = 87.34
c = 23.46
Debug.Print MyMinimum(a, b, c)

End Sub


Result was 23.46

thanks for help.. here is my screenshot on the query that i created. Can you guide on how to use your code in my case. :):)

https://i.imgur.com/hC3ZbOd.png

one of my shipping rates tables :

https://i.imgur.com/oDIEjO2.png

shipping zoning by shipping account:

https://i.imgur.com/K23Mkh1.png

IATACode Tables:

https://i.imgur.com/vv1j3KG.png

i'm not sure if what i created here is correct, i'm learning access on my own from youtube and i start to realize is way harder than excel. :banghead::banghead:
 
Last edited by a moderator:

RC01

New member
Local time
Today, 18:38
Joined
Dec 9, 2019
Messages
7
You don't. You immediately abandon the path you are on and restructure your data.

Your structure is even worse than was evident in your original post. The rates for all shippers need to be in one table.

Take a look at that SQL code. Do you really want to be trying to maintain stuff like that? You will be facing nightmares like this over and over again while you persist with inappropriate data structure. Imagine trying to add another shipper.

No matter how much work you have already done, it will be far less effort to fix the structure now than continue on your current path. Properly structured data is easy to work with.

Hi Galaxiom, thanks for your advice. could you advice what's the best way for me to organize the structure. i'm just learning access from youtube, i thought it will help to lessen my daily task of checking multiple rates and also trying to impress my bosses.. but now i'm end up with some huge headaches here.:banghead::banghead:

i have problem in organizing all shipper rates into a table, each shipper is having at least 10-13 zones and each zones is charged at 0.5kg break and i will need a table that can accommodate weight from 0.5kgs to 999kgs.

i agree with what you saying and i can forsee myself having issues if i will need to add another new shipper. i will really appreciate if you could guide me to the correct path. please............
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:38
Joined
Jan 20, 2009
Messages
12,849
I will have a look tonight. Others are probably already getting their heads around this. There is quite a lot to take in.
Don't despair. You have demonstrated considerable skill to get this working at all.
We will sort the structure and then you will have a fantastic platform to build on.
You will reach your goal.
 

RC01

New member
Local time
Today, 18:38
Joined
Dec 9, 2019
Messages
7
I will have a look tonight. Others are probably already getting their heads around this. There is quite a lot to take in.
Don't despair. You have demonstrated considerable skill to get this working at all.
We will sort the structure and then you will have a fantastic platform to build on.
You will reach your goal.

thank you for this.. you wouldn't know how happy i am on your help. i have sleepless night for months trying to learn by myself..

my greatest thank you to you.......
 

isladogs

MVP / VIP
Local time
Today, 10:38
Joined
Jan 14, 2017
Messages
18,186
Post#10 with the huge screenshots was moderated. Just discovered and belatedly approved.
However I've replaced the enormous inline images with links to the external files.

RC01
Until you have 10 posts please zip any attachments so your posts aren't moderated.
Also please reduce the size of any future screenshots before posting
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 10:38
Joined
Sep 21, 2011
Messages
14,038
Seriously, how is anyone meant to be able to read that? :(

Please use the # icon that will allow you to post any code within tags and give you the ability to make it readable.

#SELECT [IATA Code].IATACode, Weight.Weight, UPSMY_9428EA_Rates.Rates, Fedex_478457529_Priority.Rates, Fedex_300541852_Priority.Rates, DHLTCM_Rates.Rates, IIf([DHLTCM_Rates]![Rates]>[Fedex_300541852_Priority]![Rates],"FDXMY_300541852_Priority",(IIf([DHLTCM_Rates]![Rates]>[Fedex_478457529_Priority]![Rates],"FDXMY_478457529_Priority",(IIf([DHLTCM_Rates]![Rates]>[UPSMY_9428EA_Rates]![Rates],"UPSMY_9428EA","DHLTCM"))))) AS Carrier, IIf([SFexpress_Rates]![Rates]>[UPSMY_9428EA_Rates]![Rates],"UPSMY_9428EA",(IIf([SFexpress_Rates]![Rates]>[Fedex_478457529_Priority]![Rates],"FDXYMY_478457529_Priority",(IIf([SFexpress_Rates]![Rates]>[Fedex_300541852_Priority]![Rates],"FDXMY_300541852_Priority",(IIf([SFexpress_Rates]![Rates]>[DHLTCM_Rates]![Rates],"DHLTCM","SFexpressMY"))))))) AS carrier2
FROM (SFexpress_Zoning INNER JOIN ((((Fedex_300541852_Priority INNER JOIN ((((((([IATA Code] INNER JOIN ADS_Zoning ON [IATA Code].IATACode = ADS_Zoning.[IATA Code]) INNER JOIN ADS_Rates ON ADS_Zoning.Zone = ADS_Rates.Zone) INNER JOIN Weight ON ADS_Rates.Weight = Weight.Weight) INNER JOIN UPS_Zoning ON [IATA Code].IATACode = UPS_Zoning.[IATA Code]) INNER JOIN UPSMY_9428EA_Rates ON (Weight.Weight = UPSMY_9428EA_Rates.Weight) AND (UPS_Zoning.Zone = UPSMY_9428EA_Rates.Zone)) INNER JOIN Fedex_Zoning ON [IATA Code].IATACode = Fedex_Zoning.[IATA Code]) INNER JOIN Fedex_478457529_Priority ON (Fedex_478457529_Priority.Weight = Weight.Weight) AND (Fedex_Zoning.Zone = Fedex_478457529_Priority.Zone)) ON Fedex_300541852_Priority.Weight = Weight.Weight) INNER JOIN Fedex_Zoning AS Fedex_Zoning_1 ON (Fedex_Zoning_1.Zone = Fedex_300541852_Priority.Zone) AND ([IATA Code].IATACode = Fedex_Zoning_1.[IATA Code])) INNER JOIN DHLTCM_Zoning ON [IATA Code].IATACode = DHLTCM_Zoning.[IATA Code]) INNER JOIN DHLTCM_Rates ON (DHLTCM_Rates.Weight = Weight.Weight) AND (DHLTCM_Zoning.Zone = DHLTCM_Rates.Zone)) ON SFexpress_Zoning.[IATA Code] = [IATA Code].IATACode) INNER JOIN SFexpress_Rates ON (SFexpress_Rates.Weight = Weight.Weight) AND (SFexpress_Zoning.Zone = SFexpress_Rates.Zone)
WHERE ((([IATA Code].IATACode)="CA") AND ((Weight.Weight)=2));#
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:38
Joined
Jan 23, 2006
Messages
15,361
RC01,
As Galaxiom has said -you've done quite well to get what you have.
However,
i'm learning access on my own from youtube and i start to realize is way harder than excel
Excel(spreadsheet) and Access (database) are very different animals based on different underlying concepts and intended for different audiences.

You may start Excel by sitting at a blank sheet and typing, but with database you'll need a little more planning. I don't know what "youtubes"
you have been using to learn. I have a link in my signature related to Database Planning and Design that should be helpful to your efforts.

I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary identified in the link.
You will learn more about design in 30-45 minutes. In addition, I recommend that you start with a clear, simple description of the business
area you are trying/intending to support with this proposed database.

It seems you have jumped into physical Access database with little understanding of relational database concepts, database structures and
experience with Access.

Good luck with your project and learning.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:38
Joined
Jan 20, 2009
Messages
12,849
i'm not sure if what i created here is correct, i'm learning access on my own from youtube and i start to realize is way harder than excel. :banghead::banghead:

I isn't harder than Excel but it requires a very different approach. Often being very experienced at Excel can lead the novice database developer in the wrong direction.

Excel is designed to be read by humans so we put the data together on sheets in a grid and have different sheets as the third dimension. This is effectively what you have done by having different table for different Shippers/Services.

Access tables are meant to be read by a database engine. Everything to do with one kind of data goes in the same table. Instead of having another table for a different Shipper or Service we have a field that designates that dimension. The different kinds of data it relates to are in another table and so on.

All the information about one thing we might want to know ends up spread out across multiple tables. The database engine deals with this information incredibly efficiently as whole sets rather than processing individual groups of information as happens in Excel.

Reports and Forms are used to bring all this back together for humans to read and interact with.

Tables tend to be tall and narrow. You are on the right track there with your rates tables but you need a structure that gets all the same kind of data together regardless of the shipper.

I'm struggling a bit because I can't see all your data so I will try to describe in general terms. You will need to adapt the principles for the details. What I have written looks complex. Don't focus on the specifics so much as the principles. There are definitely variations on how this can be done but it is too hard to explain all the differences here.

Table Shipper would have ShipperID (Primary Key) with fields for anything uniquely to do with the shipper such as the contact information etc.

Table Service would have ServiceID (PK), ShipperID (Foreign Key as the ShipperID), ServiceName and any specific information about the service.

Table Countries would have the PK (you could use the IATACode or have a numeric id), CountryName plus anything specific you want to store about the country. Countries are going to be the same regardless of the shippers so you don't want any information in there about shippers, zones or anything but countries.

Shippers might have different Zones for the countries. Even if they don't now they might later so probably best design that way.

There might be a Zone table with ZoneID, ZoneCode, ZoneName. ZoneCode is what the Shipper uses as a code. Because the ZoneCode is a displayed value, using a ZoneID allows the displayed code to be changed at any time without altering the underlying structure. Assuming the Zones are consistent across all Services for the same Shipper you will have a ShipperID field.

Allocate the CountryIDs into the Zones. This uses what is known as a Junction table that stores a many-to-many relationship. It would have fields for ZoneID and CountryID. Each record stores a unique combination of Zone and Country according to the different Shippers.

From a human (Excel) perspective, a junction table looks like a crazy idea but it is the structure the engine needs to work with.

Now from the other end. Table Weights will have a WeightID, WeightDescription (what you will display on Forms and Reports) and fields to designate the range of weights it covers. It is simplest to have WeightLow and WeightHigh fields. When you compare the weight of your package you test for the package weight being between those two and you get a unique WeightID.

Now you need another junction table that connects WeightID, ServiceID and ZoneID. The ServiceID can already be related back though the ShipperID and ZoneID is already related to a ShipperID. (This can get messy because there are two different way to join the tables. There are other ways to avoid this by using composite keys but it is complicated enough already and should work.)

Now you are able to join these few tables together simple queries where you can select on the package weight and destination country and bring back a list of the Shippers and Services available with the prices.

You will need to build forms for operators to ultimately manage the prices because they certainly are not going to be able to write directly into the junction tables. On these forms you will display the human readable names but store ID.

The junction tables will be manged via subforms. There are some really advanced tricks to make these work well but we can worry about that later. The initial focus must be in structuring the data. You are not far from having it right.

Once you have this structure you should be able to write queries to transfer all the information in your existing tables into the new tables.

A note on the Weight table. Having both Low and High weights means it is possible to miss a record and not get a WeightID for a particular weight. In fact it is technically a breach of normalization to have both Maximum and Minimum in a continuous series. More advanced design stores only the maximum weight threshold and returns the lowest weight range that is heavier than the package. However, it requires aggregate subqueries and results in non-updateable query so can be more trouble than it is worth.

BTW Avoid spaces or special characters in any object names . CamelCase is easy to read and you don't have to put brackets around names.

I really hope what I have written helps and doesn't confuse you further. Unfortunately you have taken on something quite complex as your first project but it certainly can be done.
 

RC01

New member
Local time
Today, 18:38
Joined
Dec 9, 2019
Messages
7
Thank you so much Galaxiom for taking your time to help on this. I'm greatly appreciate it. I will need some time to read thru and try on your advice. I'm currently clearing my work for my xmas holiday next week. Shall give revert back once i'm back from my break. hopefully, i'm able to work it thru during my break when i have more time to really ponder over it.

a million thanks to you Galaxiom :):)
 

Users who are viewing this thread

Top Bottom