Microsoft Access sql query to find records where all values in a column match

Question

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

Answer 1

Just compare the min and max values:

Select 
    ProductID,
    IIf(Min(OrdNum) = Max(OrdNum), First(OrdNum), "Varies") As OrderNumber
From
    YourTable
Group By
    ProductID

Answer 2

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