Countifs with unique values in access report

[FJMSalgueiro]

I am trying to create an expression to give me the count of PO + PO Line but I keep getting the wrong results. See some samples below in red.

Shipped Orders Query Count

PO PO Line Expr1

1	1	2
2225392	1	1
2225397	1	1 it should be 2
2225397	2	1
2225971	1	1 it should be 3
2225971	2	1
2225971	3	1
3060248	1	1 it should be 3
3060248	3	1
3060248	5	1
3060387	1	1
3060387	3	1 it should be 2
3061967	30	1
3061967	31	1
3061967	45	1 it should be 3
3062166	1	1
3062168	50	1
3062169	2	1
3062170	1	1
3062173	1	1
3062178	8	1
3062178	12	1 it should be 2
3062179	5	1
3062180	1	1
3062464	1	1
3062521	1	1
3062522	1	1
3062671	1	1
3062964	1	1
3063854	5	1
3063857	1	1
3063858	1	1
3063859	1	1
3063862	1	1
3063863	1	1
3063864	1	1
3063865	1	1
3063928	1	1
3063954	1	1
3063954	2	1
3063954	3	1
3063954	4	1
3063954	5	1
3063954	6	1
3063954	7	1 it should be 7
3063958	4	1
3063986	1	1
3064012	2	1
3064113	4	1
3064114	1	1
3064448	1	1
3065232	1	1
3065232	2	1 it should be 2

[Welshgasman]

Only 1 is red.
What should 2225971 be?

[FJMSalgueiro]

Please see the update table sample

[FJMSalgueiro]

Thanks, I update the sample table.

[Welshgasman]

Cross posted https://www.access-programmers.co.uk...report.329993/

[Welshgasman]

Try SUM() on your 1's?
However I think you would then get the same value for each PO which is the same?

[FJMSalgueiro]

It should be 3 - 2225971 - 1, 2225971 - 2, and 2225971 - 3

2225971	1	1 it should be 3
2225971	2	1
2225971	3	1

[FJMSalgueiro]

I try Sum() but it give the Sum of the entire table not the sum of each PO

[Welshgasman]

Set a group in the report on PO.
Or upload the dB with enough to see the issue. Reports are not my forte, and I have to work on them by trial and error.

[June7]

Have you designed report with a Grouping section on the PO number? Have you included Count() calculation in group header?

[FJMSalgueiro]

No I did not

[June7]

Perhaps you should try. That's how group aggregate calcs are accomplished in report.

[Bulzie]

In report if you group on PO, then in detail section add an unbound field for your count and on Data tab, for Control Source put 1, then under Running Sum select Over Group (can do Overall if you want it to count all records and then you can reference it at the end for a Grand total, etc.)

[June7]

Bulzie, can use RunningSum if there is a need to get a row number for detail records but it is not necessary for group count or even grand total count.