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

Trouble with a multi level query

P: 10
Hi,

I am having trouble with the following query.

I need to sort from a list of contacts the last date each was contacted.

This is to be represented in days so for instance:

0-42 days since last contact I have where

First = Previous Contact
Closed = To filter no longer required

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(*) AS Expr1, Date()-[first] AS Expr2, Contacts.Closed
  2. FROM Contacts
  3. GROUP BY Date()-[first], Contacts.Closed
  4. HAVING (((Date()-[first]) Between 0 And 42) AND ((Contacts.Closed) Is Null));
  5.  
My idea is to create similar queries for 42-91 days etc then merge them into one query to run a report from.

The 2 problems I am having are

1. The way I have implemented the count function is in error, I can't figure how to count individual records. The current logic is grouping similar dates and then counting only unique dates. (I'm sure this is straight forward but I can't think of how!)

2. How to merge the totals into one query. The only information I need are the totals, this one I don't have a clue how to do.

Thanks for reading

Stephen
Oct 22 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,430
1. The way I have implemented the count function is in error, I can't figure how to count individual records. The current logic is grouping similar dates and then counting only unique dates. (I'm sure this is straight forward but I can't think of how!)
I'm having trouble understanding this. What do you mean by count individual records? If you could provide fictitious data and results, it'd help me understand what you're looking for.
Oct 22 '07 #2

P: 10
I'm having trouble understanding this. What do you mean by count individual records? If you could provide fictitious data and results, it'd help me understand what you're looking for.
Hi, thanks for checking by, I got myself sorted on both points.

When I came back to look I realised I had Group By instead of Where placed in the SQL. Really straight forward mistake on my part.

Thanks again

Stephen
Oct 22 '07 #3

Rabbit
Expert Mod 10K+
P: 12,430
Hi, thanks for checking by, I got myself sorted on both points.

When I came back to look I realised I had Group By instead of Where placed in the SQL. Really straight forward mistake on my part.

Thanks again

Stephen
Glad you got it working.
Oct 22 '07 #4

Post your reply

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