Solved

Count unique field values within query

Posted on 2010-08-20
10
931 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 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:dlogan7
Comment Utility
Oops. I accidentally selected Mathews comment as the accepted solution. I will request attention and get it changed.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:dlogan7
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now