Solved

Count unique field values within query

Posted on 2010-08-20
10
936 Views
Last Modified: 2013-11-27
I am hoping to be able to eliminate additional queries that are used simply to count unique values in a query.

Here's a sample of fields in my base query:
FIPS_Code
ExpNo
CheckProduct
OtherProduct
CheckPrice
OtherPrice

I have a summary query that returns:
Averages for CheckPrice and OtherPrice
Count of unique values in ExpNo (2 queries-One to group ExpNo and then one to count those records)
Count of unique values in CheckProduct (2 queries-One to group CheckProduct and then one to count those records)
Count of unique values in OtherProduct (2 queries-One to group OtherProduct and then one to count those records)

I am repeating this process for 4 different sets of records. Surely, there's a better way. Any suggestions would be greatly appreciated.

Thanks, Dale
0
Comment
Question by:dlogan7
10 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 33485183
see this article by MathewsPatrick, and try to apply the function in your query


http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2417-Calculating-Distinct-Counts-in-Access.html
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 33485614
Rey,

Thanks for recommeding my article!

Dale,

That article explains how to perform distinct counts using SQL statements as well as using a VBA UDF, DCountDistinct.

Please let us know if you need help applying this to your situation.

Patrick
0
 

Author Closing Comment

by:dlogan7
ID: 33486421
Cap,

Thanks for pointing me to this article.

Mathew,

This is wonderful and works perfectly. I have been using the 2 step process for years and knew there had to be a more efficient way. I have already tested your VBA suggestion and can't wait to implement it into my projects.

I feel as though you deserve some points here, but don't know what the protocol would be.

Thanks to both of you.

Dale
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:dlogan7
ID: 33486437
Oops. I accidentally selected Mathews comment as the accepted solution. I will request attention and get it changed.
0
 

Author Comment

by:dlogan7
ID: 33486626
Patrick,

In your VBA suggestion, how would I incorporate excluding Null values in the criteria section? An example would be [PricePremium] Is Not Null.
0
 

Author Comment

by:dlogan7
ID: 33486680
Played around with this option and ended up with the correct number. Just wondering if it will always be correct?

DistinctCountOfTrials: DCountDistinct("[ExpNumber]","[*tempProductPerformance]","[PricePremium]<>0 or [PricePremium]=0")
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33486755
Dale,

Glad to help!

Please use the request attention link to ask the Mods to reopen, because cap1 deserves at least a split.

To incorporate the not null test, simply use this for the Criteria argument:

"[PricePremium] Is Not Null"

Patrick
0
 

Author Comment

by:dlogan7
ID: 33487257
Yep. That worked. Should have tried that one first. I was trying to put Is Not Null outside the brackets.

One of these days I may try to modify your code to perform another task. It's an odd one. Basically, I need to do the exact same thing, but instead of Count I want to Average unique values. For example I have a list of prices of products with duplicate prices per product throughout. I want to get the average of unique prices per product.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33497934
Hi dlogan7
Not sure if you have got your answers to this question, it's a bit hard to follow !

For you last post
< I want to Average unique values

Select AVG(DISTINCT Price) from products;

Let us know where your at with this, or if you have the answer and need to ask a new one.?

Kj


0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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