Gouping/counts 1

[kentwoodjean]

I have imported data from an excell report oflarge amounts of data. The data includes specific 800 numbers, the date of the calls, the time of the calls, and the state the calls originated from. I have grouped the 800 numbers an d have counted the number of calls bassed on time of calls which is a hidden field. Here is how I want the report to look like:

800-000-0000 Name
Month
#Calls from Michigan # Calls from out of State

Total Calls

Is there a way that I can separate the # of calls by instate or out of state? The table and query have one field for State From.

 

[PaulBricker]

The control sources for your textboxes would be
=Sum(IIf([State] = "MI",1,0))
for in state
=Sum(IIf([State] <> &quot;MI&quot;,1,0))
for out of state.
=Count([State])
for Total.

Paul

Hope I got the correct abbreviation for Michgan. I'm an East Coast boy without a map handy.

 

[kentwoodjean]

It works perfectly. I had the right idea but had the parenthesis slightly off.

One other question for the same report. This print out is set up by day (date) mm/dd/yyyy, and rather than listing the totals by each day, I need to have the calls for each # totaled by month. When I try to change the current date to month, it formats as a #. I am doing the following:

Month:Month([Date])

I would like it to spell the month out. Can I do that?

 

[PaulBricker]

I don't know how it's going to like the alias Month but try this

Month:Format(Month([Date])&quot;mmmm&quot

Paul

 

[kentwoodjean]

Well, I did try your suggestion, but couldn't make it work. AS you can see, I still have not grouped the different dates so they so up for the month only as 1 total in each of the categories (mich calls, out of state and total).

Is there anyway I can send you a copy of my database so you can take a look at it? I am creating it using only a small amount of data before I apply it to the full database. There is only one report at this time, and once I get this formated properly, I will be able to do the others for other time frames.

 

[randysmid]

Hi,
Did you know that you can do sorting and grouping by month, as long as the field in question is a date datatype? I wrote an FAQ on the topic of getting totals based on this topic. See faq703-2980 HTH,
Randy Smith
California Teachers Association

 

[PaulBricker]

Randy's FAQ will help you sort by Month. I'm sorry, I thought you just needed the month spelled out instead of a number. If you still have problems let me know.

Paul