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

queries and sql

P: n/a
Iif I wanted a count of client "encounter" dates, from a table that
stored all services delivered to clients. A client may receive more
than one service on a particular date, but I only want to count
unduplicated combinations of date and client ID. I want to use this
as a data source for a form.

Right now, I would first create a saved query query1 with fields Date
and ClientID and group by both fields.

Query1:
SELECT tServices.Date, tServices.ClientID
FROM tServices
GROUP BY tServices.Date, tServices.ClientID;
Then I would I would construct the following as a data source for the
form:

SELECT Query1.ClientID, Count(Query1.Date) AS CountOfDate
FROM Query1
GROUP BY Query1.ClientID;

My question:
I would like to construct as a data source without using any saved
queries. Is it possible to write this as a single sql statement,
rather than an sql statement built on a saved query?
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Gwin" <do*********@verizon.net> wrote in message
news:gv********************************@4ax.com...

Query1:
SELECT tServices.Date, tServices.ClientID
FROM tServices
GROUP BY tServices.Date, tServices.ClientID;

SELECT Query1.ClientID, Count(Query1.Date) AS CountOfDate
FROM Query1
GROUP BY Query1.ClientID;

. Is it possible to write this as a single sql statement,
rather than an sql statement built on a saved query?

I think count distinct would do what you want - unfortunately Jet SQL
doesn't support it. Try something like this instead:

select ClientID, count([date]) as dateCount
from
(
select [date], ClientID
from tServices
group by [date], ClientID
) as a
group by ClientID
BTW [date] is a reserved word and thus is not a good choice for column name.
Nov 13 '05 #2

P: n/a
That is incredibly instructive!!! Thanks a million. This will really
help me make "cleaner" applications without nine-zillion saved
queries.

On Sat, 28 Aug 2004 10:18:09 -0400, "John Winterbottom"
<as******@hotmail.com> wrote:
"Gwin" <do*********@verizon.net> wrote in message
news:gv********************************@4ax.com.. .

Query1:
SELECT tServices.Date, tServices.ClientID
FROM tServices
GROUP BY tServices.Date, tServices.ClientID;

SELECT Query1.ClientID, Count(Query1.Date) AS CountOfDate
FROM Query1
GROUP BY Query1.ClientID;

. Is it possible to write this as a single sql statement,
rather than an sql statement built on a saved query?

I think count distinct would do what you want - unfortunately Jet SQL
doesn't support it. Try something like this instead:

select ClientID, count([date]) as dateCount
from
(
select [date], ClientID
from tServices
group by [date], ClientID
) as a
group by ClientID
BTW [date] is a reserved word and thus is not a good choice for column name.


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.