Telephone Database Calc

[Turborekker]

Good Day,

I have a telephone database which I import a text file into, this contains all the calls made in our company month to month.

I require a calculation that allows me to list the numbers dialled, then next to each number it must tell me how many times that number has been dialled by different extensions.

Ie:

Number dialled / Times dialled by different ext

0215485896 / 1

0218963569 / 40

This would make it easier for me to tell whether the call was work related or private.

Thanks in advance

Shaun

[bcooney]

Create a summary field that equals the count of telephone number and then a sub-summary report by telephone number with the summary field in the sub-summary part.

[Turborekker]

Tks for your input - It mustn't be a report, it needs to be a calculation field...

[bcooney]

I don't think, then, that it can be done. Since each phone number exists in one or many records, you can create a calc in the table that would display the count for that phone number (using a self-join), but you will not be able to get a "report in a field."

edit: I may be taking what you're saying too literally. Perhaps you're saying that you want the count for each phone number to display without running a sub-summary report. That is what I describe above (self-join).

Edited November 3, 2010 by Guest

[Cabinetman]

Ditto ... using the self joining on a layout will allow you to go to layout to see who's calling who.

Or are you looking for something a little different? If so more detail/explanation is needed.....

[peterbuchanan]

Create a value list - showing all extensions dialled from each number. So create a self join relationship(number dialled), and the value list will use values in field "extension", but including only related values from the self join.

Then use Value Count to count the number of values in the value list.

So, a calculation field (number) will have

ValueCount (ValueListItems ("Database Name";"Value List" ))

This should result in the number of extensions that have dialled each number...

[Turborekker]

Excellent, I'll give this a go now...

Will let you know how it goes.

Tks for your help

[Turborekker]

It works perfectly

Tks PetertheGreat

[bcooney]

I really misunderstood this one. I realize upon rereading that you must have two tables: Extensions and Calls. You are reporting for each Extension record a count of unique Phone Numbers called (perhaps within a date range). Therefore, the Calls table must have at least the fields:

PhoneNumber

Date_Call

ExtensionID

[Turborekker]

@ bcooney - yes thats correct

[comment]

I really misunderstood this one.

You're not the only one - and I am still not sure I get it now.

The original question was:

how many times that number has been dialled by different extensions

The accepted solution answers a different question:

[color:blue]How many extensions dialed that number, ANY number of times (and unless the relationship is further filtered - at ANY time)?

Now, if there is a table of Extensions where each extension is a unique record, then the second question can be answered by defining the following relationships:

Calls::Number = Calls 2::Number

Calls 2::ExtensionID = Extensions 2::ExtensionID

and a calculation =

Count ( Extensions 2::ExtensionID )

[Turborekker]

Apologies, I was a bit vague in my question.

However tks to everyone for their input including Barbara and her date filter tip, and Peterthegreat

tks again

Edited November 4, 2010 by Guest