By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Access - Count records

P: 7
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!
Apr 27 '06 #1
Share this Question
Share on Google+
6 Replies


100+
P: 135
You did not post what you have done so far but nested SQL statements should work.
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS NumberOfCalls, TimesCalled
  2. FROM [SELECT testPhone.PhoneNumber, testPhone.dDate, Count(testPhone.PhoneNumber) AS TimesCalled
  3. FROM testPhone
  4. GROUP BY testPhone.PhoneNumber, testPhone.dDate]. AS TempTable Group By TimesCalled;
  5.  
Note: the second Select statement in incased in "[]" marks to represent a table
Apr 27 '06 #2

P: 7
You did not post what you have done so far but nested SQL statements should work.
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS NumberOfCalls, TimesCalled
  2. FROM [SELECT testPhone.PhoneNumber, testPhone.dDate, Count(testPhone.PhoneNumber) AS TimesCalled
  3. FROM testPhone
  4. GROUP BY testPhone.PhoneNumber, testPhone.dDate]. AS TempTable Group By TimesCalled;
  5.  
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?
Apr 28 '06 #3

100+
P: 135
Isn't that what you asked for?
ie 12 numbers called 1 time, 6 called 2 times, 17 called 3 times.
Apr 28 '06 #4

P: 7
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...
Apr 28 '06 #5

100+
P: 135
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
Apr 28 '06 #6

P: 56
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2.         CSQL = "SELECT Count(*) As Counted FROM AccountCardRegister, AccountCardComments"
  3.         CSQL = CSQL & " WHERE AccountCardRegister.AccountCard LIKE '*" & Cardnumber & "*'"
  4.         DoCmd.RunSQL CSQL
  5.  
  6.  
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!
Apr 19 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.