Query on single field 1

[mrkshpntf]

I am trying to write a query on a single field(column).

The query is supposed to count 4 different types of contact methods Email, Phone, Fax and USPS mail used in a specified time interval.

The table is a simple two column table (created from a larger table with other data). The two columns are Date and contactMethod. A single day can have many contactMethods used to conact customers.

I want the query to count and total the number of each type of contact method used between a date range like days, weeks or month.

 

[PHV]

Something like this ?
SELECT contactMethod, Count(*) AS countOfContacts
FROM yourTable
WHERE [Date] Between [start date] And [End date]
GROUP BY contactMethod

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrkshpntf]

PHV

Thanks for your prompt response.

What I want to do is count and total the number of times the customers reps contact customers using each method.

This is an example for June:

Date contactMethod
01-Jun-07 phone
01-Jun-07 Phone
04-Jun-07 Mail
04-Jun-07 fax
04-Jun-07 phone
04-Jun-07 Phone
05-Jun-07 Email
05-Jun-07 fax
05-Jun-07 phone
06-Jun-07 email
06-Jun-07 phone
07-Jun-07 Mail
07-Jun-07 phone
08-Jun-07 Mail
11-Jun-07 phone
13-Jun-07 phone
14-Jun-07 phone
18-Jun-07 phone
19-Jun-07 Mail
19-Jun-07 email
19-Jun-07 phone
20-Jun-07 Mail
20-Jun-07 email
20-Jun-07 phone
21-Jun-07 Mail
21-Jun-07 email
21-Jun-07 phone
22-Jun-07 Mail
22-Jun-07 email
22-Jun-07 phone
22-Jun-07 Phone
25-Jun-07 Mail
25-Jun-07 phone
26-Jun-07 Mail
26-Jun-07 email
26-Jun-07 phone
27-Jun-07 Mail
27-Jun-07 Email
27-Jun-07 phone
28-Jun-07 Mail
28-Jun-07 Email
28-Jun-07 Mail
28-Jun-07 phone
28-Jun-07 Phone
29-Jun-07 Mail
29-Jun-07 email
29-Jun-07 phone

I want the query to give me the following result

Phone Fax Email Mail Total
Jun07 20 2 9 13 44


Your help is greatly appreciated.

Thanks,
mrkshpntf.

 

[PHV]

More like this ?
SELECT Format([Date},"mmmyy") AS [Month]
, Sum(IIf(contactMethod="phone",1,0)) AS Phone
, Sum(IIf(contactMethod="fax",1,0)) AS Fax
, Sum(IIf(contactMethod="email",1,0)) AS Email
, Sum(IIf(contactMethod="mail",1,0)) AS Mail
, Count(*) AS Total
FROM yourTable
WHERE yourCriteriasHere
GROUP BY Format([Date},"mmmyy")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrkshpntf]

PHV,

Thanks a lot!

Your solution worked perfectly!

I am sorry I didn't get to thank you earlier.

mrkshpntf.