Count function in a query

[lostdog]

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!

[cyberdwarf]

Hi

Try something like this

Select Count(*), CallType, City From tblClient
Group By City, CallType
Order By City, CallType

HTH
Steve

[lostdog]

Hi

Try something like this

Select Count(*), CallType, City From tblClient
Group By City, CallType
Order By City, CallType

HTH
Steve

Cheers for that,

I have modified it slighty

SELECT Count(*) AS Expr1, Calls.Subject, tblclient.city
FROM contacts INNER JOIN Calls ON tblclient.ContactID = Calls.ContactID
GROUP BY Calls.Subject, tblclient.city
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

[NeoPa]

Look at the data very carefully.
Are there any extraneous spaces or other white-space chars?

[MMcCarthy]

Try this ...

 SELECT Count(*) AS Expr1, Calls.Subject, tblclient.city
FROM tblclient INNER JOIN Calls ON tblclient.ContactID = Calls.ContactID
GROUP BY Calls.Subject, tblclient.city
ORDER BY tblclient.city, Calls.Subject;

You never replaced the contacts table name with tblclient

[NeoPa]

Good spot Mary.
I've added the code tags to the post (#3) so that it's more clearly visible what was used.

[lostdog]

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

[NeoPa]

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.

[lostdog]

Cheers for that,

I have modified it slighty

SELECT Count(*) AS Expr1, Calls.Subject, tblclient.city
FROM tblclient INNER JOIN Calls ON tblclient.ContactID = Calls.ContactID
GROUP BY Calls.Subject, tblclient.city
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

[NeoPa]

Do you have your latest (as correct as possible) SQL?

[lostdog]

Do you have your latest (as correct as possible) SQL?

I'm little further on from my last post

SELECT Count(*) AS Expr1, Calls.Subject, contacts.ContactTypeID, Calls.CallDate
FROM contacts INNER JOIN Calls ON contacts.ContactID = Calls.ContactID
GROUP BY Calls.Subject, contacts.ContactTypeID, Calls.CallDate
HAVING (((Calls.CallDate) Between [Enter Start of Date Range] And [Enter End of Date Range]))
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

[MMcCarthy]

Is this what you're looking for?

SELECT Count(*) AS Expr1, Calls.Subject, contacts.ContactTypeID
FROM contacts INNER JOIN Calls 
ON contacts.ContactID = Calls.ContactID
WHERE [last contact date] Between [Enter Start of Date Range] And [Enter End of Date Range]
GROUP BY Calls.Subject, contacts.ContactTypeID
ORDER BY contacts.ContactTypeID, Calls.Subject;
 

Mary

[NeoPa]

I'm little further on from my last post

SELECT Count(*) AS Expr1, Calls.Subject, contacts.ContactTypeID, Calls.CallDate
FROM contacts INNER JOIN Calls ON contacts.ContactID = Calls.ContactID
GROUP BY Calls.Subject, contacts.ContactTypeID, Calls.CallDate
HAVING (((Calls.CallDate) Between [Enter Start of Date Range] And [Enter End of Date Range]))
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

SELECT Count(*) AS Expr1, Calls.Subject, contacts.ContactTypeID, Calls.CallDate
FROM contacts INNER JOIN Calls ON contacts.ContactID = Calls.ContactID
WHERE (Calls.CallDate Between [Enter Start of Date Range] And [Enter End of Date Range])
GROUP BY contacts.ContactTypeID, Calls.Subject, Calls.CallDate
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.

------------Input--------    ----------------Output----------------
Date        City    Value    Date (Group)  City (?)     Value (Sum)
1 Jan 2007  London   32
1 Jan 2007  Bristol  12
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 :

------------Input--------    ----------------Output----------------
Date        City    Value    Date (Group)  City (Group) Value (Sum)
1 Jan 2007  London   32
1 Jan 2007  London   19      1 Jan 2007    London        51
1 Jan 2007  Bristol  12      1 Jan 2007    Bristol       12

[lostdog]

Just a thank you for all your help, time and patience, my db is now nearly complete.

[NeoPa]

Good to hear it lostdog. I'm glad to have helped.