Solved

Access query to return unique values of multiple rows

Posted on 2009-04-08
11
314 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
Stressed Out?

Watch some penguins on the livecam!

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

623 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