Trouble with a multi level query

[lostdawg]

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

SELECT Count(*) AS Expr1, Date()-[first] AS Expr2, Contacts.Closed
FROM Contacts
GROUP BY Date()-[first], Contacts.Closed
HAVING (((Date()-[first]) Between 0 And 42) AND ((Contacts.Closed) Is Null));
 

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

[Rabbit]

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.

[lostdawg]

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

[Rabbit]

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.