Solved

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

Posted on 2014-02-19
5
453 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 18

Expert Comment

by:Peter Hutchison
Comment Utility
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
Comment Utility
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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now