Access query to return unique values of multiple rows

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

LVL 6
ITHelper80Asked:
Who is Participating?
 
TheRadDudeConnect With a Mentor Commented:
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
 
MNelson831Commented:
try this:

select count(Distinct *) from ...
0
 
ITHelper80Author Commented:
I get a syntax error when I do that.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
MNelson831Commented:
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
 
ITHelper80Author Commented:
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
 
ITHelper80Author Commented:
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
 
MNelson831Commented:
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
 
MNelson831Commented:
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
 
ITHelper80Author Commented:
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
 
TheRadDudeCommented:
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
 
ITHelper80Author Commented:
Thanks. I tried the query and I get a syntax error about the union statement.
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.