Solved

Count unique field values within query

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

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

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.

Question has a verified solution.

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

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

809 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