query

[Saeed]

been a while doing SQL programming , its all wiped.
If i have a table with a field say refNo

1001
1002
1003
1001



How woud I be able to get a query telling me the distinc RefNo's with
the number of occurance (duplicates in the table for each RefNo)

Something like

1001 2
1002 1
1003 1


Cheers

 

[John W. Vinson]

been a while doing SQL programming , its all wiped.
If i have a table with a field say refNo

1001
1002
1003
1001



How woud I be able to get a query telling me the distinc RefNo's with
the number of occurance (duplicates in the table for each RefNo)

Something like

1001 2
1002 1
1003 1


Cheers

A simple TOTALS query:

SELECT RefNo, Count(*)
FROM tablename
GROUP BY RefNo;

In the query grid, add the table; Group By the field you want distinct; and
add the field a second time and choose Count.