Query for Totals

[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 three-column table (created from a larger table with other data). The three columns are Date, contactMethod, amount. A single day can have many contactMethods used to contact customers to record their orders.

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 And total amount recorded from each contact method.


The table looks like:


Date contactMethod Amount
1-Jun-07 phone
1-Jun-07 Phone $20.00
4-Jun-07 Mail
4-Jun-07 fax
4-Jun-07 phone
4-Jun-07 Phone
5-Jun-07 Email $14.00
5-Jun-07 fax
5-Jun-07 phone
6-Jun-07 email
6-Jun-07 phone $30.00
7-Jun-07 Mail
7-Jun-07 phone
8-Jun-07 Mail $11.00
11-Jun-07 phone
13-Jun-07 phone
14-Jun-07 phone
18-Jun-07 phone
19-Jun-07 Mail $19.00
19-Jun-07 email
19-Jun-07 phone
20-Jun-07 Mail
20-Jun-07 email $16.00
20-Jun-07 phone
21-Jun-07 Mail
21-Jun-07 email
21-Jun-07 phone $25.00
22-Jun-07 Mail
22-Jun-07 email
22-Jun-07 phone
22-Jun-07 Phone
25-Jun-07 Mail $35.00
25-Jun-07 phone
26-Jun-07 Mail
26-Jun-07 email
26-Jun-07 phone $15.00
27-Jun-07 Mail
27-Jun-07 Email
27-Jun-07 phone
28-Jun-07 Mail $15.00
28-Jun-07 Email
28-Jun-07 Mail
28-Jun-07 phone $12.00
28-Jun-07 Phone $28.00
29-Jun-07 Mail $17.00
29-Jun-07 email $10.00
29-Jun-07 phone $23.00


Query Result:

Phone Fax Email Mail Total
Jun07 20 2 9 13 44
Total Amount 153 97 40 290

Thanks for your help,

mrkshpntf

 

[jrbarnett]

Use a crosstab query wizard based on the contactmethod field and the month(datefield) to produce the month column.

John

 

[randy700]

Something like...

"SELECT Count(ContactMethod) AS MethodCount, ContactMethod, Sum(Amount)
FROM [i][purple]yourtable[/purple][/i]
WHERE DateField BETWEEN #" & startDate & "# and #" & endDate & "# 
GROUP BY ContactMethod"

Randy