Get Followup and Followup Past Due Counts

[Rey]

Need to create table that contains user names and a field w/number of
follow ups due today as well as a field w/number of followups past due
(Specialist, FLWDueToday, FLWPastDue).

Below are two separate queries but am suffering memory loss as to how
to do this.

Thank you,
Rey

- due today
SELECT FU.SpecialistName, Count(FU.[Followup Date]) AS FLWDueToday
FROM Followup AS FU
WHERE (FU.[Followup Date] <> null AND FU.[Followup Date]=Date())
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;



- past due
SELECT FU.SpecialistName, Count(FU.[Followup Date]) as FLWPastDue
FROM Followup FU
WHERE (FU.[Followup Date] <> null AND FU.[Followup Date] < Date())
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;

 

[MGFoster]

Need to create table that contains user names and a field w/number of
follow ups due today as well as a field w/number of followups past due
(Specialist, FLWDueToday, FLWPastDue).

Below are two separate queries but am suffering memory loss as to how
to do this.

Thank you,
Rey

- due today
SELECT FU.SpecialistName, Count(FU.[Followup Date]) AS FLWDueToday
FROM Followup AS FU
WHERE (FU.[Followup Date] <> null AND FU.[Followup Date]=Date())
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;



- past due
SELECT FU.SpecialistName, Count(FU.[Followup Date]) as FLWPastDue
FROM Followup FU
WHERE (FU.[Followup Date] <> null AND FU.[Followup Date] < Date())
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The queries look OK, except you don't need

FU.[Followup Date] <> null AND

If the date is NULL it won't be selected by the "< Date()" comparison.
Anyway, the correct syntax for using NULL would have been

FU.[Followup Date] IS NOT NULL

What do you mean by "how to do this"?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfJeboechKqOuFEgEQIHdQCeOw9MMl7S1AgkDVmg7ZOqobkUVM8An2Qq
L2qRoVJ9dHRNcszDwBXUy2HM
=mcmp
-----END PGP SIGNATURE-----

 

[Rey]

Need to create table that contains user names and a field w/number of
follow ups due today as well as a field w/number of followups past due
(Specialist, FLWDueToday, FLWPastDue).

Below are two separate queries but am suffering memory loss as to how
to do this.

Thank you,
Rey

- due today
SELECT FU.SpecialistName, Count(FU.[Followup Date]) AS FLWDueToday
FROM Followup AS FU
WHERE (FU.[Followup Date] <> null AND FU.[Followup Date]=Date())
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;

- past due
SELECT FU.SpecialistName, Count(FU.[Followup Date]) as FLWPastDue
FROM Followup FU
WHERE (FU.[Followup Date] <> null AND FU.[Followup Date] < Date())
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The queries look OK, except you don't need

   FU.[Followup Date] <> null AND

If the date is NULL it won't be selected by the "< Date()" comparison.
Anyway, the correct syntax for using NULL would have been

   FU.[Followup Date] IS NOT NULL

What do you mean by "how to do this"?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfJeboechKqOuFEgEQIHdQCeOw9MMl7S1AgkDVmg7ZOqobkUVM8An2Qq
L2qRoVJ9dHRNcszDwBXUy2HM
=mcmp
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -

How to combine the results of both queries into one table containing:
specialistname, FLW Due Today, FLW Past Due.

Will try to run the 2 queries as select statements within the initial
select statement as in
Select specialistname, (FU.SpecialistName, Count(FU.[Followup Date])
AS FLWDueToday
FROM Followup AS FU etc) as FLW Past Due

 

[Rey]

Need to create table that contains user names and a field w/number of
follow ups due today as well as a field w/number of followups past due
(Specialist, FLWDueToday, FLWPastDue).
Below are two separate queries but am suffering memory loss as to how
to do this.
Thank you,
Rey
- due today
SELECT FU.SpecialistName, Count(FU.[Followup Date]) AS FLWDueToday
FROM Followup AS FU
WHERE (FU.[Followup Date] <> null AND FU.[Followup Date]=Date())
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;
- past due
SELECT FU.SpecialistName, Count(FU.[Followup Date]) as FLWPastDue
FROM Followup FU
WHERE (FU.[Followup Date] <> null AND FU.[Followup Date] < Date())
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The queries look OK, except you don't need

   FU.[Followup Date] <> null AND

If the date is NULL it won't be selected by the "< Date()" comparison.
Anyway, the correct syntax for using NULL would have been

   FU.[Followup Date] IS NOT NULL

What do you mean by "how to do this"?

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfJeboechKqOuFEgEQIHdQCeOw9MMl7S1AgkDVmg7ZOqobkUVM8An2Qq
L2qRoVJ9dHRNcszDwBXUy2HM
=mcmp
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -

How to combine the results of both queries into one table containing:
specialistname, FLW Due Today, FLW Past Due.

Will try to run the 2 queries as select statements within the initial
select statement as in
Select specialistname, (FU.SpecialistName, Count(FU.[Followup Date])
AS FLWDueToday
FROM Followup AS FU etc) as FLW Past Due- Hide quoted text -

- Show quoted text -

Sorry. Hit the return key too early.
Need to figure out how to combine the results of both queries into one
table.

Will make the change regarding IS NOT NULL in the query.

Thanks for replying,

Rey

 

[MGFoster]

Rey wrote:
Need to create table that contains user names and a field w/number of
follow ups due today as well as a field w/number of followups past due
(Specialist, FLWDueToday, FLWPastDue).
Below are two separate queries but am suffering memory loss as to how
to do this.
Thank you,
Rey
- due today
SELECT FU.SpecialistName, Count(FU.[Followup Date]) AS FLWDueToday
FROM Followup AS FU
WHERE (FU.[Followup Date] <> null AND FU.[Followup Date]=Date())
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;
- past due
SELECT FU.SpecialistName, Count(FU.[Followup Date]) as FLWPastDue
FROM Followup FU
WHERE (FU.[Followup Date] <> null AND FU.[Followup Date] < Date())
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The queries look OK, except you don't need
FU.[Followup Date] <> null AND
If the date is NULL it won't be selected by the "< Date()" comparison.
Anyway, the correct syntax for using NULL would have been
FU.[Followup Date] IS NOT NULL
What do you mean by "how to do this"?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSfJeboechKqOuFEgEQIHdQCeOw9MMl7S1AgkDVmg7ZOqobkUVM8An2Qq
L2qRoVJ9dHRNcszDwBXUy2HM
=mcmp
-----END PGP SIGNATURE------ Hide quoted text -
- Show quoted text -

How to combine the results of both queries into one table containing:
specialistname, FLW Due Today, FLW Past Due.

Will try to run the 2 queries as select statements within the initial
select statement as in
Select specialistname, (FU.SpecialistName, Count(FU.[Followup Date])
AS FLWDueToday
FROM Followup AS FU etc) as FLW Past Due- Hide quoted text -

- Show quoted text -

Sorry. Hit the return key too early.
Need to figure out how to combine the results of both queries into one
table.

Will make the change regarding IS NOT NULL in the query.

Thanks for replying,

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the IIf() function in a SUM() function to "count" and do not
use the date comparison in the WHERE clause:

SELECT FU.SpecialistName,
SUM(IIf([Followup Date]=Date(),1,0)) AS FLWDueToday,
SUM(IIf([Followup Date]<Date(),1,0)) AS FLWPastDue
FROM Followup AS FU
WHERE (FU.[Followup Date] IS NOT NULL
GROUP BY FU.SpecialistName
ORDER BY FU.SpecialistName;

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfQMd4echKqOuFEgEQIY6wCgwXF57Z2UJuvBWmXoyIHUh+LKTYUAn3Kf
fumst7bJt0qpk04QDIoZKMJH
=zXrb
-----END PGP SIGNATURE-----

 

[Rey]

Thank you.
Worked nicely and learned something new.

Rey