Problems with SQL Query Counting Unique Records

[mark.godecke]

Hello,

I'm trying to create a query to give the number of unique clients
served between a range of dates. Basically, I want count each unique
ClientID in the table "Records", when the Date column for records in
that table falls between two dates in a second table
"UserEnteredDate".

I looked through past messages in this group, but the only solutions
require using SQL, which I know zero about. I copied the example SQL
given, but I couldn't figure out how to include criteria from a second
table. This is what I have so far, but when I attempt to run the query
I get an error "Syntax Error in the From Clause." I tried Access's
built in help, but found it less than informative in telling me the
proper format to use.

SELECT Count(Records.ClientID) AS CountOfClientID
FROM (SELECT DISTINCT [ClientID] FROM Records)
FROM (UserEnteredDate)
WHERE (((Records.Date) Between
[UserEnteredDate]![UserEnteredSearchDate] And
[UserEnteredDate]![UserEnteredSearchDatePrev]))
GROUP BY UserEnteredDate.UserEnteredSearchDate,
UserEnteredDate.UserEnteredSearchDatePrev;

I hope it just needs a simple correction in punctuation, and if
somebody who knows what they are doing could tell me what that is, I'd
be very greatful.

 

[Marshall Barton]

I'm trying to create a query to give the number of unique clients
served between a range of dates. Basically, I want count each unique
ClientID in the table "Records", when the Date column for records in
that table falls between two dates in a second table
"UserEnteredDate".

I looked through past messages in this group, but the only solutions
require using SQL, which I know zero about. I copied the example SQL
given, but I couldn't figure out how to include criteria from a second
table. This is what I have so far, but when I attempt to run the query
I get an error "Syntax Error in the From Clause." I tried Access's
built in help, but found it less than informative in telling me the
proper format to use.

SELECT Count(Records.ClientID) AS CountOfClientID
FROM (SELECT DISTINCT [ClientID] FROM Records)
FROM (UserEnteredDate)
WHERE (((Records.Date) Between
[UserEnteredDate]![UserEnteredSearchDate] And
[UserEnteredDate]![UserEnteredSearchDatePrev]))
GROUP BY UserEnteredDate.UserEnteredSearchDate,
UserEnteredDate.UserEnteredSearchDatePrev;

I think this may be closer to what you're looking for:

SELECT Count(R.ClientID) AS CountOfClientID
FROM (
SELECT DISTINCT Records.ClientID FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date
Between U.UserEnteredSearchDate
And U.UserEnteredSearchDatePrev
) As R

 

[mark.godecke]

Thanks very much for your suggestion. However, when I try running the
new query you suggested, I get the following error

"Between operator without And in query expression 'Records.Date Between
U.UserEnteredSearchDate'.

Obviously there is an "And" present, so I have no idea what's wrong.
If it helps, I'm running Access 2003.

I'm trying to create a query to give the number of unique clients
served between a range of dates. Basically, I want count each unique
ClientID in the table "Records", when the Date column for records in
that table falls between two dates in a second table
"UserEnteredDate".

I looked through past messages in this group, but the only solutions
require using SQL, which I know zero about. I copied the example SQL
given, but I couldn't figure out how to include criteria from a second
table. This is what I have so far, but when I attempt to run the query
I get an error "Syntax Error in the From Clause." I tried Access's
built in help, but found it less than informative in telling me the
proper format to use.

SELECT Count(Records.ClientID) AS CountOfClientID
FROM (SELECT DISTINCT [ClientID] FROM Records)
FROM (UserEnteredDate)
WHERE (((Records.Date) Between
[UserEnteredDate]![UserEnteredSearchDate] And
[UserEnteredDate]![UserEnteredSearchDatePrev]))
GROUP BY UserEnteredDate.UserEnteredSearchDate,
UserEnteredDate.UserEnteredSearchDatePrev;

I think this may be closer to what you're looking for:

SELECT Count(R.ClientID) AS CountOfClientID
FROM (
SELECT DISTINCT Records.ClientID FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date
Between U.UserEnteredSearchDate
And U.UserEnteredSearchDatePrev
) As R

 

[Marshall Barton]

I don't suppose it's possible there's typo in your version
of the query, so I don't see how you could get that error.

Just in case, maybe you should post a Copy/Paste of the
query.
--
Marsh
MVP [MS Access]

Thanks very much for your suggestion. However, when I try running the
new query you suggested, I get the following error

"Between operator without And in query expression 'Records.Date Between
U.UserEnteredSearchDate'.

Obviously there is an "And" present, so I have no idea what's wrong.
If it helps, I'm running Access 2003.

I'm trying to create a query to give the number of unique clients
served between a range of dates. Basically, I want count each unique
ClientID in the table "Records", when the Date column for records in
that table falls between two dates in a second table
"UserEnteredDate".

I looked through past messages in this group, but the only solutions
require using SQL, which I know zero about. I copied the example SQL
given, but I couldn't figure out how to include criteria from a second
table. This is what I have so far, but when I attempt to run the query
I get an error "Syntax Error in the From Clause." I tried Access's
built in help, but found it less than informative in telling me the
proper format to use.

SELECT Count(Records.ClientID) AS CountOfClientID
FROM (SELECT DISTINCT [ClientID] FROM Records)
FROM (UserEnteredDate)
WHERE (((Records.Date) Between
[UserEnteredDate]![UserEnteredSearchDate] And
[UserEnteredDate]![UserEnteredSearchDatePrev]))
GROUP BY UserEnteredDate.UserEnteredSearchDate,
UserEnteredDate.UserEnteredSearchDatePrev;

I think this may be closer to what you're looking for:

SELECT Count(R.ClientID) AS CountOfClientID
FROM (
SELECT DISTINCT Records.ClientID FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date
Between U.UserEnteredSearchDate
And U.UserEnteredSearchDatePrev
) As R

 

[mark.godecke]

Here is the copy and past directly from the SQL query view in Access

SELECT Count(R.ClientID) AS CountOfClientID
FROM [SELECT DISTINCT Records.ClientID FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date
Between U.UserEnteredSearchDate
And U.UserEnteredSearchDatePrev
]. AS R;

 

[Marshall Barton]

Here is the copy and past directly from the SQL query view in Access

SELECT Count(R.ClientID) AS CountOfClientID
FROM [SELECT DISTINCT Records.ClientID FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date
Between U.UserEnteredSearchDate
And U.UserEnteredSearchDatePrev
]. AS R;

Looks good to me, but let's try an alternative to Between:

SELECT Count(R.ClientID) AS CountOfClientID
FROM [SELECT DISTINCT Records.ClientID FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date >= U.UserEnteredSearchDate
And Records.Date <= U.UserEnteredSearchDatePrev
]. AS R;

 

[Michael Gramelspacher]

Here is the copy and past directly from the SQL query view in Access

SELECT Count(R.ClientID) AS CountOfClientID
FROM [SELECT DISTINCT Records.ClientID FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date
Between U.UserEnteredSearchDate
And U.UserEnteredSearchDatePrev
]. AS R;

This might work if I have the names all correct.

SELECT R.ClientID, Count (*) AS CountofClientID
FROM Records AS R, UserEnteredDate AS U
WHERE R.myDate BETWEEN U.UserEnteredDate AND
U.UserEnteredDatePrev
GROUP BY R.ClientID;

 

[mark.godecke]

Marsh, the new version of the query, using greater than and less than,
seems to be working perfectly. Thank you very much for your help, you
pulled me out of a deep pit of frustration.

 

[mark.godecke]

The query suggested above that uses >= and <= gives me the count I
want. However, I would like to add a GROUP BY option, so that the
count of ClientIDs appears next to their ClientType. Both piece of
information are in the "Records" table. I've tried adding this in two
ways, the first adds the GROUP By to the main query, the second adds it
to the subquery:

SELECT Count(R.ClientID) AS CountOfClientID, ClientType
FROM [SELECT DISTINCT Records.ClientID FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date <= U.UserEnteredSearchDate
And Records.Date >= U.UserEnteredSearchDatePrev
]. AS R
GROUP BY ClientType;

which results in it asking me to "Enter Parameter Value" for Clientype

or

SELECT Count(R.ClientID) AS CountOfClientID
FROM [SELECT DISTINCT Records.ClientID, ClientType FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date <= U.UserEnteredSearchDate
And Records.Date >= U.UserEnteredSearchDatePrev
GROUP BY ClientType
]. AS R;

which results in the message "You tried to execute a query that does
not include the specified expression 'ClientID' as part of an aggregate
function."

Any suggestions on how to make this work?

 

[Marshall Barton]

The query suggested above that uses >= and <= gives me the count I
want. However, I would like to add a GROUP BY option, so that the
count of ClientIDs appears next to their ClientType. Both piece of
information are in the "Records" table. I've tried adding this in two
ways, the first adds the GROUP By to the main query, the second adds it
to the subquery:

SELECT Count(R.ClientID) AS CountOfClientID, ClientType
FROM [SELECT DISTINCT Records.ClientID FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date <= U.UserEnteredSearchDate
And Records.Date >= U.UserEnteredSearchDatePrev
]. AS R
GROUP BY ClientType;

which results in it asking me to "Enter Parameter Value" for Clientype

or

SELECT Count(R.ClientID) AS CountOfClientID
FROM [SELECT DISTINCT Records.ClientID, ClientType FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date <= U.UserEnteredSearchDate
And Records.Date >= U.UserEnteredSearchDatePrev
GROUP BY ClientType
]. AS R;

which results in the message "You tried to execute a query that does
not include the specified expression 'ClientID' as part of an aggregate
function."

I'm not sure I understand exactly what you are trying to do
here, but I think it should be more like:

SELECT R.ClientType, Count(R.ClientID) AS CountOfClientID
FROM [SELECT DISTINCT Records.ClientID, ClientType
FROM Records
INNER JOIN UserEnteredDate As U
ON Records.Date <= U.UserEnteredSearchDate
And Records.Date >= U.UserEnteredSearchDatePrev].
AS R
GROUP BY ClientType