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

Count function in a query

P: 15
Thanks for all the help so far, my database is starting to come along well now.

I am having trouble with a query at the minute. Basically it goes like this:

I have a main table to my database which holds client info, including city name.

Through a many to many relationship this is conected to a contact table which records contacts with the clients. This table has a field titled outcome in which either 'no reply', 'call later', or returned call' is recorded

What I am trying to do is run a query that will sort and count call types by location (city)

i.e.

[PHP]Count Call type City

3 call later London
6 no reply London
3 call later Birmingham[/PHP]

I hope this reads ok for you, any help is much appreaciated!
Jan 11 '07 #1
Share this Question
Share on Google+
14 Replies


Expert 100+
P: 218
Hi

Try something like this
Expand|Select|Wrap|Line Numbers
  1. Select Count(*), CallType, City From tblClient
  2. Group By City, CallType
  3. Order By City, CallType
HTH
Steve
Jan 11 '07 #2

P: 15
Hi

Try something like this
Expand|Select|Wrap|Line Numbers
  1. Select Count(*), CallType, City From tblClient
  2. Group By City, CallType
  3. Order By City, CallType
HTH
Steve
Cheers for that,

I have modified it slighty

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS Expr1, Calls.Subject, tblclient.city
  2. FROM contacts INNER JOIN Calls ON tblclient.ContactID = Calls.ContactID
  3. GROUP BY Calls.Subject, tblclient.city
  4. ORDER BY tblclient.city, Calls.Subject;
Then something strange happens...

I tested it on a 5 record db

thing is it works perfectly for all the records except 2 and 5

for some reason the query returns results like so

Count Subject City

2 no reply london
3 no reply london

etc, as I have said all the other records sort and count perfectly.

the subject field is a text input, I have checked the formatting and it corresponds with that of other records.

The query seems to treat these 2 records as separate counts, anyone got any ideas?

Thanks
Jan 11 '07 #3

NeoPa
Expert Mod 15k+
P: 31,308
Look at the data very carefully.
Are there any extraneous spaces or other white-space chars?
Jan 12 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1.  SELECT Count(*) AS Expr1, Calls.Subject, tblclient.city
  2. FROM tblclient INNER JOIN Calls ON tblclient.ContactID = Calls.ContactID
  3. GROUP BY Calls.Subject, tblclient.city
  4. ORDER BY tblclient.city, Calls.Subject;
You never replaced the contacts table name with tblclient
Jan 12 '07 #5

NeoPa
Expert Mod 15k+
P: 31,308
Good spot Mary.
I've added the code tags to the post (#3) so that it's more clearly visible what was used.
Jan 12 '07 #6

P: 15
Thanks for the help, bit of an oversight there!!

I thought about Neopa's post an decided it would be better to use a dropdown list to select an outcome to avoid the chance of white space errors.

One thing I am trying to sort out is the query display, the dropdown list is as usual supported by an autonumbered table. How do I go about getting the linked text to appear in the query result as opposed to just the autonumber number?

Thanks
Jan 15 '07 #7

NeoPa
Expert Mod 15k+
P: 31,308
Try the Column property of the ComboBox.
Press F1 on the word Column from the VBA window for a fuller explanation of the Column item.
Jan 16 '07 #8

P: 15
Cheers for that,

I have modified it slighty

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS Expr1, Calls.Subject, tblclient.city
  2. FROM tblclient INNER JOIN Calls ON tblclient.ContactID = Calls.ContactID
  3. GROUP BY Calls.Subject, tblclient.city
  4. ORDER BY tblclient.city, Calls.Subject;
Then something strange happens...

I tested it on a 5 record db

thing is it works perfectly for all the records except 2 and 5

for some reason the query returns results like so

Count Subject City

2 no reply london
3 no reply london

etc, as I have said all the other records sort and count perfectly.

the subject field is a text input, I have checked the formatting and it corresponds with that of other records.

The query seems to treat these 2 records as separate counts, anyone got any ideas?

Thanks
I got the last bit sorted thank you Neopa (again)

I made the corrections that mmccarthy pointed out.

I found out why the counts were running the way I showed, its because of the different call dates, i.e. it counts all the calls from one day and sums them by city and subject. Is there any way to ammend the sql so it sums the totals for the user inputed dates?

Thanks

Stephen
Jan 16 '07 #9

NeoPa
Expert Mod 15k+
P: 31,308
Do you have your latest (as correct as possible) SQL?
Jan 16 '07 #10

P: 15
Do you have your latest (as correct as possible) SQL?
I'm little further on from my last post

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS Expr1, Calls.Subject, contacts.ContactTypeID, Calls.CallDate
  2. FROM contacts INNER JOIN Calls ON contacts.ContactID = Calls.ContactID
  3. GROUP BY Calls.Subject, contacts.ContactTypeID, Calls.CallDate
  4. HAVING (((Calls.CallDate) Between [Enter Start of Date Range] And [Enter End of Date Range]))
  5. ORDER BY contacts.ContactTypeID, Calls.Subject;


It seems when I add the date range option in the above code it changes the type of result
Jan 16 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
Is this what you're looking for?

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS Expr1, Calls.Subject, contacts.ContactTypeID
  2. FROM contacts INNER JOIN Calls 
  3. ON contacts.ContactID = Calls.ContactID
  4. WHERE [last contact date] Between [Enter Start of Date Range] And [Enter End of Date Range]
  5. GROUP BY Calls.Subject, contacts.ContactTypeID
  6. ORDER BY contacts.ContactTypeID, Calls.Subject;
  7.  
Mary
Jan 16 '07 #12

NeoPa
Expert Mod 15k+
P: 31,308
I'm little further on from my last post

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS Expr1, Calls.Subject, contacts.ContactTypeID, Calls.CallDate
  2. FROM contacts INNER JOIN Calls ON contacts.ContactID = Calls.ContactID
  3. GROUP BY Calls.Subject, contacts.ContactTypeID, Calls.CallDate
  4. HAVING (((Calls.CallDate) Between [Enter Start of Date Range] And [Enter End of Date Range]))
  5. ORDER BY contacts.ContactTypeID, Calls.Subject;


It seems when I add the date range option in the above code it changes the type of result
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS Expr1, Calls.Subject, contacts.ContactTypeID, Calls.CallDate
  2. FROM contacts INNER JOIN Calls ON contacts.ContactID = Calls.ContactID
  3. WHERE (Calls.CallDate Between [Enter Start of Date Range] And [Enter End of Date Range])
  4. GROUP BY contacts.ContactTypeID, Calls.Subject, Calls.CallDate
  5. ORDER BY contacts.ContactTypeID, Calls.Subject;
This is a simple tidy up of your SQL.
Looking at your latest request I'm a little stuck :
You show examples using a field (City) but this isn't in your SQL at all.
Do you want to GROUP BY date alone (have only one result line ever for each date)?
If so, you need to decide what you want to do with the other fields.
Expand|Select|Wrap|Line Numbers
  1. ------------Input--------    ----------------Output----------------
  2. Date        City    Value    Date (Group)  City (?)     Value (Sum)
  3. 1 Jan 2007  London   32
  4. 1 Jan 2007  Bristol  12
  5. 1 Jan 2007  London   19      1 Jan 2007    Undefined(?)  63
If they are to be included at all, you need to treat them as some sort of Aggregate. Otherwise you may get extra output lines for each different item found in the field.
In this example I use City but it can be any non-aggregated field in a GROUP BY query.

If the same data had a query with a GROUP BY including the other field (City in this case) you get :
Expand|Select|Wrap|Line Numbers
  1. ------------Input--------    ----------------Output----------------
  2. Date        City    Value    Date (Group)  City (Group) Value (Sum)
  3. 1 Jan 2007  London   32
  4. 1 Jan 2007  London   19      1 Jan 2007    London        51
  5. 1 Jan 2007  Bristol  12      1 Jan 2007    Bristol       12
Jan 16 '07 #13

P: 15
Just a thank you for all your help, time and patience, my db is now nearly complete.
Jan 20 '07 #14

NeoPa
Expert Mod 15k+
P: 31,308
Good to hear it lostdog. I'm glad to have helped.
Jan 21 '07 #15

Post your reply

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