Help with counting in query

  • Thread starter Thread starter Cam
  • Start date Start date
C

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
 
Try these --
QryOrderCount --
SELECT [Date], [Part], Count([Part]) AS Ord_Count
FROM YourTable
ORDER BY [Date], [Part];

SELECT [YourTable].[Date], [YourTable].[Part], [YourTable].[Ord], Ord_Count
FROM YourTable INNER JOIN QryOrderCount ON ([YourTable].[Date] =
[QryOrderCount].[Date] AND [YourTable].[Part] = [QryOrderCount].[Part]);
 
Karl,

Thanks for the tip. 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.

KARL DEWEY said:
Try these --
QryOrderCount --
SELECT [Date], [Part], Count([Part]) AS Ord_Count
FROM YourTable
ORDER BY [Date], [Part];

SELECT [YourTable].[Date], [YourTable].[Part], [YourTable].[Ord], Ord_Count
FROM YourTable INNER JOIN QryOrderCount ON ([YourTable].[Date] =
[QryOrderCount].[Date] AND [YourTable].[Part] = [QryOrderCount].[Part]);

Cam said:
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
Click to expand...
Click to expand...
 
Use the query substituting your table and field names --
SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;


Cam said:
Karl,

Thanks for the tip. 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.

KARL DEWEY said:
Try these --
QryOrderCount --
SELECT [Date], [Part], Count([Part]) AS Ord_Count
FROM YourTable
ORDER BY [Date], [Part];

SELECT [YourTable].[Date], [YourTable].[Part], [YourTable].[Ord], Ord_Count
FROM YourTable INNER JOIN QryOrderCount ON ([YourTable].[Date] =
[QryOrderCount].[Date] AND [YourTable].[Part] = [QryOrderCount].[Part]);

Cam said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top