Multiple Date Ranges In Query

[Guest]

Hi,

I have a table called tblPlan with four fields: Title (string), Milestone
(string), DueDate (date), Complete (boolean).

I want to run a query that will tell me for each Title how many Milestones
are Overdue (ie. DueDate < Today and Complete = False), AND for each Title
how many Milestones are Due in 7 Days (ie. DueDate >=Today and DueDate <
Today+8 and Complete = False). I'm also going to go a little further (ie.
due in 8 to 30 days, and due in over 30 days), but if I can get the basic
idea to work, adding the rest of the conditions should be straightforward!

I can accomplish this using multiple queries and tying them together in a
union query at the end, but this seems a little more complicated than it
should be. Can this be done in a single query?

Thanks for any help!

Sean

 

[Guest]

There probably is a much better way to do it but I did it with five queries.

SM_Query --
SELECT tblPlan.Title, Count(tblPlan.Title) AS [Over Due Milestone], "" AS
[Due Milestone in 8 days], "" AS [Due Milestone in 8-30 days]
FROM tblPlan
WHERE (((tblPlan.DueDate)<Date()) AND ((tblPlan.Complete)=0))
GROUP BY tblPlan.Title, "", "";

SM_Query-1 --
SELECT tblPlan.Title, Count(tblPlan.Title) AS [Due Milestone in 8 days], ""
AS x, "" AS y
FROM tblPlan
WHERE (((tblPlan.DueDate) Between Date() And Date()+8) AND
((tblPlan.Complete)=0))
GROUP BY tblPlan.Title, "", "";

SM_Query-2 --
SELECT tblPlan.Title, Count(tblPlan.Title) AS [Due Milestone in 8-30 days],
"" AS x, "" AS y
FROM tblPlan
WHERE (((tblPlan.DueDate) Between Date()+9 And Date()+30) AND
((tblPlan.Complete)=0))
GROUP BY tblPlan.Title, "", "";

SM_Query milestones --
SELECT SM_Query.Title, SM_Query.[Over Due Milestone], [Due Milestone in 8
days], [Due Milestone in 8-30 days]
FROM SM_Query
UNION SELECT [SM_Query-1].Title, [x], [SM_Query-1].[Due Milestone in 8
days], [y]
FROM [SM_Query-1]
UNION SELECT [SM_Query-2].Title,[x], [y], [SM_Query-2].[Due Milestone in
8-30 days]
FROM [SM_Query-2];


SELECT [SM_Query milestones].Title, Sum(Val([Over Due Milestone])) AS [Over
Due], Sum(Val([Due Milestone in 8 days])) AS [Due in 8 days], Sum(Val([Due
Milestone in 8-30 days])) AS [Due >8 and 30 days]
FROM [SM_Query milestones]
GROUP BY [SM_Query milestones].Title;

 

[Guest]

Thanks for the sample code! This is a similar approach I took with the same
situation in another database. It seems a little clunky, but it works!

There probably is a much better way to do it but I did it with five queries.

SM_Query --
SELECT tblPlan.Title, Count(tblPlan.Title) AS [Over Due Milestone], "" AS
[Due Milestone in 8 days], "" AS [Due Milestone in 8-30 days]
FROM tblPlan
WHERE (((tblPlan.DueDate)<Date()) AND ((tblPlan.Complete)=0))
GROUP BY tblPlan.Title, "", "";

SM_Query-1 --
SELECT tblPlan.Title, Count(tblPlan.Title) AS [Due Milestone in 8 days], ""
AS x, "" AS y
FROM tblPlan
WHERE (((tblPlan.DueDate) Between Date() And Date()+8) AND
((tblPlan.Complete)=0))
GROUP BY tblPlan.Title, "", "";

SM_Query-2 --
SELECT tblPlan.Title, Count(tblPlan.Title) AS [Due Milestone in 8-30 days],
"" AS x, "" AS y
FROM tblPlan
WHERE (((tblPlan.DueDate) Between Date()+9 And Date()+30) AND
((tblPlan.Complete)=0))
GROUP BY tblPlan.Title, "", "";

SM_Query milestones --
SELECT SM_Query.Title, SM_Query.[Over Due Milestone], [Due Milestone in 8
days], [Due Milestone in 8-30 days]
FROM SM_Query
UNION SELECT [SM_Query-1].Title, [x], [SM_Query-1].[Due Milestone in 8
days], [y]
FROM [SM_Query-1]
UNION SELECT [SM_Query-2].Title,[x], [y], [SM_Query-2].[Due Milestone in
8-30 days]
FROM [SM_Query-2];


SELECT [SM_Query milestones].Title, Sum(Val([Over Due Milestone])) AS [Over
Due], Sum(Val([Due Milestone in 8 days])) AS [Due in 8 days], Sum(Val([Due
Milestone in 8-30 days])) AS [Due >8 and 30 days]
FROM [SM_Query milestones]
GROUP BY [SM_Query milestones].Title;

Hi,

I have a table called tblPlan with four fields: Title (string), Milestone
(string), DueDate (date), Complete (boolean).

I want to run a query that will tell me for each Title how many Milestones
are Overdue (ie. DueDate < Today and Complete = False), AND for each Title
how many Milestones are Due in 7 Days (ie. DueDate >=Today and DueDate <
Today+8 and Complete = False). I'm also going to go a little further (ie.
due in 8 to 30 days, and due in over 30 days), but if I can get the basic
idea to work, adding the rest of the conditions should be straightforward!

I can accomplish this using multiple queries and tying them together in a
union query at the end, but this seems a little more complicated than it
should be. Can this be done in a single query?

Thanks for any help!

Sean