How do I count different fields in Report Sections

[scottpw]

I have a section that contains employees names, then beneath each name, I
list the each employee's details. Example:

Employee 1
Detail 1
Detail 2
Detail 3

Total Details: 3

Employee 2
Detail 1
Detail 2

Total Details: 2

Employee 3
Detail 1
Detail 2
Detail 3

Total Details: 3

Total Employees: 8

I want the "Total Employees" count to equal 3 employees instead of the 8
details.

Thanks,

Scottpw

 

[Larry Linson]

Two ways to do what you want:

(1) Add a Control named txtEmployeeCount to the Group Header with Control
Source of 1, and Running Sum set to "Over All" (set its Visible property to
No). Then, in the Report Footer, place a TextBox Control with a Control
Source of "= txtEmployeeCount"

OR

(2) Recreate the Report with a main Report using the Employee table as its
Record Source, and a Detail Report embedded in a Subform for showing
Employee Details. Using the Count function on Employee ID in the Report
footer should give you the count you want.

Larry Linson
Microsoft Access MVP

 

[scottpw]

Larry,

Thank you for your quick response.
I tried option 1 earlier today and acquired a "Enter Parameter Value" dialog
with a title of "1".

Unfortunately, I am not familiar with the process to apply option 2. I am
unable to find it in my Access Bible Book.

Is there any guidance on this process you can point me to; or, is it simple
enough with some direction?

Thanks again,

Scotty

Two ways to do what you want:

(1) Add a Control named txtEmployeeCount to the Group Header with Control
Source of 1, and Running Sum set to "Over All" (set its Visible property to
No). Then, in the Report Footer, place a TextBox Control with a Control
Source of "= txtEmployeeCount"

OR

(2) Recreate the Report with a main Report using the Employee table as its
Record Source, and a Detail Report embedded in a Subform for showing
Employee Details. Using the Count function on Employee ID in the Report
footer should give you the count you want.

Larry Linson
Microsoft Access MVP

I have a section that contains employees names, then beneath each name, I
list the each employee's details. Example:

[quoted text clipped - 27 lines]

Scottpw

 

[Duane Hookom]

The control source in option 1 should be:
=1

--
Duane Hookom
MS Access MVP

Larry,

Thank you for your quick response.
I tried option 1 earlier today and acquired a "Enter Parameter Value"
dialog
with a title of "1".

Unfortunately, I am not familiar with the process to apply option 2. I am
unable to find it in my Access Bible Book.

Is there any guidance on this process you can point me to; or, is it
simple
enough with some direction?

Thanks again,

Scotty

Two ways to do what you want:

(1) Add a Control named txtEmployeeCount to the Group Header with Control
Source of 1, and Running Sum set to "Over All" (set its Visible property
to
No). Then, in the Report Footer, place a TextBox Control with a Control
Source of "= txtEmployeeCount"

OR

(2) Recreate the Report with a main Report using the Employee table as
its
Record Source, and a Detail Report embedded in a Subform for showing
Employee Details. Using the Count function on Employee ID in the Report
footer should give you the count you want.

Larry Linson
Microsoft Access MVP

I have a section that contains employees names, then beneath each name, I
list the each employee's details. Example:

[quoted text clipped - 27 lines]

Scottpw

 

[Larry Linson]

Thank you for your quick response.
I tried option 1 earlier today and acquired a "Enter
Parameter Value" dialog with a title of "1".

My apology, as I should have said "with Control Source of =1". Try that
again. I just did it to verify my suggestion, and it worked nicely for me.

Unfortunately, I am not familiar with the process to
apply option 2. I am unable to find it in my Access
Bible Book.

Is there any guidance on this process you can point
me to; or, is it simple enough with some direction?

I don't have, or use, the Access Bible... last copy I owned was back in
Access 2.0 days. But I can't imagine it not including _something_ about
"Subreport".

But, also, I was dismayed to discover how difficult it is to find
"subreport" in Access 2002 Help, but it is there -- click the Contents tab
on Help, then click the + beside "Reports and Report Snapshots", then click
the + beside "Advanced Reports", then click the + beside "Subreports", and
you'll see two topics "About subreports" and "Create a subreport".

It's much easier in Access 2003 "online-primary" Help, if connected to the
Internet -- search on "subreport" and the first topic that appears is
"Create a subreport".

But, it _is_ simple. Create a Report for the details, at least a little
narrower than the main Employee Report (whose RecordSource now contains only
Employee Data not Employee joined with Employee Detail). Don't bother with
Report Header or Report Footer because they won't show, anyway. Then save
the Detail Report.

Open the main Report, and look in the Toolbox (which is, by default, on the
left of the page in Report design view), make sure the "magic wand" is
selected so you'll see the Wizards for Controls that have them. Move down
past the Tab Control button to Subform/Subreport, click the Control, and
drop it on the Report in approximately the position that you want it (you
can move it later if it is not exactlly where you want it). Follow the
prompts in the Wizard, one of which will let you choose the "Source
Object" -- choose your detail Report, just previously saved. Check to see
that the Wizard used the common field (probably Employee ID) in the
MasterLinkFields and ChildLinkFields properties to synchronize the main and
sub-reports.

Larry Linson
Microsoft Access MVP

 

[scottpw]

Thanks Duane,

I had just figured this one out with the "=" sign and was about to respond
back to Larry. You confirmed it.

I want to thank both you and Larry for getting me through this one. I really
appreciate your inputs.

Scotty

The control source in option 1 should be:
=1

Larry,

[quoted text clipped - 38 lines]

 

[Guest]

Hi
I tried your 2 options solutions but it didn't work.
it only counts the number of employees.
What I want is to sum the NbFam field: for example,
- the 1st employee has 5 nbfam
- the 2nd employee has 2 nbfam
- the total must be 7
In your solution it gave me 2
Note: In crystal report I didn't have this problem
Thank you