Creating summary records

[Rey]

Have a table imported from Excel:
Fields in table CallProductivity
CallerName Text
CertificateID Number
Priority Text
EventName Text
CallDateTime Date/Time
ContactStatus Text
Subject Text
Notes Text


The ContactStatus field contains the following entries.
Row Labels Count Left msg No Contact RPC
Call Made - Needs Bilingual 37 N
Call made - no contact 1183 N
Call Made - Other 62 N
Call Made - Refused Assistance 38 R
Call Made - Updated Information 83 R
Counseling Provided 68 R
Identified to call 2389
Left Message 2159 M
Mail Sent 19
Needs Skip work 280
No Contact 317 N
Not Attended 10
Proposal to Servicer 11
Referral in progress 699 R
Referral to PMI 71
Referred to Servicer 6
Refused Message 6 N
Review Note 59
RSVP- May Attend 111 R
RSVP- Will Attend 85 R
RSVP- Will NOT Attend 508 R
Sent Email 11
Working with Servicer 174 R

User wants to have a report depicting following. Month to date comes
from above.
record Yesterday Month to date
Left Message 10 2159
No contact 5 1605
RPC 5 1766

The No contact summary row combines all the N rows (Call Made - Needs
Bilingual,Call made - no contact) while the RPC summary row combines
all the R rows.

My question is:
Short of creating individual queries w/results inserted into a temp
table whose combined totals are appended to a final table via a VBA
code block is it possible to do this with an Access 2007 query?

I'm also encountering an issue in Access 2007 where when I filter the
table in the ContactStatus field for call made - no contact I get 1276
rows of 9169. I can manually count 20 records containing 29-Apr-09
from three diffent callernames. But when I filter for CallDateTime 29-
Apr-09 I get one row.
I then filter the CallDateTime field for 4/29/2009 and I have one row.
Possibly because of time differences w/i each calldatetime entry.

Thank you in advance for your suggestions/comments,

Rey

 

[KARL DEWEY]

I suggest you add separate fields for all the different data items in
ContactStatus.

You say 'manually count 20 records containing 29-Apr-09 --
filter for CallDateTime 29-Apr-09 I get one row --
filter the CallDateTime field for 4/29/2009 and I have one row.
How do you 'filter' the data? Are you using criteria?

 

[Rey]

I suggest you add separate fields for all the different data items in
ContactStatus.

You say 'manually count 20 records containing 29-Apr-09 --
            filter for CallDateTime 29-Apr-09 I get one row  --
            filter the CallDateTime field for 4/29/2009 and Ihave one row.
How do you 'filter'  the data?    Are you using criteria?

Howdy Karl.
Thanks for replying.

Access 2007 has an Excel like autofilter on the field headers. Don't
know if you can turn this off but will have to research.

Anyway, I select the date through this feature and get one record
instead of three or more (not at work) so will have to check datetime
for each record.

Though believe that via an SQL query statement using where
CallDateTime = #4/29/2009# I get a blank row.

Adding a field for each data item goes against table normalization I
believe. I can group the ContactStatus field by the individual entries
and get their count, issue is trying to summarize them.

So will do some more research and read my Access 2002 desktop and
Enterprise books by Allison Balter.

Thanks again,

Rey

 

[KARL DEWEY]

What do you get with --
DatePart([CallDateTime]) = #4/29/2009#

 

[Rey]

What do you get with --
   DatePart([CallDateTime]) = #4/29/2009#

Sorry for delay in responding.

Results for DatePart:
Priority EventName CallDatetime theDatePart
Low Castor: Tampa 27-Apr-09 27

query:
SELECT CallProductivity.CallerName, CallProductivity.Priority,
CallProductivity.EventName, CallProductivity.CallDatetime, DatePart
('d', CallProductivity.CallDatetime) AS theDatePart
FROM CallProductivity
WHERE (((CallProductivity.CallerName)="Steven Zukerrman") AND
((CallProductivity.Priority)="Low") AND ((CallProductivity.EventName)
="Castor: Tampa"))
and CallProductivity.CallDatetime between #4/27/2009# and #4/28/2009#;

Had to use BETWEEN else would get nothing returned. Seems like
datetime field is responding like SQL Server.

Thus far, I've basically created interim queries for each summary
record, i.e. No contact, inserting the query results into a temp table
(ResultHolding) that I would then summarize into the final table from
which report/dashboard will draw from.

This is the bilingual yesterday query:
INSERT INTO ResultHolding ( ContactStatus, Yesterday, MTD )
SELECT CP.ContactStatus, Count(*) AS Expr1, 0 AS Expr2
FROM CallProductivity AS CP
WHERE (((CP.CallDateTime)>=(Date()-1) And (CP.CallDateTime)<Date())
AND ((CP.ContactStatus)="Call Made - Needs Bilingual") AND
((CP.CallerName) In (SELECT ME.CallerName FROM MXEmployees ME)))
GROUP BY CP.ContactStatus;

This is the bilingual MTD (month to date) query:
INSERT INTO ResultHolding ( ContactStatus, Yesterday, MTD )
SELECT CP.ContactStatus, 0 AS Yesterday, count(*) AS MTD
FROM CallProductivity AS CP
WHERE "(cp.CallDateTime >= #" & Month(Date() & '/1/' & Year(Date()) &
"#" and cp.CallDateTime <= Date())
AND
CP.ContactStatus="Call Made - Needs Bilingual"
AND
(CP.CallerName In (SELECT ME.CallerName FROM MXEmployees ME))
GROUP BY contactstatus;


ResultHolding table records after interim queries:
ContactStatus Yesterday MTD
Call Made - Needs Bilingual 5 0
Call Made - Needs Bilingual 0 47
Call made - no contact 38 0
Call made - no contact 0 1271
Call Made - Other 0 51
No Contact 0 215
Refused Message 0 6


Finally the query that summarizes the above records:

INSERT INTO ProductivityResults ( ContactStatus, Yesterday, MTD )
SELECT "No Contact" AS ContactStatus, Sum(RH.Yesterday) AS Yesterday,
Sum(RH.MTD) AS MTD
FROM ResultHolding AS RH;

Resulting in:
ContactStatus Yesterday MTD
No Contact 43 1590

Now to put together the VBA that will clear the various tables, insert
and summarize the data.

Then see how to place 6 table frames into one report. Gotta read up on
subreports...

As user is accustomed to "dashboard" reports generated from SQL Server
Reporting Services will try to "approximate" a similar report.

This effort will subsequently be replaced by a "real database" per
management once user puts together the project proposal...

Thanks again for replying. Happy to entertain any other suggestions
you may have on matter.

Rey