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
Solved

How count number of unique text strings exist in a table/field

Posted on 2014-02-19
5
458 Views
Last Modified: 2014-02-19
I have a table named tblDefectCauses that contains an OpenOrdRecID and a field named DefectCauses.

If in the table using job #23904 for example we have the following in the DefectCauses field:

OpenOrdRecID        DefectCause
23904                           Scrap
23904                           Melted
23904                           Scrap
23904                           Burned
23904                           Melted
23904                           Rotten

There are 6 records.  But the are only 4 different Defect causes.  

I want to write a query that will give me a result of 4 (different Defect Causes).

What would the query look like in SQL?
0
Comment
Question by:SteveL13
  • 3
5 Comments
 
LVL 19

Expert Comment

by:Peter Hutchison
ID: 39871916
The following sql command will list all distinct values from a field in a table.
Select Distinct DefectCause from tblDefectCauses;
0
 

Author Comment

by:SteveL13
ID: 39871928
I probably don't understand what you just wrote.  I want to end up with a result of 4 even though there are 6 records.
0
 

Author Comment

by:SteveL13
ID: 39871929
I tried:

SELECT DISTINCT Count(tblCleanedDefects.DefectCause) AS CountOfDefectCause, tblCleanedDefects.OpenOrdRedID
FROM tblCleanedDefects
GROUP BY tblCleanedDefects.OpenOrdRedID
HAVING (((Count(tblCleanedDefects.DefectCause)) Is Not Null))
ORDER BY tblCleanedDefects.OpenOrdRedID;

But I still get 6 instead of 4.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39871937
try this

Select A.OpenOrdRecID,Count(A.DefectCauses) As CountOfDefects
From
(
SELECT D.OpenOrdRecID, D.DefectCauses
FROM tblDefectCauses AS D
GROUP BY D.OpenOrdRecID, D.DefectCauses
) As A
Group By A.OpenOrdRecID

or


Select A.OpenOrdRecID, Count(A.DefectCauses) as CountOfDefects
From
(
SELECT tblDefectCauses.OpenOrdRecID, tblDefectCauses.DefectCauses
FROM tblDefectCauses
GROUP BY tblDefectCauses.OpenOrdRecID, tblDefectCauses.DefectCauses
) As A
Group By A.OpenOrdRecID
0
 

Author Comment

by:SteveL13
ID: 39871962
This worked perfectly.  Thanks.

Select A.OpenOrdRecID, Count(A.DefectCauses) as CountOfDefects
From
(
SELECT tblDefectCauses.OpenOrdRecID, tblDefectCauses.DefectCauses
FROM tblDefectCauses
GROUP BY tblDefectCauses.OpenOrdRecID, tblDefectCauses.DefectCauses
) As A
Group By A.OpenOrdRecID
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

860 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