• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 953
  • Last Modified:

Count unique field values within query

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
Dale Logan
Asked:
Dale Logan
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
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
 
Patrick MatthewsCommented:
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
 
Dale LoganConsultantAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Dale LoganConsultantAuthor Commented:
Oops. I accidentally selected Mathews comment as the accepted solution. I will request attention and get it changed.
0
 
Dale LoganConsultantAuthor Commented:
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
 
Dale LoganConsultantAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
Dale LoganConsultantAuthor Commented:
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
 
kingjelyCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now