Count of Duplicates

  • Thread starter Thread starter Schwimms
  • Start date Start date
S

Schwimms

I have a row in a query called job number. These job numbers sometimes get
duplicated/triplicated/quadruplicated ...etc. I need to insert a formula into
this query to count the number of times that job number shows up. Can anyone
help?
 
You can add another column in the query with DCount

CountOfJobNumber: DCount("*","TableName","[job number] = " & [job number])

Replace TableName with the real table name
If [job number] it's not the field name, then replace it

If the [job number] field is text, then replace the dcount with

CountOfJobNumber: DCount("*","TableName","[job number] = '" & [job number] &
"'")

adding single quotes
 
This works, Thank you, although it is very time consuming. Is there a shorter
way. I did have to use your second formula because the data was text.

Ofer Cohen said:
You can add another column in the query with DCount

CountOfJobNumber: DCount("*","TableName","[job number] = " & [job number])

Replace TableName with the real table name
If [job number] it's not the field name, then replace it

If the [job number] field is text, then replace the dcount with

CountOfJobNumber: DCount("*","TableName","[job number] = '" & [job number] &
"'")

adding single quotes
--
Good Luck
BS"D


Schwimms said:
I have a row in a query called job number. These job numbers sometimes get
duplicated/triplicated/quadruplicated ...etc. I need to insert a formula into
this query to count the number of times that job number shows up. Can anyone
help?
Click to expand...
Click to expand...
 
Usually I'm creating another query that count the number of entries, and then
I add that query to the first query, linked by the job number field

Something like

SELECT [job number] , Count([job number]) AS CountOfJobs
FROM TableName
GROUP BY [job number]

Then link it and display the count, I think it wil be quicker

--
Good Luck
BS"D


Schwimms said:
This works, Thank you, although it is very time consuming. Is there a shorter
way. I did have to use your second formula because the data was text.

Ofer Cohen said:
You can add another column in the query with DCount

CountOfJobNumber: DCount("*","TableName","[job number] = " & [job number])

Replace TableName with the real table name
If [job number] it's not the field name, then replace it

If the [job number] field is text, then replace the dcount with

CountOfJobNumber: DCount("*","TableName","[job number] = '" & [job number] &
"'")

adding single quotes
--
Good Luck
BS"D


Schwimms said:
I have a row in a query called job number. These job numbers sometimes get
duplicated/triplicated/quadruplicated ...etc. I need to insert a formula into
this query to count the number of times that job number shows up. Can anyone
help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top