Solved How to get Min of two fields of a table/query? (1 Viewer)

KitaYama

Well-known member
Local time
Tomorrow, 05:56
Joined
Jan 6, 2022
Messages
1,582
Here's my situation.
I have a query that returns all orders that are not yet delivered.
This query contains two date fields : Shipping & Delivery.
Shipping can be null or empty. We don't fill this field in certain situations.
Delivery is always a yyyy/mm/dd date, never null, never empty.
Delivery can be in past. We have delays. Today is 05/27 but we are still manufacturing orders that should be delivered a month ago.

Here's some sample data:

CustomerProductFKShippingDelivery
Customer1
1​
2025/02/03​
2025/02/13​
Customer1
4​
2023/08/12​
2023/08/13​
Customer2
3​
2023/06/05​
2023/06/08​
Customer3
1​
2023/08/08​
2023/08/13​
Customer1
3​
2023/04/22​
2023/04/25​
Customer5
4​
2023/04/18​
Customer5
3​
2023/07/16​
2023/07/19​
Customer3
4​
2023/08/10​
Customer2
2​
2023/06/03​
2023/06/10​
Customer1
2​
2024/03/03​
Customer1
1​
2023/05/28​
Customer5
2​
2023/07/07​

I'm trying to write a query based on this query that looks at Shipping and Delivery fields for each customer, and finds/shows the record that contains the smallest date.
This is the above data filtered for Customer1

1.png


There are 8 dates and the smallest is 2023/04/22. So the final query should show ONLY the third record (marked in red) for Customer1.
And do the same for other customers. One record per customer that contains the smallest date for that customer (either in shipping or Delivery).
The whole result should be :

CustomerProductFKShippingDelivery
Customer132023/04/222023/04/25
Customer222023/06/032023/06/10
Customer312023/08/082023/08/13
Customer542023/04/18

I hope I'm clear enough in what I'm trying to do here.
A list grouped by Customers that has the smallest date in either shipping or delivery.

Million thanks.
A small database is attached. To simplify the process, I've changed the query to a table.
Thanks again.
 

Attachments

  • Database3.accdb
    536 KB · Views: 71
Last edited:

561414

Active member
Local time
Today, 15:56
Joined
May 28, 2021
Messages
279
Does this work?
SQL:
SELECT t.Customer, t.ProductFK, t.Shipping, t.Delivery
FROM tbl AS t
WHERE EXISTS (
    SELECT 1
    FROM tbl AS sub
    WHERE sub.Customer = t.Customer
    GROUP BY sub.Customer
    HAVING MIN(sub.Shipping) = t.Shipping
)
ORDER BY t.Customer

Edit:Never mind, it does not work
 
Last edited:

KitaYama

Well-known member
Local time
Tomorrow, 05:56
Joined
Jan 6, 2022
Messages
1,582
Unfortunately No.
It's mostly where my attempts were failing. Null shipping were ruining my queries.
This is the result of your query.

2.png


thanks for trying to help though.
 

561414

Active member
Local time
Today, 15:56
Joined
May 28, 2021
Messages
279
Except for customer2 the other customers also have null shipping, what does this depend on again?
 

KitaYama

Well-known member
Local time
Tomorrow, 05:56
Joined
Jan 6, 2022
Messages
1,582
null shipping, what does this depend on again?
Null shipping?
It may be based on a million conditions. The type of contract. The size of the parts. Delivery addresses. We don't cover shipping for some countries or even prefectures within our country.
In some cases, we don't trust the product be delivered safe. So we carry them ourselves.
There's a long list and it's decided when signing the contract or in some cases the order sheet demands for shipping or asks not to be shipped.
In some cases the customer's truck come to pick up the products to cut the shipping fee.

There's no specific rule for that. And it's out of my reach to decide on that eara.


Except for customer2 the other customers also have null shipping
Any or all customers may have null shipping.

The order table has more than a million records and maybe half of it contains null shipping. If I filter and show only those that are not delivered, it maybe around 5K records that 1/3d of them have blank shipping.

Thanks again.
 

ebs17

Well-known member
Local time
Today, 22:56
Joined
Feb 7, 2020
Messages
1,991
Shipping is always smaller than Delivery?
With a unique ID in the records:
SQL:
SELECT
   T.ID,
   T.Customer,
   T.ProductFK,
   T.Shipping,
   T.Delivery
FROM
   tbl AS T
WHERE
   T.ID IN
      (
         SELECT TOP 1
            T0.ID
         FROM
            tbl AS T0
         WHERE
            T0.Customer = T.Customer
         ORDER BY
            Nz(T0.Shipping, T0.Delivery)
      )
 

561414

Active member
Local time
Today, 15:56
Joined
May 28, 2021
Messages
279
I'm not sure, this does return your output. I still don't get it though.
SQL:
SELECT t.*
FROM tbl AS t
INNER JOIN (
    SELECT Customer, MIN(IIF(ISNULL(Shipping), Delivery, IIF(ISNULL(Delivery), Shipping, IIF(Shipping < Delivery, Shipping, Delivery)))) AS MinDate
    FROM tbl
    WHERE Shipping IS NOT NULL OR Delivery IS NOT NULL
    GROUP BY Customer
) AS sub
ON t.Customer = sub.Customer AND (IIF(ISNULL(t.Shipping), t.Delivery, IIF(ISNULL(t.Delivery), t.Shipping, IIF(t.Shipping < t.Delivery, t.Shipping, t.Delivery)))) = sub.MinDate
ORDER BY t.Customer
 

561414

Active member
Local time
Today, 15:56
Joined
May 28, 2021
Messages
279
Access likes this better
SQL:
SELECT
  t.Customer,
  t.ProductFK,
  t.Shipping,
  t.Delivery
FROM
  tbl AS t
WHERE
  (
    (
      (
        IIf(
          IsNull([t].[Shipping]),
          [t].[Delivery],
          IIf(
            IsNull([t].[Delivery]),
            [t].[Shipping],
            IIf(
              [t].[Shipping] < [t].[Delivery], [t].[Shipping],
              [t].[Delivery]
            )
          )
        )
      )=(
        SELECT
          MIN(
            IIF(
              ISNULL(Shipping),
              Delivery,
              IIF(
                ISNULL(Delivery),
                Shipping,
                IIF(
                  Shipping < Delivery, Shipping, Delivery
                )
              )
            )
          )
        FROM
          tbl
        WHERE
          Customer = t.Customer
          AND (
            Shipping IS NOT NULL
            OR Delivery IS NOT NULL
          )
      )
    )
  )
ORDER BY
  t.Customer;

Anyway, I need to sleep, hope someone comes up with a better one.
 

KitaYama

Well-known member
Local time
Tomorrow, 05:56
Joined
Jan 6, 2022
Messages
1,582
Shipping is always smaller than Delivery?
Sure. Shipping is always smaller than delivery. But I can not check for only delivery.
Because there are overlapping dates that makes it harder to work with.
If you look at Customer2 data
CustomerProductFKShippingDelivery
Customer2
2​
2023/06/03​
2023/06/10​
Customer2
3​
2023/06/05​
2023/06/08​
First records shipping is sooner than the second's delivery. Even though the second record's delivery is smaller.
So in this case I need the first record as the result.
Because between the four dates, the first one's shipping is the smallest.
We should first start on first record even if the delivery is later.

Thanks for helping.
I'll check your try and will be back.
 

KitaYama

Well-known member
Local time
Tomorrow, 05:56
Joined
Jan 6, 2022
Messages
1,582
Access likes this better
SQL:
SELECT
  t.Customer,
  t.ProductFK,
  t.Shipping,
  t.Delivery
FROM
  tbl AS t
WHERE
  (
    (
      (
        IIf(
          IsNull([t].[Shipping]),
          [t].[Delivery],
          IIf(
            IsNull([t].[Delivery]),
            [t].[Shipping],
            IIf(
              [t].[Shipping] < [t].[Delivery], [t].[Shipping],
              [t].[Delivery]
            )
          )
        )
      )=(
        SELECT
          MIN(
            IIF(
              ISNULL(Shipping),
              Delivery,
              IIF(
                ISNULL(Delivery),
                Shipping,
                IIF(
                  Shipping < Delivery, Shipping, Delivery
                )
              )
            )
          )
        FROM
          tbl
        WHERE
          Customer = t.Customer
          AND (
            Shipping IS NOT NULL
            OR Delivery IS NOT NULL
          )
      )
    )
  )
ORDER BY
  t.Customer;

Anyway, I need to sleep, hope someone comes up with a better one.
Will test and report back.
Thanks.
 

KitaYama

Well-known member
Local time
Tomorrow, 05:56
Joined
Jan 6, 2022
Messages
1,582
@ebs17 @561414
First experiments were promising. For a second I thought we have the answer, but my colleague's tests proved me wrong.
He added several new records for Customer4 as following:
CustomerProductFKShippingDelivery
Customer4
6​
2024/01/01​
2024/01/02​
Customer4
1​
2023/03/12​
Customer4
2​
2023/02/01​
2023/04/12​
Customer4
6​
2023/02/01​
2023/02/02​
this is the result :

3.png


Problems:
1- Two records for Customer4

Thanks again.
 
Last edited:

561414

Active member
Local time
Today, 15:56
Joined
May 28, 2021
Messages
279
@ebs17 @561414
First experiments were promising. For a second I thought we have the answer, but my colleague's tests showed I'm wrong.
He added several new records for Customer4 as following:

this is the result :

View attachment 108150

Problems:
1- Two records for Customer4

Thanks again.
Those two records have the same shipping date, what do you do in this case?
 

ebs17

Well-known member
Local time
Today, 22:56
Joined
Feb 7, 2020
Messages
1,991
Problems:
1- Two records for Customer4
Naturally. If 2023/02/01 occurs five times as the smallest date, there are five records due to equality.

You have to differentiate more clearly in the sorting:
SQL:
         ORDER BY
            Nz(T0.Shipping, T0.Delivery),
            T0.Delivery


         ORDER BY
            Nz(T0.Shipping, T0.Delivery),
            T0.ID

Other sorting criteria differentiate more precisely. At the latest, the unique ID then only allows one record (per customer).

The problem will rather be the performance, due to the system correlated subquery.
Good indexing can mitigate the problem somewhat, but doesn't really fix it.
 

KitaYama

Well-known member
Local time
Tomorrow, 05:56
Joined
Jan 6, 2022
Messages
1,582
Naturally. If 2023/02/01 occurs five times as the smallest date, there are five records due to equality.

You have to differentiate more clearly in the sorting:
SQL:
         ORDER BY
            Nz(T0.Shipping, T0.Delivery),
            T0.Delivery


         ORDER BY
            Nz(T0.Shipping, T0.Delivery),
            T0.ID

Other sorting criteria differentiate more precisely. At the latest, the unique ID then only allows one record (per customer).
While I'm testing here's another problem.

tbl tbl
CustomerProductFKShippingDelivery
Customer4
6​
2024/01/01​
2024/02/02​
Customer4
1​
2023/02/02​
Customer4
2​
2023/02/05​
Customer4
6​
2023/02/01​
2023/02/05​

This is the result

6.png


I don't think it's an sort as mentioned above.
Customer 4 should show the second record not the last one.


Sorry I was wrong. The result is correct.
Still testing.
Thanks
 
Last edited:

ebs17

Well-known member
Local time
Today, 22:56
Joined
Feb 7, 2020
Messages
1,991
Customer 4 should show the second record not the last one.
Why?
2023/02/02 is smaller as 2023/02/01?

Rules should be kept away from a lottery wheel.
 

KitaYama

Well-known member
Local time
Tomorrow, 05:56
Joined
Jan 6, 2022
Messages
1,582
Why?
2023/02/02 is smaller as 2023/02/01?

Rules should be kept away from a lottery wheel.
Sorry you are correct.
I was in a hurry and 0 concentrated.
Saturday evening and the weight and stress of a whole week on my shoulder.

My Apologies.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:56
Joined
Feb 28, 2001
Messages
27,332
My approach might be simpler. I tend to use Divide and Conquer techniques. Start this way:

Code:
SELECT Customer, ProductFK, Shipping, Delivery, 
   IIF( NZ( Shipping, #1-Jan-2099#) < NZ( Delivery, #1-Jan-2099# ), Shipping, Delivery ) As LeastDate
FROM tbl

The NZ sequence picks an arbitrary future date that would be unlikely to be less than any applicable date. If you REALLY want to assure you can't get caught on that date, you can go as high as #31-Dec-9999# as a future date place-holder.

Then write a simple layered query that gives you the record that has MIN( LeastDate ). You could even use the first query in a DLookup if you only wanted one date and know the customer and product.
 

KitaYama

Well-known member
Local time
Tomorrow, 05:56
Joined
Jan 6, 2022
Messages
1,582
Doc, This is Saturday night and I'm home. Our server is scheduled to be down for maintenance for the weekend and I don't have remote desktop access to my PC at work. Unfortunately I don't have Access at home.
I'll test it Monday morning as soon as I'm back to my desk.


Then write a simple layered query that gives you the record that has MIN( LeastDate ). You could even use the first query in a DLookup if you only wanted one date and know the customer and product.
No, I need a list. This list will be used for the priority for manufacturing line. So we have to see the whole list to decide which job will be the next one.

Thank you.
 

ebs17

Well-known member
Local time
Today, 22:56
Joined
Feb 7, 2020
Messages
1,991
Code:
IIF( NZ( Shipping, #1-Jan-2099#) < NZ( Delivery, #1-Jan-2099# ), Shipping, Delivery )

' is easier than
Nz(Shipping, Delivery)
I would be interested in the reasoning.

As a solution via a JOIN:
SQL:
SELECT
   T.Customer,
   T.ProductFK,
   T.Shipping,
   T.Delivery
FROM
   tbl AS T
      INNER JOIN
         (
            SELECT
               Customer,
               MIN(Nz(Shipping, Delivery)) AS MinDate,
               MIN(Delivery) AS MinDelivery
            FROM
               tbl
            GROUP BY
               Customer
         ) AS X
         ON T.Customer = X.Customer
            AND
         Nz(T.Shipping, T.Delivery) = X.MinDate
            AND
         T.Delivery = X.MinDelivery
 

Users who are viewing this thread

Top Bottom