[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Count unique field values grouped by mach

Posted on 2015-01-31
5
Medium Priority
?
74 Views
Last Modified: 2015-02-02
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
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 40581706
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40581723
try this query


SELECT Table1.Machine, Table1.POn, Count(Table1.POn) AS CountOfPOn
FROM Table1
GROUP BY Table1.Machine, Table1.POn;
0
 

Author Comment

by:SteveL13
ID: 40583963
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40584204
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
 

Author Closing Comment

by:SteveL13
ID: 40584211
Worked.  I had a typo.  When I fixed it all was fine.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question