totaling like records within a query

[bmcelroy]

I am a novice at Access and I have created a query to total statistical
information, I know how to create totals within a record, but I need to total
like records within a query as well. Specifically:

Facility Emp ID Total proceedures Date of proceedure

Branchville 00001 25 04/30/08
Branchville 00025 42 04/30/08
Edinburgh 00003 3 03/02/07
Mt. Pleasant 00001 23 04/29/08

I need to combine the two Branchville record total proceedures by date and be
able to total the proceedures by employee ID as well.

Thanks!

 

[Evi]

What display can you envision with your statistics below for the results you
are describing?
In a report, you could do this with a subreport (or subreports) in a report



Evi

 

[bmcelroy via AccessMonster.com]

Evi,
Thank you for responding! I would like to show the information 2 ways. One
for my Company, by employee to see performance, and one for the Client by
facility to rate performance.

First scenario

Facility Emp ID Total procedures Month
and Year

Branchville 00001 25
00025 42

Total Procedures Branchville 67
April 08

Edinburgh 00003 3

Total Procedures Edinburgh 3
March 07

Mt. Pleasant 00001 23

Total Procedures Mt. Pleasant 23
April 08


------------------------------------------------------------------------------
----------------------------------
Second scenario

Emp ID Facility Total Proceedures by month
and year

00001 Branchville 25
00001 Mt. Pleasant 23

Total Procedures Emp 00001 48 April
08

00025 Branchville 42


Total Procedures Emp 00025 42 April
08

00003 Edinburgh 3

Total Procedures Emp 00003 3 March
07


I know how to select date ranges to only pull specific records, so that's no
problem.

Thanks Again,
Becca

 

[Evi]

Then this is something you would do in a report with a subreport rather than
in a query.
In the first scenario using the Report's Sorting/Grouping button, you would
group first by Empoyee, choosing a Header and Footer for the Facility
Then Group by your date field.
In the bottom part of the SortingGrouping Box choose the Group Interval as
Year
Add the datefield again but this time choose Group Interval Month and choose
to have a Header and footer.
In the Group Footers you would put
=Sum([Total Procedures])

For the second section, create a report grouped by Employee and drag this
report into the desired section of the main report

Is this the sort of thing you had in mind?

Evi

Evi,
Thank you for responding! I would like to show the information 2 ways. One
for my Company, by employee to see performance, and one for the Client by
facility to rate performance.

First scenario

Facility Emp ID Total procedures Month
and Year

Branchville 00001 25
00025 42

Total Procedures Branchville 67
April 08

Edinburgh 00003 3

Total Procedures Edinburgh 3
March 07

Mt. Pleasant 00001 23

Total Procedures Mt. Pleasant 23
April 08


-------------------------------------------------------------------------- ----
----------------------------------
Second scenario

Emp ID Facility Total Proceedures by month
and year

00001 Branchville 25
00001 Mt. Pleasant 23

Total Procedures Emp 00001 48 April
08

00025 Branchville 42


Total Procedures Emp 00025 42 April
08

00003 Edinburgh 3

Total Procedures Emp 00003 3 March
07


I know how to select date ranges to only pull specific records, so that's no
problem.

Thanks Again,

Becca

What display can you envision with your statistics below for the results you
are describing?
In a report, you could do this with a subreport (or subreports) in a report

Evi

 

[bmcelroy via AccessMonster.com]

It sounds like it. I've never created a subreport, so I will have to try it
tomorrow. jWill the report do the totaling for me by facility and employee
or will I need to write code to make it happen?

Thanks Again!
Becca

Then this is something you would do in a report with a subreport rather than
in a query.
In the first scenario using the Report's Sorting/Grouping button, you would
group first by Empoyee, choosing a Header and Footer for the Facility
Then Group by your date field.
In the bottom part of the SortingGrouping Box choose the Group Interval as
Year
Add the datefield again but this time choose Group Interval Month and choose
to have a Header and footer.
In the Group Footers you would put
=Sum([Total Procedures])

For the second section, create a report grouped by Employee and drag this
report into the desired section of the main report

Is this the sort of thing you had in mind?

Evi

Evi,
Thank you for responding! I would like to show the information 2 ways. One

[quoted text clipped - 49 lines]

Thanks Again,

Becca

What display can you envision with your statistics below for the results you
are describing?
In a report, you could do this with a subreport (or subreports) in a report

Evi

I am a novice at Access and I have created a query to total statistical
information, I know how to create totals within a record, but I need to total
like records within a query as well. Specifically:

[quoted text clipped - 6 lines]

I need to combine the two Branchville record total proceedures by date and be
able to total the proceedures by employee ID as well.

 

[Evi]

you can total stuff with = Sum([Your Field]) or = Count([YourField]) text
boxes in your subreport in group footers in the same way as you do in your
main report.
You can either display the overall total in the subreport in an 'artificial'
group footer (I'll explain if you want to do this). Or you can put it in
your subreport's footer and refer to that total in your main report using

=[NameOfYourSubreport].Report.[NameOfTextBoxThatHasTheTotal]

A simple way of creating a subreport is to create a normal report but delete
all the header and footer stuff.

Open your main report in Design View and slide the subreport into perhaps
the main report's Report Header or Report footer -whichever looks best.

That would create an Unlinked report.



Evi

It sounds like it. I've never created a subreport, so I will have to try it
tomorrow. jWill the report do the totaling for me by facility and employee
or will I need to write code to make it happen?

Thanks Again!
Becca

Then this is something you would do in a report with a subreport rather than
in a query.
In the first scenario using the Report's Sorting/Grouping button, you would
group first by Empoyee, choosing a Header and Footer for the Facility
Then Group by your date field.
In the bottom part of the SortingGrouping Box choose the Group Interval as
Year
Add the datefield again but this time choose Group Interval Month and choose
to have a Header and footer.
In the Group Footers you would put
=Sum([Total Procedures])

For the second section, create a report grouped by Employee and drag this
report into the desired section of the main report

Is this the sort of thing you had in mind?

Evi

Evi,
Thank you for responding! I would like to show the information 2 ways.

One
[quoted text clipped - 49 lines]

Thanks Again,

Becca

What display can you envision with your statistics below for the results you
are describing?
In a report, you could do this with a subreport (or subreports) in a report

Evi

I am a novice at Access and I have created a query to total statistical
information, I know how to create totals within a record, but I need to total
like records within a query as well. Specifically:

[quoted text clipped - 6 lines]

I need to combine the two Branchville record total proceedures by date

and
be

able to total the proceedures by employee ID as well.