Access - Count records

[peterb]

I have a database with records consisting of phone numbers and date/time called.
I can write a query to tell me how many times each number is has been called but also want to know how many once, twice etc. there are.

ie 12 numbers called 1 time, 6 called 2 times, 17 called 3 times.

Help!

[CaptainD]

You did not post what you have done so far but nested SQL statements should work.

SELECT Count(*) AS NumberOfCalls, TimesCalled
FROM [SELECT testPhone.PhoneNumber, testPhone.dDate, Count(testPhone.PhoneNumber) AS TimesCalled
FROM testPhone
GROUP BY testPhone.PhoneNumber, testPhone.dDate]. AS TempTable Group By TimesCalled;
 

Note: the second Select statement in incased in "[]" marks to represent a table

[peterb]

You did not post what you have done so far but nested SQL statements should work.

SELECT Count(*) AS NumberOfCalls, TimesCalled
FROM [SELECT testPhone.PhoneNumber, testPhone.dDate, Count(testPhone.PhoneNumber) AS TimesCalled
FROM testPhone
GROUP BY testPhone.PhoneNumber, testPhone.dDate]. AS TempTable Group By TimesCalled;
 

Note: the second Select statement in incased in "[]" marks to represent a table

Thanks for this, it works - almost! The query is

SELECT Count(*) AS NumberOfNumbers, TimesCalled
FROM [SELECT cli, Count(*) AS TimesCalled
FROM 1290
GROUP BY cli]. AS TempTable
GROUP BY TimesCalled;

If all the phone numbers (cli) are different it shows 1 call for 16 numbers.

However if 2 are the same it shows

NumberOfNumbers TimesCalled
14 1
1 2

Any ideas?

[CaptainD]

Isn't that what you asked for?

ie 12 numbers called 1 time, 6 called 2 times, 17 called 3 times.

[peterb]

Yes, but this gives both sets of totals for Times Called and Calls.

Thanks for your help to get me started.

SELECT Count(*) AS NumberOfCallers, TempTable.TimesCalled, Count(*)*[TimesCalled] AS Calls
FROM [SELECT Field10, Count(*) AS TimesCalled
FROM 20060424
GROUP BY Field10]. AS TempTable
GROUP BY TempTable.TimesCalled;

Now I need to add the ability to ignore numbers with certain prefixes held in a table and select for last 1, 2 or 3 etc. days...

[CaptainD]

Correct in your query 14 numbers called once and 1 number called twice.

If you have any questions on filtering records just re-post.

Good Luck

[Widge]

I'm trying to do something similar except using VBA on a form but can't get to grips with Count(*) in VBA. I get a string with my SQL statement and what I am then trying to do is use DoCmd.RunSQL statement

This is popping an error saying that RunSQL is expecting a valid SQL statement. So obviously I'm having a formatting issue.

All I'm trying to do is count the amount of records that a search is coming back with for visual feedback purposes. Here is my gumf:

 
        CSQL = "SELECT Count(*) As Counted FROM AccountCardRegister, AccountCardComments"
        CSQL = CSQL & " WHERE AccountCardRegister.AccountCard LIKE '*" & Cardnumber & "*'"
        DoCmd.RunSQL CSQL
 
 

I was hoping to popuate a textbox with Counted, the result.

Also tried this without the 'As Counted' in the string and had no joy.

Once again, question-a-rama from me, but any help gratefully received!