Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-12-2018, 11:15 AM   #1
Magnus1982
Newly Registered User
 
Join Date: Apr 2017
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Magnus1982 is on a distinguished road
Counting unique records and unique with condition

During my work with access I stock because of some problem. Looking help with this.
Let's say I have query (qryStat) which contain two columns:

Color Result

RED FAIL

RED PASS

BLUE FAIL

BLUE FAIL

BLUE PASS

GREEN FAIL

GREEN FAIL

PINK PASS

In the form (fmFinal) I have text box (txtBox1,tztBox2)
What I want to achieve:

1. txtBox1 - count unique value from Color column (in this case = 4)
2. txtBox2 - count colours which have Result PASS and don't have result FAIL (in this case = 1)

I try to do that with Dcount but I was able only to count all which have PASS or ALL which have FAIL

Can any one explain me how to do that?

Magnus1982 is offline   Reply With Quote
Old 08-12-2018, 11:42 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,937
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Counting unique records and unique with condition

I would build an aggregate query on qryStat with a special column to determine ifit has a FAIL:

Code:
SELECT Color, SUM(Iif(Result='FAIL', 1,0) AS Fails
FROM qryStat
GROUP BY Color
That will give you a record for each unique color and denotes how many fails each has.
That will be the query that feeds your 2 text boxes. You will set their control sources to DCount functions (https://www.techonthenet.com/access/...ain/dcount.php).

txtBox1 will be a simple DCount using just the first 2 arguments.
txtBox2 will require you to use criteria to exclude those with a Fails field greater than 0

Give the above shot and post back any issues you encounter.
plog is online now   Reply With Quote
Old 08-12-2018, 01:38 PM   #3
Magnus1982
Newly Registered User
 
Join Date: Apr 2017
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Magnus1982 is on a distinguished road
Re: Counting unique records and unique with condition

Thank you . Perfect solution Once again tnx

Magnus1982 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Counting unique records using SQL daievans Modules & VBA 2 05-30-2013 02:14 PM
Counting Unique Records in a Report Moxioron Reports 2 08-23-2012 07:38 AM
Counting unique records leaster Queries 1 12-06-2011 01:33 PM
Counting unique records in a query Nutritionist Queries 4 06-05-2006 11:17 PM
Counting unique records Kila Reports 12 03-20-2003 09:34 AM




All times are GMT -8. The time now is 09:41 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World