?
Solved

Count unique field values within query

Posted on 2010-08-20
10
Medium Priority
?
940 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:Dale Logan
[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
10 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 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:Dale Logan
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
RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

 

Author Comment

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

Author Comment

by:Dale Logan
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:Dale Logan
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 93

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:Dale Logan
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

764 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