Count of Duplicates

[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?

 

[Ofer Cohen]

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

 

[Schwimms]

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.

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

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?

 

[Ofer Cohen]

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

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.

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

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?