Dale Logan
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oops. I accidentally selected Mathews comment as the accepted solution. I will request attention and get it changed.
ASKER
Patrick,
In your VBA suggestion, how would I incorporate excluding Null values in the criteria section? An example would be [PricePremium] Is Not Null.
In your VBA suggestion, how would I incorporate excluding Null values in the criteria section? An example would be [PricePremium] Is Not Null.
ASKER
Played around with this option and ended up with the correct number. Just wondering if it will always be correct?
DistinctCountOfTrials: DCountDistinct("[ExpNumber ]","[*temp ProductPer formance]" ,"[PricePr emium]<>0 or [PricePremium]=0")
DistinctCountOfTrials: DCountDistinct("[ExpNumber
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
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
ASKER
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.
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.
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
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
ASKER
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