Multiple Date Ranges In Query

S

SM

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
 
K

KARL DEWEY

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;
 
S

SM

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!

KARL DEWEY said:
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;


SM said:
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
 

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

Date query 2
Date Query 1
Union Query 2
Serious Problem with a Date count fields & YES/No field 4
Query sorting help 3
Calucation in query 2
Why is access filling in fields in query results 2
Before Due Date Help 1

Top