SubQueries

G

Guest

Hello,

I am still a novice with SQL and am trying to figure out how I can combine
the following 2 queries into 1 using a subquery. As you can see, 'qry_New
Client Appt Info' is built upon 'qry_Num of Appt by Client'.

qry_Num of Appt by Client
*******************
SELECT [tbl_Client Apptointments].ClientID, Count([tbl_Client
Apptointments].ApptDate) AS CountOfApptDate
FROM [tbl_Client Apptointments]
GROUP BY [tbl_Client Apptointments].ClientID;

qry_New Client Appt Info
******************
SELECT [tbl_Client Apptointments].ApptDate
FROM [qry_Num of Appt by Client] LEFT JOIN [tbl_Client Apptointments] ON
[qry_Num of Appt by Client].ClientID = [tbl_Client Apptointments].ClientID
WHERE ((([qry_Num of Appt by Client].CountOfApptDate)=1));

Your guidance is greatly appreciated.

Daniel P
 
G

Guest

Try

SELECT T1.ApptDate
FROM [tbl_Client Apptointments] As T1
WHERE T1.ClientID In (SELECT T2.ClientID
FROM [tbl_Client Apptointments] As T2
GROUP BY T2.ClientID
HAVING Count(T2.ApptDate) = 1)
 
G

Guest

I'm hoping you can help me even further.

Here's the whole portrait.

I have a single table 'tbl_Client Apptointments' which has the following
fields
ApptID -Autonumber (PK)
ClientID - Number
ApptDate - Date/Time
ApptDetails - Memo

What I am trying to do (and you got me one step closer) is to extract a
summary by week of the number of client seen, but I have to be able to count
how many are returning client and how many are new client in for their first
visit and the total.

Year/Month/Week/#New/#Returning/Total
2007 1 1 3 5 8
2007 5 21 2 2 4
2007 5 22 0 3 3
2007 6 24 1 3 4

Thank you for enlighting me. I thought I had it based on your previous
answer but it has been steadily been going downhill the more I work on it.
--
Hope this helps,

Daniel P





Ofer Cohen said:
Try

SELECT T1.ApptDate
FROM [tbl_Client Apptointments] As T1
WHERE T1.ClientID In (SELECT T2.ClientID
FROM [tbl_Client Apptointments] As T2
GROUP BY T2.ClientID
HAVING Count(T2.ApptDate) = 1)

--
Good Luck
BS"D


Daniel said:
Hello,

I am still a novice with SQL and am trying to figure out how I can combine
the following 2 queries into 1 using a subquery. As you can see, 'qry_New
Client Appt Info' is built upon 'qry_Num of Appt by Client'.

qry_Num of Appt by Client
*******************
SELECT [tbl_Client Apptointments].ClientID, Count([tbl_Client
Apptointments].ApptDate) AS CountOfApptDate
FROM [tbl_Client Apptointments]
GROUP BY [tbl_Client Apptointments].ClientID;

qry_New Client Appt Info
******************
SELECT [tbl_Client Apptointments].ApptDate
FROM [qry_Num of Appt by Client] LEFT JOIN [tbl_Client Apptointments] ON
[qry_Num of Appt by Client].ClientID = [tbl_Client Apptointments].ClientID
WHERE ((([qry_Num of Appt by Client].CountOfApptDate)=1));

Your guidance is greatly appreciated.

Daniel P
Click to expand...
Click to expand...
 
M

Michael Gramelspacher

tbl_Client Apptointments
Click to expand...
try this, but note that I changed table name to tbl_Client Appointments.
(and watch line wrapping)

Let me know if it works.

SELECT DATEPART("yyyy",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Year],
DATEPART("m",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Month], DATEPART
("ww",a.apptdate-DATEPART("w",a.apptdate)+1) AS Week, (SELECT COUNT(* )
FROM [tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID NOT IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client
Appointments] AS d WHERE d.ApptDate <( b.apptdate - DATEPART
("w",b.apptdate) + 1)))) AS [New Patients], (SELECT COUNT(* ) FROM
[tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client Appointments]
AS d WHERE d.ApptDate <( b.apptdate - DATEPART("w",b.apptdate) + 1)))) AS
[Old Patients], COUNT(* ) AS [Total Patients]
FROM [tbl_Client Appointments] AS a
GROUP BY a.apptdate-DATEPART("w",a.apptdate)+1;
 
G

Gary Walter

Michael Gramelspacher said:
tbl_Client Apptointments
Click to expand...
try this, but note that I changed table name to tbl_Client Appointments.
(and watch line wrapping)

Let me know if it works.

SELECT DATEPART("yyyy",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Year],
DATEPART("m",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Month], DATEPART
("ww",a.apptdate-DATEPART("w",a.apptdate)+1) AS Week, (SELECT COUNT(* )
FROM [tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID NOT IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client
Appointments] AS d WHERE d.ApptDate <( b.apptdate - DATEPART
("w",b.apptdate) + 1)))) AS [New Patients], (SELECT COUNT(* ) FROM
[tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client Appointments]
AS d WHERE d.ApptDate <( b.apptdate - DATEPART("w",b.apptdate) + 1)))) AS
[Old Patients], COUNT(* ) AS [Total Patients]
FROM [tbl_Client Appointments] AS a
GROUP BY a.apptdate-DATEPART("w",a.apptdate)+1;
Click to expand...

Pardon me for butting in...

I wonder (I don't know and I could be wrong)
that if a client has more than one appt within
the same week, if that client will be counted
more than once for the week, i.e., if the outer
part of each subquery should not be GROUP BY'd
ClientID and first day of week?

SELECT
DATEPART("yyyy",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Year],
DATEPART("m",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Month],
DATEPART("ww",a.apptdate-DATEPART("w",a.apptdate)+1) AS Week,
(SELECT
COUNT(* )
FROM
[tbl_Client Appointments] AS b
WHERE
(a.apptdate - DATEPART("w",a.apptdate) + 1) =
(b.apptdate - DATEPART("w",b.apptdate) + 1)
AND
b.ClientID NOT IN
((SELECT
DISTINCT
d.ClientID
FROM [tbl_Client Appointments] AS d
WHERE
d.ApptDate <
( b.apptdate - DATEPART("w",b.apptdate) + 1)
))
GROUP BY
b.ClientID,
b.apptdate - DATEPART("w",b.apptdate) + 1
) AS [New Patients],
(SELECT
COUNT(* )
FROM
[tbl_Client Appointments] AS b
WHERE
(a.apptdate - DATEPART("w",a.apptdate) + 1) =
(b.apptdate - DATEPART("w",b.apptdate) + 1)
AND
b.ClientID IN
((SELECT
DISTINCT
d.ClientID
FROM
[tbl_Client Appointments] AS d
WHERE
d.ApptDate <
( b.apptdate - DATEPART("w",b.apptdate) + 1)
))
GROUP BY
b.ClientID,
b.apptdate - DATEPART("w",b.apptdate) + 1
) AS [Old Patients],
COUNT(* ) AS [Total Patients]
FROM
[tbl_Client Appointments] AS a
GROUP BY
a.apptdate-DATEPART("w",a.apptdate)+1;

I could be wrong...I did not test.

I also wonder if this might not be the case
where it would help to create a Calendar table
where the fields DteYear, DteMonth, DteWeek,
FirstDayOfWeek are already computed
based on its Dte field.

Again, apologies for butting in...
(especially if I was wrong)

good luck,

gary
 
G

Gary Walter

please disregard my previous post...sorry

"Gary Walter"wrote:
Michael Gramelspacher said:
(e-mail address removed) says...
tbl_Client Apptointments
Click to expand...
try this, but note that I changed table name to tbl_Client Appointments.
(and watch line wrapping)

Let me know if it works.

SELECT DATEPART("yyyy",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Year],
DATEPART("m",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Month], DATEPART
("ww",a.apptdate-DATEPART("w",a.apptdate)+1) AS Week, (SELECT COUNT(* )
FROM [tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID NOT IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client
Appointments] AS d WHERE d.ApptDate <( b.apptdate - DATEPART
("w",b.apptdate) + 1)))) AS [New Patients], (SELECT COUNT(* ) FROM
[tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client Appointments]
AS d WHERE d.ApptDate <( b.apptdate - DATEPART("w",b.apptdate) + 1)))) AS
[Old Patients], COUNT(* ) AS [Total Patients]
FROM [tbl_Client Appointments] AS a
GROUP BY a.apptdate-DATEPART("w",a.apptdate)+1;
Click to expand...

Pardon me for butting in...

I wonder (I don't know and I could be wrong)
that if a client has more than one appt within
the same week, if that client will be counted
more than once for the week, i.e., if the outer
part of each subquery should not be GROUP BY'd
ClientID and first day of week?

SELECT
DATEPART("yyyy",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Year],
DATEPART("m",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Month],
DATEPART("ww",a.apptdate-DATEPART("w",a.apptdate)+1) AS Week,
(SELECT
COUNT(* )
FROM
[tbl_Client Appointments] AS b
WHERE
(a.apptdate - DATEPART("w",a.apptdate) + 1) =
(b.apptdate - DATEPART("w",b.apptdate) + 1)
AND
b.ClientID NOT IN
((SELECT
DISTINCT
d.ClientID
FROM [tbl_Client Appointments] AS d
WHERE
d.ApptDate <
( b.apptdate - DATEPART("w",b.apptdate) + 1)
))
GROUP BY
b.ClientID,
b.apptdate - DATEPART("w",b.apptdate) + 1
) AS [New Patients],
(SELECT
COUNT(* )
FROM
[tbl_Client Appointments] AS b
WHERE
(a.apptdate - DATEPART("w",a.apptdate) + 1) =
(b.apptdate - DATEPART("w",b.apptdate) + 1)
AND
b.ClientID IN
((SELECT
DISTINCT
d.ClientID
FROM
[tbl_Client Appointments] AS d
WHERE
d.ApptDate <
( b.apptdate - DATEPART("w",b.apptdate) + 1)
))
GROUP BY
b.ClientID,
b.apptdate - DATEPART("w",b.apptdate) + 1
) AS [Old Patients],
COUNT(* ) AS [Total Patients]
FROM
[tbl_Client Appointments] AS a
GROUP BY
a.apptdate-DATEPART("w",a.apptdate)+1;

I could be wrong...I did not test.

I also wonder if this might not be the case
where it would help to create a Calendar table
where the fields DteYear, DteMonth, DteWeek,
FirstDayOfWeek are already computed
based on its Dte field.

Again, apologies for butting in...
(especially if I was wrong)

good luck,

gary
Click to expand...
 
G

Guest

Michael,

First off, thanking for taking the time to help me!

Secondly, I am getting a synthax error when I try to run it and it display
the following in the error message.

(SELECT COUNT(* )
FROM [tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID NOT IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client
Appointments] AS d WHERE d.A

Any ideas?
--
Hope this helps,

Daniel P







Michael Gramelspacher said:
tbl_Client Apptointments
Click to expand...
try this, but note that I changed table name to tbl_Client Appointments.
(and watch line wrapping)

Let me know if it works.

SELECT DATEPART("yyyy",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Year],
DATEPART("m",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Month], DATEPART
("ww",a.apptdate-DATEPART("w",a.apptdate)+1) AS Week, (SELECT COUNT(* )
FROM [tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID NOT IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client
Appointments] AS d WHERE d.ApptDate <( b.apptdate - DATEPART
("w",b.apptdate) + 1)))) AS [New Patients], (SELECT COUNT(* ) FROM
[tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client Appointments]
AS d WHERE d.ApptDate <( b.apptdate - DATEPART("w",b.apptdate) + 1)))) AS
[Old Patients], COUNT(* ) AS [Total Patients]
FROM [tbl_Client Appointments] AS a
GROUP BY a.apptdate-DATEPART("w",a.apptdate)+1;
Click to expand...
 
G

Guest

I noticed one thing reviewing the SQL statement (and I am no expert so I may
be way off the deep end here) I noticed 2 As b declarations. Is this
permisable in SQL?

Daniel P






Daniel said:
Michael,

First off, thanking for taking the time to help me!

Secondly, I am getting a synthax error when I try to run it and it display
the following in the error message.

(SELECT COUNT(* )
FROM [tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID NOT IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client
Appointments] AS d WHERE d.A

Any ideas?
--
Hope this helps,

Daniel P







Michael Gramelspacher said:
tbl_Client Apptointments
Click to expand...
try this, but note that I changed table name to tbl_Client Appointments.
(and watch line wrapping)

Let me know if it works.

SELECT DATEPART("yyyy",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Year],
DATEPART("m",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Month], DATEPART
("ww",a.apptdate-DATEPART("w",a.apptdate)+1) AS Week, (SELECT COUNT(* )
FROM [tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID NOT IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client
Appointments] AS d WHERE d.ApptDate <( b.apptdate - DATEPART
("w",b.apptdate) + 1)))) AS [New Patients], (SELECT COUNT(* ) FROM
[tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client Appointments]
AS d WHERE d.ApptDate <( b.apptdate - DATEPART("w",b.apptdate) + 1)))) AS
[Old Patients], COUNT(* ) AS [Total Patients]
FROM [tbl_Client Appointments] AS a
GROUP BY a.apptdate-DATEPART("w",a.apptdate)+1;
Click to expand...
Click to expand...
 
M

Michael Gramelspacher

I noticed one thing reviewing the SQL statement (and I am no expert so I may
be way off the deep end here) I noticed 2 As b declarations. Is this
permisable in SQL?

Daniel P






Daniel said:
Michael,

First off, thanking for taking the time to help me!

Secondly, I am getting a synthax error when I try to run it and it display
the following in the error message.

(SELECT COUNT(* )
FROM [tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID NOT IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client
Appointments] AS d WHERE d.A

Any ideas?
--
Hope this helps,

Daniel P







Michael Gramelspacher said:
tbl_Client Apptointments

try this, but note that I changed table name to tbl_Client Appointments.
(and watch line wrapping)

Let me know if it works.

SELECT DATEPART("yyyy",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Year],
Click to expand...
Click to expand...
Click to expand...

download this: http://www.psci.net/gramelsp/temp/db4.zip

Just a table with 7 rows and a query. This query also runs with slight
modifications on SQL Server Express.
 
G

Guest

I cannot thank you enough! Supplying me with a functional example to learn
from, wow!!!!

Daniel P






Michael Gramelspacher said:
I noticed one thing reviewing the SQL statement (and I am no expert so I may
be way off the deep end here) I noticed 2 As b declarations. Is this
permisable in SQL?

Daniel P






Daniel said:
Michael,

First off, thanking for taking the time to help me!

Secondly, I am getting a synthax error when I try to run it and it display
the following in the error message.

(SELECT COUNT(* )
FROM [tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID NOT IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client
Appointments] AS d WHERE d.A

Any ideas?
--
Hope this helps,

Daniel P







:

tbl_Client Apptointments

try this, but note that I changed table name to tbl_Client Appointments.
(and watch line wrapping)

Let me know if it works.

SELECT DATEPART("yyyy",a.apptdate-DATEPART("w",a.apptdate)+1) AS [Year],
Click to expand...
Click to expand...

download this: http://www.psci.net/gramelsp/temp/db4.zip

Just a table with 7 rows and a query. This query also runs with slight
modifications on SQL Server Express.
Click to expand...
 
M

Michael Gramelspacher

I cannot thank you enough! Supplying me with a functional example to learn
from, wow!!!!

Daniel P






Michael Gramelspacher said:
I noticed one thing reviewing the SQL statement (and I am no expert so I may
be way off the deep end here) I noticed 2 As b declarations. Is this
permisable in SQL?

Daniel P






:

Michael,

First off, thanking for taking the time to help me!

Secondly, I am getting a synthax error when I try to run it and it display
the following in the error message.

(SELECT COUNT(* )
FROM [tbl_Client Appointments] AS b WHERE (a.apptdate - DATEPART
("w",a.apptdate) + 1) = (b.apptdate - DATEPART("w",b.apptdate) + 1) AND
b.ClientID NOT IN ((SELECT DISTINCT d.ClientID FROM [tbl_Client
Appointments] AS d WHERE d.A

Any ideas?
Click to expand...
Click to expand...
Click to expand...
Here is a tip. Copy the SQL statement and paste it here. Select MSAccess.
http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm

Might make it easier to read. Do not paste formated SQL back into Access. When
you save the query, it will look worse than before.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

update recordset that isn't updateable... 7
What's wrong with this query? 5
followup question on updating un-updateable queries 1
SQL Error 11
query duplication problem 1
select random record 2
Recordset?? 2
Max record 4

Top