I have a similar to below:
| ProductID | Division | OrdNum |
|---|---|---|
| 1 | 011 | 123 |
| 1 | 705 | 123 |
| 1 | 660 | 123 |
| 2 | 011 | 511 |
| 2 | 705 | 412 |
| 2 | 660 | 216 |
I am trying to write a query that will return each product ID and if the OrdNum for all Divisions is the same it will return the OrdNum, and if the OrdNums are different it will return "Varies"
The results should look like this:
| ProductID | OrdNum |
|---|---|
| 1 | 123 |
| 2 | Varies |
CodePudding user response：
Consider:
Query1:
SELECT DISTINCT Table1.ProductID, Table1.OrdNum FROM Table1;
Query2:
SELECT ProductID, Count(OrdNum) AS CountOfOrdNum
FROM Query1
GROUP BY ProductID;
Query3:
SELECT Table1.ProductID, IIf([CountOfOrdNum]=1,[OrdNum],"Varies") AS Ord
FROM Query2 INNER JOIN Table1 ON Query2.ProductID = Table1.ProductID
GROUP BY Table1.ProductID, IIf([CountOfOrdNum]=1,[OrdNum],"Varies");
CodePudding user response：
Below query may work for you.
select t2.ProductID,iif(t2.OrdCount>1,"Varies",DLookup("[OrdNum]","[Table1]","[ProductID]="&t2.ProductID)) as OrdNum
from (select t1.ProductID, count(t1.ProductID) as OrdCount
from (select ProductID, OrdNum, Count(Division) AS CountOfDivision from Table1 group by ProductID, OrdNum) as t1 group by t1.ProductID) as t2
