unique count

[Mark J Kubicki]

looking for a way to count the total number of field entries that are unique
in 2 separate fields in the same table

ex:
field1 values: A, A, B, C,
field2 values: B, D

count of unique values is 4 (ABCD)

the number is being assigned to a text box on a form

thanks in advance,
mark

 

[Damian S]

Hi Mark,

There's probably an easier way, and I'm sure someone will reply and say so,
but this sort of thing is easy to handle if you break it down into parts...
like this.

Query1 -
select field1 as FieldToCount from table1
union
select field2 as FieldToCount from table1

Query2

select distinct FieldToCount from Query1

Query3

select count([FieldToCount]) as NumberUnique from Query2

Set your field to be something like dlookup("[NumberUnique]", "Query3")

Obviously you want to use more meaningful names than query1, 2 and 3.

Hope this helps.

Damian.