Counting Values in Continuous Form

[mreference]

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,

 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]

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?

1) create query object.  sql = "select distinct table.code from table"

2) on the control source property of the textbox in the form header:

=dcount("code", "queryNameHere")

 

[Pat Hartman]

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.