Group header needs count of distinct records

[JustinP]

I have a report grouped by ID with several fields in the detail
section. For each group header I need to provide a count of distinct
records for that group. So for example:

********Group Header*************
ID: 3

Number of Products: 3
Number of Product In Stock: 2

(****Detail****)
Product X 2005 In Stock
Product X 2006 In Stock
Product Y 2006 In Stock
Product Z 2006 Not In Stock

Is this possible?

 

[Allen Browne]

In the Sorting And Grouping dialog (View menu), enter another row below your
ID grouping:
ProductID

In the lower pane of the dialog, set Group Footer to Yes.
In the ProductID group footer, place a text box with these properties:
Control Source =IIf([InStock], 1,0)
Running Sum Over Group
Name txtInStockRS
Add another text box in the same section, with properties:
Control Source =IIf([InStock], 0,1)
Running Sum Over Group
Name txtNotInStockRS

In your ID footer, add 2 text box with Control Source of:
=[txtInStockRS]
and
=[txtNotInStockRS]

Once you have it working, you can set the Visible property of the ProductID
footer to No if you wish to hide it.

The example assumes InStock is a Yes/No field.
Adjust the expressions if you need something different.

 

[JustinP]

This is close to what I am after but in cases where there are duplicate
product from the different years, such as:

Product X 2005 In Stock
Product X 2006 In Stock

This comes out with two products even though it should on be one. There
are two queries:

Total of distinct products
Total of distinct products in stock

It appears this question has been asked a number of times on here over
the years and a solution has not been provided. If this isn't possible
please just say...

In the Sorting And Grouping dialog (View menu), enter another row below your
ID grouping:
ProductID

In the lower pane of the dialog, set Group Footer to Yes.
In the ProductID group footer, place a text box with these properties:
Control Source =IIf([InStock], 1,0)
Running Sum Over Group
Name txtInStockRS
Add another text box in the same section, with properties:
Control Source =IIf([InStock], 0,1)
Running Sum Over Group
Name txtNotInStockRS

In your ID footer, add 2 text box with Control Source of:
=[txtInStockRS]
and
=[txtNotInStockRS]

Once you have it working, you can set the Visible property of the ProductID
footer to No if you wish to hide it.

The example assumes InStock is a Yes/No field.
Adjust the expressions if you need something different.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report grouped by ID with several fields in the detail
section. For each group header I need to provide a count of distinct
records for that group. So for example:

********Group Header*************
ID: 3

Number of Products: 3
Number of Product In Stock: 2

(****Detail****)
Product X 2005 In Stock
Product X 2006 In Stock
Product Y 2006 In Stock
Product Z 2006 Not In Stock

Is this possible?

 

[JustinP]

Any takers?

This is close to what I am after but in cases where there are duplicate
product from the different years, such as:

Product X 2005 In Stock
Product X 2006 In Stock

This comes out with two products even though it should on be one. There
are two queries:

Total of distinct products
Total of distinct products in stock

It appears this question has been asked a number of times on here over
the years and a solution has not been provided. If this isn't possible
please just say...

In the Sorting And Grouping dialog (View menu), enter another row below your
ID grouping:
ProductID

In the lower pane of the dialog, set Group Footer to Yes.
In the ProductID group footer, place a text box with these properties:
Control Source =IIf([InStock], 1,0)
Running Sum Over Group
Name txtInStockRS
Add another text box in the same section, with properties:
Control Source =IIf([InStock], 0,1)
Running Sum Over Group
Name txtNotInStockRS

In your ID footer, add 2 text box with Control Source of:
=[txtInStockRS]
and
=[txtNotInStockRS]

Once you have it working, you can set the Visible property of the ProductID
footer to No if you wish to hide it.

The example assumes InStock is a Yes/No field.
Adjust the expressions if you need something different.