Count function help

[Cam]

Hello,

I have a query trying add a column to count the number of records based on 2
matching fields/ columns. Not sure how to calculate or what function to use
in query design.

Count the number of Ord with the criteria both the Date and Part field
matches. Below is an example of what I want to achieve. Thanks for any help.

Current query:
Date Part Ord
6/11/09 4310-6 335385
6/11/09 4310-6 336441
6/11/09 4310-6 336440
6/11/09 1147-1 336525
6/12/09 4510-11 336982
6/15/09 4510-11 337001
6/15/09 4110-14 336414

Result query wanted: with added Count column
Date Part Ord Count
6/11/09 4310-6 335385 3
6/11/09 4310-6 336441 3
6/11/09 4310-6 336440 3
6/11/09 1147-1 336525 1
6/12/09 4510-14 336982 1
6/15/09 4510-11 337001 2
6/15/09 4110-11 336414 2

 

[John Spencer MVP]

One way would be to construct and save a query that gets the count by date and
part.

SELECT [Date], [Part], Count([Part]) as theCount
FROM [Your Table]
GROUP BY [Date], [Part]

Then add that query as a table to your current query and link the query and
the table on Date and Part fields.

Slower method would be to use the DCount function in a calculated field in
your query.
Field:
DCount("*","YourTable","[Date]=#" & [Date] & "# AND Part='" & [Part] & "' ")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Cam]

Thanks John,

Actually my mistake it is not what I want for the count column.
I want the Count column to return a sequential number 1 to 3 if they are 3
matching records. Here is my modified example version.

Current query:
Date Part Ord
6/11/09 4310-6 335385
6/11/09 4310-6 336441
6/11/09 4310-6 336440
6/11/09 1147-1 336525
6/12/09 4510-14 336982
6/15/09 4510-11 337001
6/15/09 4510-11 336414

Returned Query:
Date Part Ord Count
6/11/09 4310-6 335385 1
6/11/09 4310-6 336441 2
6/11/09 4310-6 336440 3
6/11/09 1147-1 336525 1
6/12/09 4510-14 336982 1
6/15/09 4510-11 337001 1
6/15/09 4510-11 336414 2

Instead of 6/11/09 and 4310-6 return 3, 3, 3 in Count column, it will be 1,
2, 3.

One way would be to construct and save a query that gets the count by date and
part.

SELECT [Date], [Part], Count([Part]) as theCount
FROM [Your Table]
GROUP BY [Date], [Part]

Then add that query as a table to your current query and link the query and
the table on Date and Part fields.

Slower method would be to use the DCount function in a calculated field in
your query.
Field:
DCount("*","YourTable","[Date]=#" & [Date] & "# AND Part='" & [Part] & "' ")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

I have a query trying add a column to count the number of records based on 2
matching fields/ columns. Not sure how to calculate or what function to use
in query design.

Count the number of Ord with the criteria both the Date and Part field
matches. Below is an example of what I want to achieve. Thanks for any help.

Current query:
Date Part Ord
6/11/09 4310-6 335385
6/11/09 4310-6 336441
6/11/09 4310-6 336440
6/11/09 1147-1 336525
6/12/09 4510-11 336982
6/15/09 4510-11 337001
6/15/09 4110-14 336414

Result query wanted: with added Count column
Date Part Ord Count
6/11/09 4310-6 335385 3
6/11/09 4310-6 336441 3
6/11/09 4310-6 336440 3
6/11/09 1147-1 336525 1
6/12/09 4510-14 336982 1
6/15/09 4510-11 337001 2
6/15/09 4110-11 336414 2

 

[John Spencer MVP]

You can use a ranking query to get the information. HOWEVER, if you need this
for a report there is a much simpler method to have a line counter in a report.

In a query, you can get the rank (count) using something like the following.

SELECT A.[Date], A.[Part], 1 + Count(B.[Part]) as theCount
FROM [Your Table] as A Left Join [Your Table] as B
On A.Date = B.Date
AND A.Part = B.Part
AND A.Ord > B.Ord
GROUP BY A.[Date], A.[Part]

Post back if you need instructions on how to have a line counter that resets
for each group in a report.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks John,

Actually my mistake it is not what I want for the count column.
I want the Count column to return a sequential number 1 to 3 if they are 3
matching records. Here is my modified example version.

Current query:
Date Part Ord
6/11/09 4310-6 335385
6/11/09 4310-6 336441
6/11/09 4310-6 336440
6/11/09 1147-1 336525
6/12/09 4510-14 336982
6/15/09 4510-11 337001
6/15/09 4510-11 336414

Returned Query:
Date Part Ord Count
6/11/09 4310-6 335385 1
6/11/09 4310-6 336441 2
6/11/09 4310-6 336440 3
6/11/09 1147-1 336525 1
6/12/09 4510-14 336982 1
6/15/09 4510-11 337001 1
6/15/09 4510-11 336414 2

Instead of 6/11/09 and 4310-6 return 3, 3, 3 in Count column, it will be 1,
2, 3.

One way would be to construct and save a query that gets the count by date and
part.

SELECT [Date], [Part], Count([Part]) as theCount
FROM [Your Table]
GROUP BY [Date], [Part]

Then add that query as a table to your current query and link the query and
the table on Date and Part fields.

Slower method would be to use the DCount function in a calculated field in
your query.
Field:
DCount("*","YourTable","[Date]=#" & [Date] & "# AND Part='" & [Part] & "' ")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello,

I have a query trying add a column to count the number of records based on 2
matching fields/ columns. Not sure how to calculate or what function to use
in query design.

Count the number of Ord with the criteria both the Date and Part field
matches. Below is an example of what I want to achieve. Thanks for any help.

Current query:
Date Part Ord
6/11/09 4310-6 335385
6/11/09 4310-6 336441
6/11/09 4310-6 336440
6/11/09 1147-1 336525
6/12/09 4510-11 336982
6/15/09 4510-11 337001
6/15/09 4110-14 336414

Result query wanted: with added Count column
Date Part Ord Count
6/11/09 4310-6 335385 3
6/11/09 4310-6 336441 3
6/11/09 4310-6 336440 3
6/11/09 1147-1 336525 1
6/12/09 4510-14 336982 1
6/15/09 4510-11 337001 2
6/15/09 4110-11 336414 2