Solved

Access query to return unique values of multiple rows

Posted on 2009-04-08
11
309 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 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

15 Experts available now in Live!

Get 1:1 Help Now