Counting Values in Continuous Form (1 Viewer)

mreference

Registered User.
Local time
Today, 02:23
Joined
Oct 4, 2010
Messages
137
How can I set up a count of records in my form footer that only counts a value once.

For example.

OrderNo Code

1 Hot
1 Hot
1 Cold
2 Cold
3 Cold
4 Hot
4 Cold

I want to count the number of unique [OrderNo], the result I am after would be 4.

Then I want to count,
Code:
 orders
a. how many Hot unique orders there are = 2 
b. how many Cold unique orders there are = 4 

Is that possible in a form footer?

I only ask as we need to see the entire list of orders in the continuous form.
 

vba_php

Forum Troll
Local time
Yesterday, 21:23
Joined
Oct 6, 2019
Messages
2,884
How can I set up a count of records in my form footer that only counts a value once.

For example.

OrderNo Code

1 Hot
1 Hot
1 Cold
2 Cold
3 Cold
4 Hot
4 Cold

I want to count the number of unique [OrderNo], the result I am after would be 4.
how about this?
Code:
1) create query object.  sql = "select distinct table.code from table"
2) on the control source property of the textbox in the form header:
Code:
=dcount("code", "queryNameHere")
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:23
Joined
Feb 19, 2002
Messages
42,981
To produce this count, you need two levels of summation. One that groups away the duplicates and the second that counts the results. This can be done with nested queries (my personal preference) or with a subquery within a query (Access does not optimize these well so this method might be slower if the recordset is large).

Then put a subform bound to the query in the form's footer. Hard-coding the selection criteria is not recommended since if you ever change the values, you will also need to change this code and the form. This is Excel thinking rather than Access thinking. NEVER hard-code anything unless you actually need to run different code for different values. This is not one of those cases.
 

Users who are viewing this thread

Top Bottom