count number of entries 1

[ronwmon]

I create an Access database from a coma delimited file that I receive from our long distacne provider. One of the files is titled "toll free detail."

We get this file on a monthly basis. When I download this months file I import it into my database. I have added a field titled "Month" to identify the month we received the file. This makes it easy to look at data from one bill versus another.

The table created from this file contains thousands of records. Each record contains amoung others, the date, time, toll free number called, originating number, call duration and cost.

We use about 400 toll free numbers. Generally each toll free number gets several calls during the month. In some cases a toll free number will get thousands of calls.

I need to find a way to count the number of calls that an individual toll free number gets from each originating number. For example if one of the toll free numbers was 800-123-4567, I need to look at a single month and create a report that will show me how many calls came to this number from each of the originating numbers. It would be nice to sort this based on the highest number of calls first.

In an effort to solve this I created another table titled "t-dist" that has just 2 fields. The first field is the distinct toll free number from the main table, the second is null and titled "count." I planed to use this table that is basically a list of our toll free numbers, join it to the main table and count based on the sub table.

I am open to what ever I need to do to accomplish this task. If you have any ideas I thank you in advance.

Later,

Ron--

 

[dhookom]

Have you tried using a simple totals query like:

SELECT [TollFreeNumber], Month(TheDate) as Mth, Year(TheDate) as Year, Count(*) as NumOfCalls
FROM TableImported
GROUP BY [TollFreeNumber], Month(TheDate), Year(TheDate);

Duane
Hook'D on Access
MS Access MVP

 

[ronwmon]

Thanks for the great idea. It worked great.

Ron--