Getting totals for certain fields in Access 2002

  • Thread starter Thread starter Rolfee
  • Start date Start date
R

Rolfee

Hi.
I am having trouble working out how I can do something that I presumed
would be really simple. Having built a contact/telemarketing database
consisiting of a masterform and subform, and various tables and queries
- all of which have worked fine - I am now at the end of the 'project'
and need to run a report to get totals from a couple of fields. One
field is extremely straight forward, simply a CallCount field in which
I enter the number of times I have called a certain record. The other
field is a bit more complicated in that it refers to a table of
possible Outcomes (e.g. Appointment booked; New contact added;
Qualified Lead etc..).
The problem is that I cannot seem to work out how to total up these
fields. I have scoured websites, past posts in these user groups and
Access help, but to no avail. I left this (presumably) simply task 'til
the end (ie, when I have to submit the report, which is this week) and
I have run out of ideas and tenacity!

Can anyone tell me, please, how I can get a query/report that would
provide the totals I need in the following format;

CallCount Total 290
---------------------------------------------------------------------------------------------
Outcome Total
Appointment booked 15
New contact added 12
Qualified Lead 9


Thanks,

Ed
 
Create a totals query that groups by outcome and either counts or sums
CallCount. If you can't figure this out then come back with table and field
names.
 
Rolfee said:
Hi.
I am having trouble working out how I can do something that I presumed
would be really simple. Having built a contact/telemarketing database
consisiting of a masterform and subform, and various tables and queries
- all of which have worked fine - I am now at the end of the 'project'
and need to run a report to get totals from a couple of fields. One
field is extremely straight forward, simply a CallCount field in which
I enter the number of times I have called a certain record. The other
field is a bit more complicated in that it refers to a table of
possible Outcomes (e.g. Appointment booked; New contact added;
Qualified Lead etc..).
The problem is that I cannot seem to work out how to total up these
fields. I have scoured websites, past posts in these user groups and
Access help, but to no avail. I left this (presumably) simply task 'til
the end (ie, when I have to submit the report, which is this week) and
I have run out of ideas and tenacity!

Can anyone tell me, please, how I can get a query/report that would
provide the totals I need in the following format;

CallCount Total 290
---------------------------------------------------------------------------------------------
Outcome Total
Appointment booked 15
New contact added 12
Qualified Lead 9


Thanks,

Ed
Click to expand...

If call count is in the Main table and form and Outcome is in a sub
table andform do the following

Create a query similar to this that will join the two tables as
follows:

SELECT [CallCountTbl].[CallCount], [OutcomeTbl].[Outcome],
[OutcomeTbl].[total] FROM ([CallCountTbl] INNER JOIN [OutcomeTbl] ON
[CallCountTbl].[id] =[OutcomeTbl].[IDFK])

Create a report with a header for the call count total
Add the call count field to the call count header
Add the outcome and totals to the details
In the call count header and report footer create a field that shows
the following:
=Sum([total])+[CallCount]

Would look like this in report design view:

CallCount Header
Call Count Total [CallCount]

Detail
[Outcome] [Total]

CallCount Footer
Call Count Total [=Sum([total])+[CallCount]

Page Footer
[=Now()] [="Page " &
[Page] & " of " & [Pages]]

Report Footer
Grand Total [=Sum([total])+[CallCount]

Would print like this:

Call Count Total 290

Outcome Total
Appointment Booked 15
New Contact Added 12
Qualified Lead 9

Call Count Total 326

Grand Total 326
 
Back
Top