Summary report based on date range - help!

[Stacy]

Hi again!

For part of my project I need to display the number of records that
were entered into the database within the past 2 weeks, that were
entered 2-4 weeks ago, and that were entered 4 or more weeks ago. I
also need to display how many are in the database that are within 5
days of their due date and how many are past their due dates. Can
anyone give me some guidance (the rest of my questions from my earlier
post I've figured out!)

For reference, my table is tblIssue and the relevant fields are:

I_ID (Issue Number - tblIssue primary key)
I_ORIG_DATE (Date the issue was entered into the database; via the New
Issue form, this number is generated with a now() function.)
I_DATE_RES_REQ (date the resolution is needed)

I've found info on how to do this if you are manually setting a start
and end date in a query, but I need the query to basically grab today's
date, then determine how much time has passed between now and when it
was entered into the database, and then return a count of the records
that fall into each time frame (<2 weeks, between 2-4 weeks, and >4
weeks).

Similarly, I need a query that will grab today's date, then determine
how many records have I_DATE_RES_REQ dates within 5 days after today,
and determine how many records have I_DATE_RES_REQ dates that are
farther back than 5 days before today.

Logically, I understand it, but I'm not familiar enough with queries or
SQL just now to simply sit down and plug it out (and the four books on
my desk don't touch on time frames!)

Thanks!

 

[John Spencer]

Use the Date function to get today's date
Use DateDiff to calculate the number of days between two dates

If all you want is the number then you can use the following formulas
Sum(Abs(DateDiff("d",Date(),I_DATE_RES_REQ) <=5)) as InNext5
Sum(Abs(DateDiff("d",I_DATE_RES_REQ,Date()) <=5)) as Over5Ago
Sum(Abs(DateDiff("d",I_Orig_Date,Date())<=14) As Prior2Weeks
Sum(Abs(DateDiff("d",I_Orig_Date,Date())>28) As Over4Weeks
Sum(Abs(DateDiff("d",I_Orig_Date,Date()) Between 15 and 28) As
Prior2to4Weeks

The last might have to be expressed as
Sum(Abs(DateDiff("d",I_Orig_Date,Date()) > 15 and
Abs(DateDiff("d",I_Orig_Date,Date()) <=28)

All in one query that might work. Try adding one calculated field at a time
to see how it works and if it breaks.
SELECT
Sum(Abs(DateDiff("d",Date(),I_DATE_RES_REQ) <=5)) as InNext5
, Sum(Abs(DateDiff("d",I_DATE_RES_REQ,Date()) <=5)) as Over5Ago
, Sum(Abs(DateDiff("d",I_Orig_Date,Date())<=14) As Prior2Weeks
, Sum(Abs(DateDiff("d",I_Orig_Date,Date()) Between 15 and 28) As Prior4Weeks
, Sum(Abs(DateDiff("d",I_Orig_Date,Date())>28) As Over4Weeks
FROM tblIssue

If you just want to do this in