Count Records (only count duplicate record once)

[JennyL]

Hi,



I have been searching all day on how to count duplicate record once but no luck. I have a form with list of records but sometimes same customer id is used. I want to have a unbound txtbox to show

- number of Active Status with L90 field counted once
- number of Closed status with L90 fields counted once


I tried to do it through query but don't know how to tell the query to count only once for L90 field.

any recommendation on how I can do this easily?

[ranman256]

make a query to pull your data needed, and set the query property UNIQUE VALUES = TRUE.
this will eliminate duplicates.

Then make a query on THAT query to count.

[JennyL]

I did but still didn't work. I must be missing something. Could you provide details on how to do it? I went to Property sheet and change the Unique Values to YES.

make a query to pull your data needed, and set the query property UNIQUE VALUES = TRUE.
this will eliminate duplicates.

Then make a query on THAT query to count.

[ranman256]

you only want fields in the query that will produce a unique record.
you cant add say fields in the query that have nothing to do with what you want
if you want to count persons who attended:
name, date.

having the date field will produce duplicate names (even tho they are diffenent dates)
so the Q1 would only have
NAME
(but date would be in the criteria, but not shown)
now you have unique names.

[JennyL]

I am not sure if I follow your description above.

what I have is

ID
Status
AccountNumber
L90
L20

L90 information could be repeated in some records.

I want the query to show number of L90's in Active Status (only counting L90 once), if that makes sense.

I tried changing the unique record but still same result.

Thanks,

[aytee111]

Query 1 - All L90's with Active Status:
SELECT DISTINCT Table1.L90 FROM Table1 WHERE (((Table1.Status)="Active"));

Query 2 - count the number of records in Query 1
SELECT Count(Query1.L90) AS CountOfL90 FROM Query1;