Count unique field values grouped by mach

Using the attached d/b I am trying to write a query that will give me the following:

Grouped by Machine

TestMachine1 - 123 = the result should be 2 because there are two of them for that machine
TestMachine1 - 234 = the result should be 3 because there are three of them for that machine
TestMachine1 - 564 = the result should be 2 because there are two of them for that machine
TestMachine2 - 589 = the result should be 3 because there are three of them for that machine
TestMachine2 - 849 = the result should be 1 because there is one of them for that machine
TestMachine2 - 956 = the result should be 2 because there are two of them for that machine

--Steve
Unique-Values.accdb
SteveL13Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this query


SELECT Table1.Machine, Table1.POn, Count(Table1.POn) AS CountOfPOn
FROM Table1
GROUP BY Table1.Machine, Table1.POn;
0
 
rspahitzCommented:
One way is to create a calculated field that pulls out the machine name, something like:
Machine: Left(YourField, instr("-", YourField)

then sort on this calculated field (hidden) and show YourField
0
 
SteveL13Author Commented:
Neither of these suggestions seem to work.  Again the result should be:

TestMachine1 - 123 = the result should be 2 because there are two of them for that machine
 TestMachine1 - 234 = the result should be 3 because there are three of them for that machine
 TestMachine1 - 564 = the result should be 2 because there are two of them for that machine
 TestMachine2 - 589 = the result should be 3 because there are three of them for that machine
 TestMachine2 - 849 = the result should be 1 because there is one of them for that machine
 TestMachine2 - 956 = the result should be 2 because there are two of them for that machine
0
 
Rey Obrero (Capricorn1)Commented:
i don't know what you were doing, but the query i posted above will give you this result

Machine                    POn   CountOfPOn
TestMachine 1    123      2
TestMachine 1    234      3
TestMachine 1    564      2
TestMachine 2    589      3
TestMachine 2    849      1
TestMachine 2    956      2
0
 
SteveL13Author Commented:
Worked.  I had a typo.  When I fixed it all was fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.