Have results split into two colums (1 Viewer)

[Ken Sheridan]

I'm trying to say that it cannot be decided which of these is correct:
A14212 19/06/2025 111 444242 5 --- 1
A14212 19/06/2025 111 444242 9 --- 2
or
A14212 19/06/2025 111 444242 5 --- 2
A14212 19/06/2025 111 444242 9 --- 1

Maybe the OP does not care. That would then be the additional rule.

In the 28 years in which I have been answering newsgroup and forum posts I have always based my replies on the same basis as I would in my professional life, that any conclusion can not be more than a working hypothesis. If this can be tested by observation then the hypothesis stands until contrary evidence becomes available.

Firstly what are the known facts? In this case the OP has categorically stated that there can be no more than two bins per order. I would not presume to think I know their job better than they do, and doubt that this is the case.

Secondly what can be reasonably inferred from the available evidence? In the original post the OP shows the Pick_Bin_1 column to contain the lower values per order and Pick_Bin_2 the higher value.

The OP's example includes no rows with the same value in each Pick_Bin column.

On the basis of the available evidence, therefore, the simple aggregating query I posted earlier provides a solution. If the evidence should change, so that the hypothesis does not stand up to observation, then the hypothesis would be revised or discarded. That's how science works.

 

[CJ_London]

I'm late jumping into this, but can't you use a simple aggregating query:

Would only ever be max of 2

Which implies there can be 1

So your aggregate query would need to be modified to be conditional on the max <> min for bin 2

Iif(max <>min,max) as bin2