count unique numbers in a Table Row source

[SDRichardC]

I have created a query table and want to count the distinct or unique number in the table-row. I have found some sort of a solution but it is not working for me.

The solution was to use sub-queries in SQL statements nested with two other sub-queries. My data in the table row is called Order a numeric data type. Say I have order number
1001, 1002, 1001, 1003 in each row field. I want to count the unique numbers totaling 1001 = 1 and 1002 = 1 and 1003 = 1. How do I go about creating the criteria? Thank you.

 

[MajP]

If I understand correctly you have some non normalized data like.

ID fld1 fld2 fld3 fld4
1 1001 1002 1001 1003
2 1234 1234 4567 4567

Just do a union query to normalize

select
ID,
Fld1 as NewFld
From
Table1
Union
Select
ID,
Fld2 as NewFld
From
Table1
....
Union
Select
ID,
FldN as NewFld
From
Table 1


Your data will look like
ID NewFld
1 1001
1 1002
1 1001
1 1003
2 1234
2 1234
2 4567
2 4567

Now it can be done simply in a aggregate query to get
iD Val Count
1 1001 2
1 1002 1
1 1003 1
2 1234 2
2 4567 2