Dsum and Dcount Functions in a report 2

[kastaman]

Hi there,

Firstly, my table consists of:
Option line:
Mainline
Data Centre

Each line would have a record pertaining to the type of calls they've managed:
Live
Voice Mail
Fax
EMail

Now I need to create a report where each "Option Line's" calls are summed up. Then I would create a calculated query to determine the Live %, # of operator per "Line Option"

I was hoping my report would reflect for each Line option like:
Data Centre:
Live = 5
Voice Mail = 5
Fax = 5
EMail = 5
Total Calls = 20
Live % = ([Total Calls]-/Total Calls]] or 75%


I hope I haven't confused you.

Thanks in advance.

 

[pdldavis]

Hi, a simple Dcount example might be something like this:

=DCount("[live]","tblYourtable","[date] = date()&quot

where you are counting the number of 'Live' calls from YourTable where the date = today's date. This can be done using an unbound text box in your report for each field.
Live% would be placed in the report footer :

=([Total Calls]-)/[TotalCalls]

Hope that helps.

 

[kastaman]

Thanks for the tip.

Kastaman Thanks in advance,

Kastaman

 

[kastaman]

pdldavis,

your example helps in counting the "live" calls but how can I segregate the live call count between the 2 line options?

Kastaman Thanks in advance,

Kastaman

 

[pdldavis]

Hi, I'm afraid I don't quite follow what you mean about segregating the two line options which I assume are the live and voice mail calls. Could you elaborate a bit? The original question posed had three options seperate from the e-mail.

 

[kastaman]

OK, we have 2 departments 1) Mainline 2) Data Centre.
The operators from each department logs in which channel (fax, e-mail, live or voicemail) the customer uses to contact their department.

I need to be able to count the volume by department the volumes for Total calls and E-mail for example:

Data Centre:
Live 10
Vmail 10
E-mail 10
Total 30
Live % = 66%

Mainline
Live 100
vmail 50
Live % = 50%

Thanks again.
Kastaman Thanks in advance,

Kastaman

 

[pdldavis]

Ok, this might help. This assumes that Data Centre is in a field called [type] and you wish to make the date selectable, based on a control in a report- a text box, for example:

=DCount("[live]","tblYourtable","([date] = '" & [txtDate] & "' And [type] = '" & "Data Centre" & "')&quot

-which counts all the live calls whose date matches the desired date from a control and the [type] = Data Centre.

Is that something more like what you had in mind?

 

[kastaman]

pdldavis,

That is exactly what I had in mind. Thanks a million!!

Kastaman Thanks in advance,

Kastaman

 

[kastaman]

One other thing is how can I calculate the live rate? IS it by naming the control let say DataLive and DataTotal then I can have another control=[DataLive]/[DataTotal] ? Thanks in advance,

Kastaman

 

[pdldavis]

Glad it worked. Sure, you can do something like that or you can do it all in one text box:

=DCount("[live]","tblYourtable","([date] = '" & [txtDate] & "' And [type]/DCount("[live]","tblYourtable","[date] = date()&quot= '" & "Data Centre" & "')"

(depends on the time frame you're using)

Helpful hint if you're not aware of it - Use Shift F2 to Zoom in on expressions that are too long to see on a single line. Found that one out myself only a couple months ago after b*$@g about the apparent limitation to a friend, who said "Why don't you just use Shift F2?"

 

[JerryDennison]

While you could use DCount() I recommend that you don't. Have you tried using a simple Totals query?

 

[kastaman]

JerryDenison,

Can you explain the totals query? I appreeciate all the feedback. Thanks in advance,

Kastaman

 

[JerryDennison]

A totals query allows you to use various aggregate functions along with grouping to give you summations of your dataset. Press the sum button on the query toolbar to get the totals property for each field. You will then have a list of the type of totals you wish to perform on each field.

 

[kastaman]

JerryDennison,
Thanks for your input. I'll try to incorporate this query to my report. Thanks in advance,

Kastaman