Counting Days Between Dates Showing Per Month

[shelby55]

Hello

I'm using Access 2003.

I created an Excel workbook for data collection but it has now grown to where I need to create in Access.

The workbook is for collection of clinic visits and I need to know the time between certain dates but expressed per month.

So I have Referral Date and Assessment Date. If the referral date was 4/9/2009 and 5/31/2009 then this would show in the total column as 52 days and 21 days in April and 31 days in March. If the referral date was February 1, 2010 and no assessment date has been entered yet then as of today's date that is a total of 46 days - 28 in February and 18 in March.

How would I build the report to calculate days per month specific to the current fiscal year? Thanks.

 

[dhookom]

I would start by creating a calendar table [tblDates] with at least a primary key field [TheDate] with every date you need from past to future.

Your query might then look like:

SELECT Year([TheDate]) AS Yr, Month([TheDate]) AS Mth, Count(YourUnnamedTable.VisitID) AS Visits
FROM YourUnnamedTable, tblDates
WHERE (((tblDates.TheDate) Between [Referral Date] And Nz([Assessment Date],Date())))
GROUP BY Year([TheDate]), Month([TheDate]);

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

Strictly counting total elapsed days is easily accomplished with 'dateDiff' (see HELP for the details). PLEASE read the help carefully, as there are some nuances which, while quite resonable, may throw an occassional discreapncy into what might be expected.

In the more general business sense, one usually needs to account for (e.g exclude) 'working days' and 'hoidays' from the calculations. When this is the case, there are a variety of approaches to consider, including the table of dates which includes the necessary attributes to filter the data to accomoodate the specifics of the inqurie(s).



MichaelRed