A Tough One, Do I need to use an Arry to do this?

[Christopher Shanahan]

The problem:

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.

So 2 questions:

1. Am I barking up the correct tree?
2. If so does any have any ideas how to build such an array function in VBA?

Thanks
Christopher

 

[Michel Walsh]

Hi,


I would not use an array, I would use SQL statements.

You can get your data into an array with the GetRows function of a
recordset ( DAO or ADO). Just dim the variable as a dynamic array of
variants, and assign the result of the recordset GetRows to it. The result
is "transposed", ie, you get the n records by m fields into an array m by n.
( 0 to m-1 by 0 to n-1 ):


Dim x() As Variant
x=rst.GetRows()

Debug.Print x(0, 0)

You can use UBound (as usual, and LBound), to get the limits on the indices.
You do not SET x to rst.GetRows. x is an array of basic datatypes, not an
object.


Hoping it may help,
Vanderghast, Access MVP

 

[Christopher Shanahan]

Michel ,

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

 

[Michel Walsh]

Hi,


QueryDef is from DAO, you need a reference to DAO, "Microsoft DAO 3.6 Object
Library" (it is not the case, by default, with Access 2000).

You can use Nz(FieldName, 0), as in

SELECT Nz(FieldName, 0) FROM myTable


to replace the NULL with 0.



Hoping it may help,
Vanderghast, Access MVP

Michel ,

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

Hi,


I would not use an array, I would use SQL statements.

You can get your data into an array with the GetRows function of a
recordset ( DAO or ADO). Just dim the variable as a dynamic array of
variants, and assign the result of the recordset GetRows to it. The result
is "transposed", ie, you get the n records by m fields into an array m

by

n.
( 0 to m-1 by 0 to n-1 ):


Dim x() As Variant
x=rst.GetRows()

Debug.Print x(0, 0)

You can use UBound (as usual, and LBound), to get the limits on the indices.
You do not SET x to rst.GetRows. x is an array of basic datatypes, not an
object.


Hoping it may help,
Vanderghast, Access MVP


in
VBA?