Solved

Access query to return unique values of multiple rows

Posted on 2009-04-08
11
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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…

759 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