Count of distinct values in report

[JustinP]

How do you sum the distinct values based on a field in a report? I'm
guessing you do it in the record source query but is it possible to do
a count of a select query within a select query? Is there an easier way?

 

[Guest]

Hi Justin,

One of the easiest methods is to group on the field in question. In report
design view, click on View > Sorting and Grouping. Select the field in
question with Group Header and Group Footer both set to Yes. Drag the field
in question from the field list to the Group Header. Add a textbox to the
Group Footer that has a control source that sums the appropriate numeric data
type. As an example, take a look at the Invoice report in the sample
Northwind database. Here, they are grouping on the OrderID field. The Group
Footer includes a textbox named InvoiceSubtotal that has a control source:
=Sum([ExtendedPrice])

Notes:
1.) You must include the name of a field, not the control, when using the
Sum function.

2.) Make sure that the name of the control is not the same as the control
source.


Tom Wickerath
Microsoft Access MVP

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

 

[JustinP]

Hi Tom,

That pretty much covers what I'm wanting but I'm needing the text box
to be in the report header, how can I do this?

Hi Justin,

One of the easiest methods is to group on the field in question. In report
design view, click on View > Sorting and Grouping. Select the field in
question with Group Header and Group Footer both set to Yes. Drag the field
in question from the field list to the Group Header. Add a textbox to the
Group Footer that has a control source that sums the appropriate numeric data
type. As an example, take a look at the Invoice report in the sample
Northwind database. Here, they are grouping on the OrderID field. The Group
Footer includes a textbox named InvoiceSubtotal that has a control source:
=Sum([ExtendedPrice])

Notes:
1.) You must include the name of a field, not the control, when using the
Sum function.

2.) Make sure that the name of the control is not the same as the control
source.


Tom Wickerath
Microsoft Access MVP

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

How do you sum the distinct values based on a field in a report? I'm
guessing you do it in the record source query but is it possible to do
a count of a select query within a select query? Is there an easier way?

 

[Guest]

Hi Justin,

Using the Invoices report in the Northwind database, in report design view
click on View > Report Header/Footer to view the report header. Add a new
textbox with the following control source: =Sum([ExtendedPrice])

Just make sure that the text box is not named "ExtendedPrice". You can set
the height for the report footer to zero to collapse this section.


Tom Wickerath
Microsoft Access MVP

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

 

[JustinP]

Sorry for being unclear. For the Northwind database, the sort of
question I am asking is: give me the sum of distinct products (in the
report header). Any ideas?

Hi Justin,

Using the Invoices report in the Northwind database, in report design view
click on View > Report Header/Footer to view the report header. Add a new
textbox with the following control source: =Sum([ExtendedPrice])

Just make sure that the text box is not named "ExtendedPrice". You can set
the height for the report footer to zero to collapse this section.


Tom Wickerath
Microsoft Access MVP

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

Hi Tom,

That pretty much covers what I'm wanting but I'm needing the text box
to be in the report header, how can I do this?

 

[Guest]

Hi Justin,

Are you talking one textbox with a sum? If so, perhaps you should check out
the DSum domain aggregrate function, with the appropriate criteria applied to
select the distinct products that you have in mind. Here is information on
using DLookup; DSum is used in a very similar way:

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm


Note: TableName can also be a saved query in the examples given.

If you are looking for more than a single text box, perhaps this KB article
will provide some ideas:

How to Create a Table of Contents or Index for a Report
http://support.microsoft.com/kb/210269/


Tom Wickerath
Microsoft Access MVP

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