Emilia,
Thank you so much for your thoughtful answer. It really got me started.
This is what I have so far. However I have run into 2 issues at this point:
First:
When I attempt to use the QueryDef Method as in "Dim qdf As QueryDef",
Access for somereason does not want to recognize it. Does this have to do wi
DAO or OAD vs Jet? Please advise?
Second.
The bigger issue is still handling the data. I was able to construct the
SQL statement in a Query Window and was able to get close to what I wanted,
except that I had null values in the rows when no date had been listed in
the underlying table.
tblMilestonesDates 1 5 (Where column "1" = Admissions and column
"5" = Discharges
_______________________
11/5/02 1
11/6/02 2
11/5/02 1
11/5/02
11/5/02 1 1
To try to get arround all of these nulls I, created a table called
"Census{Perm)" into which I can dump the values with append queries and then
update the nulls to zeros with Update queries for columns "1" & "5"
Afterwards I will need to delete all the date from "Census{Perm)" so it will
be ready for the next cycle,
Even if this all is correct, which I doubt, I still need to find a way to
programatically pass the data to a graph embedded in a report.
As you can see, I barely know what I am doing. I am sure that thereis
better way to do this, but I help.
Again thanks
Christopher
------------------------------------
CODE ------------------------------------
Sub GetCensus()
On Error GoTo GetCensus_Err
Dim qdf As QueryDef
Dim Census As Recordset
Dim SQLStmt1 As String
Dim SQLStmt2 As String
Dim SQLStmt3 As String
Set dbs = CurrentDb
dbs.Execute SQLStmt1 = "INSERT INTO [Census{Perm)] ( CompleteionDate, 1,
5 )SELECT [qry Hospital Census].CompleteionDate, [qry Hospital Census].[1],
[qry Hospital Census].[5] FROM [qry Hospital Census]"
dbs.Execute SQLStmt2 = "UPDATE [Census{Perm)] SET [Census{Perm)].[1] = 0
WHERE ((([Census{Perm)].[1]) Is Null))"
dbs.Execute SQLStmt3 = "UPDATE [Census{Perm)] SET [Census{Perm)].[5] = 0
WHERE ((([Census{Perm)].[5]) Is Null))"
'Or this?
' dbs.Execute SQLStmt = "TRANSFORM
Count(tblMilestonesDates.CompleteionDate)AS CountOfCompleteionDate SELECT
tblMilestonesDates.CompleteionDate FROM tblMilestonesDates WHERE
(((tblMilestonesDates.fMilestonID) = 1 Or (tblMilestonesDates.fMilestonID) =
5)) GROUP BY tblMilestonesDates.CompleteionDate PIVOT
tblMilestonesDates.fMilestonID INTO Census"
' Select all records in the Employees table and copy them into a new
table named Census.
' Set qdf = dbs.OpenRecordset(SQLStmt)
If Not qdf.EOF Then
MsgBox "Test"
End If
GetCensus_Err:
MsgBox "Error"
Exit Sub
'DoCmd.OpenReport "CensusReport", acViewNormal, , "[MyCodeFieldAdmission] =
True AND [MyCodeFieldDischarge] = False"
End Sub
Emilia Maxim said:
I have table of dates with associated milestone codes. One of the codes
refers to admissions and another to discharges.
By linking the table with itself, filtering on the these codes, and
performing a datediff() function in a select query on the dates, I can
calculate things like average, max, min, s.d. on the length of stay etc.
Now I would like to create a running daily census graph by date to embed
into a report. My guess is I will need to use an Array to:
1. handle all the calculations to get the instantaneous census (people
admitted but not discharge - based on the date) and
2. then temporarily store this data, and then,
3. associate these calculated values with the dates in order to graph
them.
Additionally, while it may be possible for a clever set of queries to pull
this off, it would be efferent enough for a report.
Christopher,
if I understand correctly, you would need the number of people
admitted for a given date. You can easily have this with a grouping:
SELECT MyDate, Count([MyCodeField] As NoAdmissions FROM MyTable WHERE
[MyCodeFieldAdmission] = True AND [MyCodeFieldDischarge] = False GROUP
BY MyDate
You could set the RecordSource property of the report to this query.
You could also create the query without a WHERE clause and pass the
criteria when opening the report:
DoCmd.OpenReport "MyReport", acViewNormal, , "[MyCodeFieldAdmission] =
True AND [MyCodeFieldDischarge] = False"
As for graphs, I didn't work yet with them, but could be you'll need a
crosstab query.
HTH
Best regards
Emilia
Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
