Not counting duplicates

  • Thread starter Thread starter T
  • Start date Start date
T

T

I want to create a formula in a query so that it does not count duplicate
entries.
There will be the same individual which will be listed numerous times but I
want an individual to be counted only once.

I have the following fields in a table called tbl Units

Fields are:
Dem Firstname
Dem Middle Name
Dem Surname

Dcount did not work for me.
Please help.
 
Hi,


SELECT FirstName, MiddleName, SurName, COUNT(*)
FROM ( SELECT DISTINCT FirstName, MiddleName, SurName
FROM tableName ) As x
GROUP BY FirstName, MiddleName, SurName



Avoid space in fields name. Use CAPTION to supply a human agreeable header,
but for the fields themselves, avoid "illegal" name: that simplifies your
SQL statement, with fields names, and your user are also happy, since they
see the caption, when there is one, not the (cryptic) field name.


Hoping it may help,
Vanderghast, Access MVP
 
I interpreted your question slightly different from Michel and hence a
different suggestion:


SELECT Count(*) As PersonCount
FROM
( SELECT DISTINCT [Dem Firstname], [Dem Middle Name], [Dem Surname]
FROM [tbl Units] )


If you are still in the early stage of the database design, try changing
Table / Field names as Michel suggested.
 
Hi,


You are absolutely right, my solution returns just a list of ones for
each of the count... :-(
I would need to remove a field in the GROUP BY to get something interesting
(or to add an extra field in the SELECT DISTINCT) to get a distinct count
"by group".

Vanderghast, Access MVP
 
I needed to do the same thing and found this very helpful. However I have a
little additional piece I'm hoping you can assist with. I need the cound to
be only from entries for the previous month. I changed this ...

SELECT Count(*) As Count
FROM
( SELECT DISTINCT [TMIG]
FROM [tblsurvey response] )

to ...

SELECT Count(*) As Count
FROM
( SELECT DISTINCT [ID]
FROM [tbl] )
WHERE (((Month([tbl].[DATE]))=Month(DateAdd("m",-1,Now()))));

but I get an error. What am I doing wrong?





Van T. Dinh said:
I interpreted your question slightly different from Michel and hence a
different suggestion:


SELECT Count(*) As PersonCount
FROM
( SELECT DISTINCT [Dem Firstname], [Dem Middle Name], [Dem Surname]
FROM [tbl Units] )


If you are still in the early stage of the database design, try changing
Table / Field names as Michel suggested.

--
HTH
Van T. Dinh
MVP (Access)


T said:
I want to create a formula in a query so that it does not count duplicate
entries.
There will be the same individual which will be listed numerous times but I
want an individual to be counted only once.

I have the following fields in a table called tbl Units

Fields are:
Dem Firstname
Dem Middle Name
Dem Surname

Dcount did not work for me.
Please help.
Click to expand...
Click to expand...
 
In my last post it looks like I had some incorrect wording, those were just
in my post in the actual Access Database they are correct please ignore the
fact that the Table Names or Field Names may look different they are the same
in the database.


FrankM said:
I needed to do the same thing and found this very helpful. However I have a
little additional piece I'm hoping you can assist with. I need the cound to
be only from entries for the previous month. I changed this ...

SELECT Count(*) As Count
FROM
( SELECT DISTINCT [ID]
FROM [tbl] )

to ...

SELECT Count(*) As Count
FROM
( SELECT DISTINCT [ID]
FROM [tbl] )
WHERE (((Month([tbl].[DATE]))=Month(DateAdd("m",-1,Now()))));

but I get an error. What am I doing wrong?





Van T. Dinh said:
I interpreted your question slightly different from Michel and hence a
different suggestion:


SELECT Count(*) As PersonCount
FROM
( SELECT DISTINCT [Dem Firstname], [Dem Middle Name], [Dem Surname]
FROM [tbl Units] )


If you are still in the early stage of the database design, try changing
Table / Field names as Michel suggested.

--
HTH
Van T. Dinh
MVP (Access)


T said:
I want to create a formula in a query so that it does not count duplicate
entries.
There will be the same individual which will be listed numerous times but I
want an individual to be counted only once.

I have the following fields in a table called tbl Units

Fields are:
Dem Firstname
Dem Middle Name
Dem Surname

Dcount did not work for me.
Please help.
Click to expand...
Click to expand...
Click to expand...
 
Try:

****
SELECT Count(*) As MyCount
FROM
(
SELECT DISTINCT [ID]
FROM [tbl]
WHERE Month([tbl].[DATE])=Month(DateAdd("m",-1,Now()))
)
****
 
Is it critical to change the Now to Date ? I tried with the Now and it worked
perfectly, made me very happy, thank you very much but when I tried changing
the Now to Date for some reason I got an error message. I've been playing
around with this for a while and I swear I tried what you suggested before
but it didn't work, when I tried your suggestion noe it worked great, must
have been something I mistyped or something. Thank you again!



Van T. Dinh said:
Also change Now() to Date().

--
HTH
Van T. Dinh
MVP (Access)




Van T. Dinh said:
Try:

****
SELECT Count(*) As MyCount
FROM
(
SELECT DISTINCT [ID]
FROM [tbl]
WHERE Month([tbl].[DATE])=Month(DateAdd("m",-1,Now()))
)
****
Click to expand...
Click to expand...
 
Back
Top