Help with counting in query

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
 
K

KARL DEWEY

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]);
 
C

Cam

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...
 
K

KARL DEWEY

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count function help 3
Selecting first record from a sorted query 6
functions in queries 1
Numbering....??? 4
Help with totals function in query 2
WCG Stats Monday 17 July 2023 2
WCG Stats Thursday 24 August 2023 3
WCG Stats Thursday 07 September 2023 3

Top