Hi all
I'm trying to put together a phone call logging system that has (amongst other things) a Call table (ID, SubjectOfCall, AdviceGiven, CallerID) and a Caller table (ID, Name, PhoneNum).
I'm trying to form a query that will tell me, for a given subject, the number of calls, and the number of callers. This way we will prioritise a subject with say, 10 calls from 10 different people higher than a subject with 10 calls from the same person
Because access doesn't have count distinct, I can't do this:
Select Call.Subject, count(Call.ID), count distinct(Caller.ID) from blah
Googling suggests derived tables/subqueries are the way to go, but I've found my subquery works in isolation, but when I use it in the larger query I get the same results as my first attempt (ie number of callers were counted, but not the number of different callers).
I can do this in vba, but would like to avoid such if possible. Is there a way to get the info I am looking for just using access sql, or do i need to dive into code?
many thanks
mcalex
_________
acc2003, winxp
I'm trying to put together a phone call logging system that has (amongst other things) a Call table (ID, SubjectOfCall, AdviceGiven, CallerID) and a Caller table (ID, Name, PhoneNum).
I'm trying to form a query that will tell me, for a given subject, the number of calls, and the number of callers. This way we will prioritise a subject with say, 10 calls from 10 different people higher than a subject with 10 calls from the same person
Because access doesn't have count distinct, I can't do this:
Select Call.Subject, count(Call.ID), count distinct(Caller.ID) from blah
Googling suggests derived tables/subqueries are the way to go, but I've found my subquery works in isolation, but when I use it in the larger query I get the same results as my first attempt (ie number of callers were counted, but not the number of different callers).
I can do this in vba, but would like to avoid such if possible. Is there a way to get the info I am looking for just using access sql, or do i need to dive into code?
many thanks
mcalex
_________
acc2003, winxp
Last edited:
