Solved

Access query to return unique values of multiple rows

Posted on 2009-04-08
11
311 Views
Last Modified: 2012-06-21
I am trying to return the count of unique fields across multple columns however when I run the query below I am not getting the correct results.

The query returns 203 when it should be 130. Sould I used some other function?
SELECT COUNT(*)as Count1 FROM (SELECT DISTINCT Employee, Add_emp1,Add_emp2,Add_emp3,Add_emp4,Add_emp5,Add_emp6,Add_emp7,Add_emp8 FROM Data WHERE(((Year([MDY]))=2008)))

Open in new window

0
Comment
Question by:ITHelper80
  • 5
  • 4
  • 2
11 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 24098196
try this:

select count(Distinct *) from ...
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24098220
I get a syntax error when I do that.
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24098420
Bummer. It would appear that JET does not support distinct count.

How many records are returned by your subquery when you run it by itself?
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 6

Author Comment

by:ITHelper80
ID: 24098469
you may be right becuase when I run the below I just see all the value and thier duplicates....surely there is some way to do this though.
SELECT DISTINCT Employee, Add_emp1,Add_emp2,Add_emp3,Add_emp4,Add_emp5,Add_emp6,Add_emp7,Add_emp8 FROM Data WHERE(((Year([MDY]))=2008))

Open in new window

0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24098783
I tihnk I should explain more...it does appear the DISTINCT function ISworking...I did it only on the Employee column and had no duplicates....

I need to get the DISTINCT value for each column (Employee thru Add_Emp8) and then get the sum of those counts....I figured I could run 9 seperate queries but that seems like overkill....
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24099373
What information is it that you are trying to get?  In your query, if one employee has 4 records that each have different information in Add_emp1, Add_emp2, Add_emp3, Add_emp4, Add_emp5, Add_emp6, Add_emp7, or Add_emp8 then there are 4 distinct records for that employee using the fields that you have specified in your Select statement.
0
 
LVL 15

Expert Comment

by:MNelson831
ID: 24099386
Show me a mock sample of what kind of data you have and then use that to give me an example of what the query should be returning.
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24099693
Sure, basically I have 9 columns with user names in the this format. Smith, John. The user could be placed in any one of those 9 columns for any row.

My end goal is just to capture a "unique" count of each user name; so if a user is listed 9 times in the db (in any one of the 9 columns) it would only return a 1 and finally I need to sum all the "unique" counts

Does that make more since?
0
 
LVL 1

Expert Comment

by:TheRadDude
ID: 24099751
The best way to do this is to do a union between each of the fields, and then to select a just the distinct records from there.  It's kind of ugly but it  should do the job

The Following code should work

Select count(Emp) from
(
Select Distinct Emp from
(
Select distinct Employee as Emp from Data WHERE(((Year([MDY]))=2008))) union
Select distinct Add_emp1 as Emp from Data WHERE(((Year([MDY]))=2008))) union
Select distinct Add_emp2 as Emp from Data WHERE(((Year([MDY]))=2008))) union
Select distinct Add_emp3 as Emp from Data WHERE(((Year([MDY]))=2008))) union
Select distinct Add_emp4 as Emp from Data WHERE(((Year([MDY]))=2008))) union
Select distinct Add_emp5 as Emp from Data WHERE(((Year([MDY]))=2008))) union
Select distinct Add_emp6 as Emp from Data WHERE(((Year([MDY]))=2008))) union
Select distinct Add_emp7 as Emp from Data WHERE(((Year([MDY]))=2008))) union 
Select distinct Add_emp8 as Emp from Data) as A
) as A

Open in new window

0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24099766
Thanks. I tried the query and I get a syntax error about the union statement.
0
 
LVL 1

Accepted Solution

by:
TheRadDude earned 500 total points
ID: 24099826
Sorry about that, got a little sloppy itn the code, and forgot the spaces after the where
Select count(Emp) from
(
Select Distinct Emp from
(
Select distinct Employee as Emp from Data WHERE Year([MDY])=2008 union
Select distinct Add_emp1 as Emp from Data WHERE Year([MDY])=2008 union
Select distinct Add_emp2 as Emp from Data WHERE Year([MDY])=2008 union
Select distinct Add_emp3 as Emp from Data WHERE Year([MDY])=2008 union
Select distinct Add_emp4 as Emp from Data WHERE Year([MDY])=2008 union
Select distinct Add_emp5 as Emp from Data WHERE Year([MDY])=2008 union
Select distinct Add_emp6 as Emp from Data WHERE Year([MDY])=2008 union
Select distinct Add_emp7 as Emp from Data WHERE Year([MDY])=2008 union 
Select distinct Add_emp8 as Emp from Data WHERE Year([MDY])=2008) as A
) as A

Open in new window

0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

773 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