Count of distinct records for each group header

J

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

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...
 
W

Wayne-I-M

Hi Justin

Yes most things can be counted in a report as that one of the main reason
reports are design.

To count the occurances of a field you can use
=Count([FieldName]) as the control source of an unbound box in the group
header (or foooter)

To count if a prodoct is "In-Stock" you can use an IIF Count
=Count(IIf([FieldName]= "In-Stock",[FieldName])

To count the number of distinct products there are a number of methods -
prob the easiest would be to either create a count within the query the
report is based on and then include the field in the report or to group on
distinct and count the distinct - in this case omit the detail section to
just provide the "results"

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.
 
J

JustinP

Thanks Wayne. Just need to get clarification on your suggestions. I do
need to have a count of both distinct products and a count of distinct
product in stock. Also, I am unable to omit the detail section as it
includes the most important part of the report (the report is not
actually about products). If I created a query with the counts in it,
is it possible to use a DLookup to get each value and put it in the
right group header? (the number of IDs the report is grouped on will
not be the same everytime the report is run).

Are there any other methods I could try?

Wayne-I-M said:
Hi Justin

Yes most things can be counted in a report as that one of the main reason
reports are design.

To count the occurances of a field you can use
=Count([FieldName]) as the control source of an unbound box in the group
header (or foooter)

To count if a prodoct is "In-Stock" you can use an IIF Count
=Count(IIf([FieldName]= "In-Stock",[FieldName])

To count the number of distinct products there are a number of methods -
prob the easiest would be to either create a count within the query the
report is based on and then include the field in the report or to group on
distinct and count the distinct - in this case omit the detail section to
just provide the "results"

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.


JustinP said:
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

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...
Click to expand...
Click to expand...
 
J

JustinP

Please someone help on this one!

Thanks Wayne. Just need to get clarification on your suggestions. I do
need to have a count of both distinct products and a count of distinct
product in stock. Also, I am unable to omit the detail section as it
includes the most important part of the report (the report is not
actually about products). If I created a query with the counts in it,
is it possible to use a DLookup to get each value and put it in the
right group header? (the number of IDs the report is grouped on will
not be the same everytime the report is run).

Are there any other methods I could try?

Wayne-I-M said:
Hi Justin

Yes most things can be counted in a report as that one of the main reason
reports are design.

To count the occurances of a field you can use
=Count([FieldName]) as the control source of an unbound box in the group
header (or foooter)

To count if a prodoct is "In-Stock" you can use an IIF Count
=Count(IIf([FieldName]= "In-Stock",[FieldName])

To count the number of distinct products there are a number of methods -
prob the easiest would be to either create a count within the query the
report is based on and then include the field in the report or to group on
distinct and count the distinct - in this case omit the detail section to
just provide the "results"

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.


JustinP said:
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

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...
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Wickerath

Justin,

You've showed us in your first post what your desired result is. If you
really want some help, take it a step further and detail exactly how your
data is shown in your tables. Include the names of the table(s) and fields,
along with the same data required to generate the result you showed in your
first post.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
J

JustinP

The data is confidential so I will used products as example data.

Example table:

ProductGroup Product YearProduced InStock

Toys Teddy 2005
No
Toys Doll 2005
Yes
Toys Doll 2006
Yes
Clothes Pants 2004
Yes
Clothes Shoes 2004
Yes
Clothes Shoes 2005
Yes


Expected report:


Product Group: Toys
(Group Header)

Number of products: 2
Number of products in stock: 1

Inventory:
(Detail)
Teddy 2005 No
Doll 2005 Yes
Doll 2006 Yes



Product Group: Food
(Group Header)

Number of products: 2
Number of products in stock: 2

Inventory:
(Detail)
Pants 2004 Yes
Shoes 2004 Yes
Shoes 2005 Yes

Can anyone help?
 
T

Tom Wickerath

Hi Justin,

Okay, I think I can help you, however, I question the desired results you
published for the data you provided! You included "Product Group: Food" in
the desired report, but no such product group in the data. And, I am confused
by your desired results for Number of Products in stock result. You indicated
this result for toys:

Number of products in stock: 1
Teddy 2005 No
Doll 2005 Yes
Doll 2006 Yes


Suppose the data had been this, instead (2006 value changed to No):
Teddy 2005 No
Doll 2005 Yes
Doll 2006 No

How many toys are in stock now? I'm thinking that you *might* have meant to
indicate that two items are in stock (as opposed to two products). Well, at
least that's the solution I have come up with for the present time.

Create the following two queries. Note: You did not indicate the name of
your table, so I used tblProducts as the name of the table:

qryProducts

SELECT tblProducts.ProductGroup, tblProducts.Product,
tblProducts.YearProduced, tblProducts.InStock,
IIf([InStock]="Yes",1,0) AS InStockCount
FROM tblProducts;

qryCount

SELECT tblProducts.ProductGroup, tblProducts.Product
FROM tblProducts
GROUP BY tblProducts.ProductGroup, tblProducts.Product;


Assign qryProducts as the record source for your report. In report design
view, use View > Sorting and Grouping... to group by ProductGroup. Pick Yes
for Group Header. Add the two text boxes with labels to the ProductGroup
header:

Label caption: Number of Products:
Text box control source:
=DCount("[Product]","[qryCount]","[ProductGroup]= '" & [ProductGroup] & "'")

Label caption: Number of Items in stock:
Text box control source: =Sum([InStockCount])



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
J

JustinP

That's is excellent Tom, exactly what I am after. Appreciate your help.
Is there somewhere where we can recommend MVPs?


Tom said:
Hi Justin,

Okay, I think I can help you, however, I question the desired results you
published for the data you provided! You included "Product Group: Food" in
the desired report, but no such product group in the data. And, I am confused
by your desired results for Number of Products in stock result. You indicated
this result for toys:

Number of products in stock: 1
Teddy 2005 No
Doll 2005 Yes
Doll 2006 Yes


Suppose the data had been this, instead (2006 value changed to No):
Teddy 2005 No
Doll 2005 Yes
Doll 2006 No

How many toys are in stock now? I'm thinking that you *might* have meant to
indicate that two items are in stock (as opposed to two products). Well, at
least that's the solution I have come up with for the present time.

Create the following two queries. Note: You did not indicate the name of
your table, so I used tblProducts as the name of the table:

qryProducts

SELECT tblProducts.ProductGroup, tblProducts.Product,
tblProducts.YearProduced, tblProducts.InStock,
IIf([InStock]="Yes",1,0) AS InStockCount
FROM tblProducts;

qryCount

SELECT tblProducts.ProductGroup, tblProducts.Product
FROM tblProducts
GROUP BY tblProducts.ProductGroup, tblProducts.Product;


Assign qryProducts as the record source for your report. In report design
view, use View > Sorting and Grouping... to group by ProductGroup. Pick Yes
for Group Header. Add the two text boxes with labels to the ProductGroup
header:

Label caption: Number of Products:
Text box control source:
=DCount("[Product]","[qryCount]","[ProductGroup]= '" & [ProductGroup] & "'")

Label caption: Number of Items in stock:
Text box control source: =Sum([InStockCount])



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

JustinP said:
The data is confidential so I will used products as example data.

Example table:

ProductGroup Product YearProduced InStock

Toys Teddy 2005
No
Toys Doll 2005
Yes
Toys Doll 2006
Yes
Clothes Pants 2004
Yes
Clothes Shoes 2004
Yes
Clothes Shoes 2005
Yes


Expected report:


Product Group: Toys
(Group Header)

Number of products: 2
Number of products in stock: 1

Inventory:
(Detail)
Teddy 2005 No
Doll 2005 Yes
Doll 2006 Yes



Product Group: Food
(Group Header)

Number of products: 2
Number of products in stock: 2

Inventory:
(Detail)
Pants 2004 Yes
Shoes 2004 Yes
Shoes 2005 Yes

Can anyone help?
Click to expand...
Click to expand...
 
D

deanna morrison

That's is excellent Tom, exactly what I am after. Appreciate your help.
Is there somewhere where we can recommend MVPs?


Tom said:
Hi Justin,

Okay, I think I can help you, however, I question the desired results you
published for the data you provided! You included "Product Group: Food" in
the desired report, but no such product group in the data. And, I am confused
by your desired results for Number of Products in stock result. You indicated
this result for toys:

Number of products in stock: 1
Teddy 2005 No
Doll 2005 Yes
Doll 2006 Yes


Suppose the data had been this, instead (2006 value changed to No):
Teddy 2005 No
Doll 2005 Yes
Doll 2006 No

How many toys are in stock now? I'm thinking that you *might* have meant to
indicate that two items are in stock (as opposed to two products). Well, at
least that's the solution I have come up with for the present time.

Create the following two queries. Note: You did not indicate the name of
your table, so I used tblProducts as the name of the table:

qryProducts

SELECT tblProducts.ProductGroup, tblProducts.Product,
tblProducts.YearProduced, tblProducts.InStock,
IIf([InStock]="Yes",1,0) AS InStockCount
FROM tblProducts;

qryCount

SELECT tblProducts.ProductGroup, tblProducts.Product
FROM tblProducts
GROUP BY tblProducts.ProductGroup, tblProducts.Product;


Assign qryProducts as the record source for your report. In report design
view, use View > Sorting and Grouping... to group by ProductGroup. Pick Yes
for Group Header. Add the two text boxes with labels to the ProductGroup
header:

Label caption: Number of Products:
Text box control source:
=DCount("[Product]","[qryCount]","[ProductGroup]= '" & [ProductGroup] & "'")

Label caption: Number of Items in stock:
Text box control source: =Sum([InStockCount])



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

JustinP said:
The data is confidential so I will used products as example data.

Example table:

ProductGroup Product YearProduced InStock

Toys Teddy 2005
No
Toys Doll 2005
Yes
Toys Doll 2006
Yes
Clothes Pants 2004
Yes
Clothes Shoes 2004
Yes
Clothes Shoes 2005
Yes


Expected report:


Product Group: Toys
(Group Header)

Number of products: 2
Number of products in stock: 1

Inventory:
(Detail)
Teddy 2005 No
Doll 2005 Yes
Doll 2006 Yes



Product Group: Food
(Group Header)

Number of products: 2
Number of products in stock: 2

Inventory:
(Detail)
Pants 2004 Yes
Shoes 2004 Yes
Shoes 2005 Yes

Can anyone help?


Tom Wickerath wrote:
Justin,

You've showed us in your first post what your desired result is. If you
really want some help, take it a step further and detail exactly how your
data is shown in your tables. Include the names of the table(s) and fields,
along with the same data required to generate the result you showed in your
first post.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Please someone help on this one!
Click to expand...
Click to expand...
Click to expand...
Hey my name is deanna09
 
T

Tom Wickerath

Hi Justin,

I'm glad to read that it worked out for you. Strangely enough, I just
received a notification "A Microsoft Community member has responded to the
thread "Count of distinct records for each group header", based on a post
from this morning; I had nto received any such notification for your last
reply, made 11/25 and shown below.
Is there somewhere where we can recommend MVPs?
Click to expand...
There are many very capable individuals who contribute to the newsgroup on a
regular basis, who are not an MVP. I was one of these people until April of
this year. I invite you to recommend this newsgroup, along with the other
related newsgroups (ie. microsoft.public.access.X, where X = Forms, Queries,
Reports, Security, etc.) to people in need of help. But I do not believe that
MVPs are the sole source of authoritative answers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Group header needs count of distinct records 0
Group header needs count of distinct records 3
Section and Page numbering 0
Access 2007 0
Distinct Count in Crosstab 1
Count of records for groups on a report 1
Report Group Footer DLookUp 0
Calc Sales based on Stock Count 3

Top