Hi Sheriff,
OK. With the details sorted, here's one way to do it. I'll describe
this
step-by-step in detail, so you can adapt later if it doesn't suit
exactly.
First, I set up a function (in a module, with the module name not the
same
as the function name) to return the first of the month, so that the
DateOpen
and DateClosed fields can be standardised. Doing it this way, rather
than
converting to a month-year string via a Format function, allows greater
than/less than criteria and sorting by dates to work.
Public Function FirstOfMonth(InputDate As Variant)
' Return the first day of the month of the input date, in "mm/dd/yyyy"
format
Dim D As Integer, M As Integer, Y As Integer
If Not IsDate(InputDate) Then
FirstOfMonth = Null
Else
D = Day(InputDate)
M = Month(InputDate)
Y = Year(InputDate)
'conversion to deal with difference between system default
'and SQL requirement for mm/dd/yyyy format
FirstOfMonth = CDate(Format(DateSerial(Y, M, 1), "mm/dd/yyyy"))
End If
End Function
Note: this function is almost the same as that written by Lewis
Moseley,
available at The Access Web
http://www.mvps.org/access/datetime/date0007.htm It differs in the
declaration of the InputDate variable, to prevent it returning #error
when
InputDate is null. It also copes with the fact that I am developing
with
default date format of dd/mm/yyyy, and SQL strings require/assume
mm/dd/yyyy. For the US default date format of mm/dd/yyyy, you will
probably
find that you can change this to:
FirstOfMonth = DateSerial(Y, M, 1)
The following query, set to prompt for year and month, will return all
Open
investigations for that month-year. I'm using tblInvestigations as the
source table, and I'm showing the ID number (an autonumber in my test
data),
the month-year entered (again, as a date in the FirstOfMonth form), and
a
Status field:
SELECT FirstOfMonth([DateOpen]) AS Opened,
FirstOfMonth([DateClosed]) AS Closed,
ID AS InvestigationID,
DateSerial([Enter Year],[Enter Month (1-12)],1) AS StatusMonth,
"Open" AS Status
FROM tblInvestigations
WHERE (((FirstOfmonth([DateOpen]))<=DateSerial([Enter Year],
[Enter Month (1-12)],1)) AND ((FirstOfMonth([DateClosed])) Is
Null))
OR (((FirstOfmonth([DateOpen]))<=DateSerial([Enter Year],[Enter
Month (1-12)],1))
AND ((FirstOfMonth([DateClosed]))>DateSerial([Enter
Year],[Enter
Month (1-12)],1)))
OR (((FirstOfmonth([DateOpen]))=DateSerial([Enter Year],[Enter
Month
(1-12)],1))
AND ((FirstOfMonth([DateClosed]))=DateSerial([Enter
Year],[Enter
Month (1-12)],1)));
The following query, with the same prompts for year and month, will
return
all Closed investigations for the month-year.
SELECT FirstOfMonth([DateOpen]) AS Opened,
FirstOfMonth([DateClosed]) AS Closed,
ID As InvestigationID,
DateSerial([Enter Year],[Enter Month (1-12)],1) AS StatusMonth,
"Closed" AS Status
FROM tblInvestigations
WHERE (((FirstOfMonth([DateClosed]))=DateSerial([Enter Year],[Enter
Month (1-12)],1)));
These can be combined in a Union query to produce the status of all
open and
closed investigations for a given month-year.
To produce a dataset suitable for use as input to a totals query to
generate
the chart, for a user-selected number of months in the past from the
current
date, I'd generate and run this query from VBA code (called here from a
command button, but you could put it wherever is suitable) in a loop to
write records to a table (I'll call it tblInvStatus), which contains
the
following fields (not all are required for the chart you want):
Opened - Date/Time
Closed - Date/Time
InvestigationID - Number (long integer)
StatusMonth - Date/Time
Status - Text
Here's some code which fill tblInvStatus with values for the number of
months selected (in this example, via an InputBox). This is
bare-bones,
with no error-trapping; add/adapt/modify to suit your needs:
Private Sub cmdStatus_Click()
Dim strSQL As String
Dim intMonths As Integer
Dim i As Integer
Dim rst As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim dteStatusMonth As Date
intMonths = InputBox("Enter the number of months:")
'empty the temporary table
strSQL = "DELETE * FROM tblInvStatus"
DoCmd.RunSQL strSQL
For i = 0 To (intMonths - 1)
'Generate the StatusMonth
dteStatusMonth = DateAdd("m", -i, Date) 'unary minus, to move
backward
'convert to FirstOfMonth form
dteStatusMonth = DateSerial(Year(dteStatusMonth),
Month(dteStatusMonth),
1)
'build query string
strSQL = "SELECT FirstOfMonth([DateOpen]) AS Opened,
FirstOfMonth([DateClosed]) AS Closed, " _
& "ID AS InvestigationID, #" & dteStatusMonth & "# AS
StatusMonth, 'Open' AS Status " _
& "FROM tblInvestigations WHERE " _
& "(FirstOfMonth([DateOpen]) <= #" & dteStatusMonth & "#
And
FirstOfMonth([DateClosed]) Is Null)" _
& " Or (FirstOfMonth([DateOpen]) <= #" & dteStatusMonth &
"# And
FirstOfMonth([DateClosed])> #" & dteStatusMonth & "#)" _
& " Or (FirstOfMonth([DateOpen]) = #" & dteStatusMonth & "#
And
FirstOfMonth([DateClosed]) = #" & dteStatusMonth & "#)" _
& " UNION ALL SELECT FirstOfMonth([DateOpen]) AS Opened,
FirstOfMonth([DateClosed]) AS Closed, " _
& "ID AS InvestigationID, #" & dteStatusMonth & "# AS
StatusMonth, 'Closed' AS Status " _
& "FROM tblInvestigations WHERE FirstOfMonth([DateClosed])
= #"
& dteStatusMonth & "#;"
'open recordset based on this SQL string
Set rst = CurrentDb.OpenRecordset(strSQL)
'open recordset for temporary table
Set rstNew = CurrentDb.OpenRecordset("tblInvStatus")
With rst
'loop through records from the strSQL recordset
Do While Not rst.EOF
'add record to table recordset, set values
rstNew.AddNew
rstNew!Opened = !Opened
rstNew!Closed = !Closed
rstNew!InvestigationID = !InvestigationID
rstNew!StatusMonth = !StatusMonth
rstNew!Status = !Status
'write to table
rstNew.Update
'move to next record in strSQL recordset
.MoveNext
Loop
End With
'Close both recordsets
rst.Close
rstNew.Close
Next i
Set rst = Nothing
Set rstNew = Nothing
End Sub
After that runs, you can query the tblInvStatus table like this:
SELECT StatusMonth, Status, Count(Status) AS StatusCount
FROM tblInvStatus
GROUP BY StatusMonth, Status
ORDER BY StatusMonth;
Or, perhaps more usefully for a datasource for a chart, with a crosstab
query such as:
TRANSFORM Count(Status) AS StatusCount
SELECT StatusMonth
FROM tblInvStatus
GROUP BY StatusMonth
ORDER BY StatusMonth
PIVOT Status;
HTH,
Rob
If opened/closed in same month, then it would count as open and as
closed
for
that month. It wouldn't matter whether it was done all through
queries or
some code, as long as the solution was flexible enough to allow
changes in
the range (i.e. 6 months vs. 3 months.).
Thanks again for any help in you can provide
Sheriff
:
And a couple more questions:
How do you want to treat an investigation which is both opened and
closed
in
the same month? Should it count for both, or only for Closed?
Do you want to do this only via a query (or queries), or would code
which
processes data and writes the fianl dataset to a table be an
acceptable
solution?
Rob
message
Hi Sheriff,
No answers yet; just a few questions to define the situation a
little
better - then hopefully something useful ;-)
Does your cumulative total need to run forever, or only show data
for
the
last 6 months?
I assume that DateOpen and DateClosed are DateTime datatypes; is
this
correct? If not, what are they, and what is the exact format of
the
data
stored in them?
I assume that an investigation is open if the DateClosed field is
null;
is
this correct? If not, what determines whether an investigation is
open
or
closed?
Rob
I have a database of investigations that are open and closed.
I am having trouble creating a query/chart that is able to run a
cumulative
total (running total) for all open investigations. So the chart
will
keep
count of the open investigations until they close, then they are
considered
closed.
open = "open" from last month + "opened" in current month -
"closed"
in
current month
closed = closed in current month
*opened (will NOT need to show up on the chart) = "opened" in
current
month
Is there a way to code to automate this process where Access will
keep
track
of a running total of "open" investigations and "closed"
investigations?
This database requires a "running total" of "open" investigations
and
"closed" investigations in the past six months. Thanks for any
help
you
can
provide.
An investigation is considered "closed" in the month from
"DateClosed"
An investigation is considered opened in the month from the
"DateOpen"
and
remains "open" until the month before it is "closed". For
example, an
investigation opened in April 2007 and closed in August, would be
"open"
in
April, May, June, July and then "closed" in August and no longer
will
appear
on the chart.
I realize that Dsum function and a crosstab query would need to
be
used,
but
I do not know how to even begin. Thanks.
