How to Count how many different Ordernr´s in a report 2

[frontside]

My report gives me every ordernumber in a row like this:
1201
1201
1201
1205
1205
1301

Doing a count of these gives me 6 values, I need the answer to be 3 (as in three different values)....


Let me know if you have any ideas or wants some more info of my problem.

Thanks for all the help

Mikael (Sweden)

 

[jason246day]

SELECT COUNT(*)
FROM (SELECT DISTINCT ordernumber FROM ordernumbers_table);

I believe this should work for you.

 

[dhookom]

Create a group header for OrderNumber. Add a text box in the Group Header:
Name: txtONCount
Control Source:=1
Running Sum: Over All
Then add a text box in your report footer
Control Source: =txtONCount

The previous suggestion may count all distinct ordernumbers in you table regardless of whether they are in your report or not.

Duane
MS Access MVP

 

[frontside]

I tried the last suggestion but doesnt quite get it to work. I keep getting the sum of all the numbers....

Isn´t there a way of counting how many headers there are and then display this number in a footer....

a counter of distinct numbers would be nice here

any more suggestions

regards
Mikael

 

[frontside]

Could it be solved if I could compare one ordernr with the one before? that way I could make a counter that gives a new value a 1 and a 0 if the last ordernr is the same..
Like
1201 1
1201 0
1201 0
1205 1
1205 0
1301 1

That way I can get the result of 3 as I was looking for! Does anyone have a good solution for this problem or am I far out there?

Mike (Sweden)

 

[dhookom]

It has always worked for me as I suggested. Do you have an OrderNumber group header? Did you place the text box in the group header? This is a fairly simple solution and beats the heck out of trying to write code.

Duane
MS Access MVP

 

[frontside]

I have done a small .mdb file that shows what I´m trying to to.... I guess there is something that I havent done right.

Feel free to download it and try,

http://home.no/frontside900/problems.htm

Micke

 

[dhookom]

You had the control source of Txtoncount set to 1 rather than =1. You also forgot the "=" in the control source of =txtOnCount. I moved your text box Text20 to the Date Footer and the value displayed a 3. You may need to change the Running Sum on txtOnCount to "Over Group" if you have multiple dates.

Thanks for providing your small sample on a web site for download. It made trouble-shooting very easy.

Duane
MS Access MVP

Duane
MS Access MVP

 

[Aerowolf]

This works great for me too...except that I want to reset the counter for each page. As it is now the running sum goes over the whole report. I'd like a count of unique store #s on each page of my report.

Any suggestions?

Edwin

 

[dhookom]

This would probably require some code combined with trial and error (at least for me). I don't have time to find a solution so you are best of beginning a new thread with this question.

Duane
MS Access MVP

 

[topwaya]

This works great:

Create a group header for OrderNumber. Add a text box in the Group Header:
   Name: txtONCount
   Control Source:=1
   Running Sum: Over All
Then add a text box in your report footer
   Control Source: =txtONCount

is there anyway to make it work with the text box in the Report Header instead of the Footer? I can't figure it out.
Thanks!