Solved

Access query to return unique values of multiple rows

Posted on 2009-04-08
11
310 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

863 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

17 Experts available now in Live!

Get 1:1 Help Now