Graphing in Access

[Ian]

I manage a db at work (hospital) that stores data on
patients including laboratory results. I display this
data (along with other data) on a form in the hospital db
but I'm interested to know if it is possible to do any
kind of graphing of such data and display it on my form
as well as add it to a report of the patients stay in the
hospital. I have been told that Access cannot graph and
I'd have to export to Excel, chart it and then re-import
the graph which seems a bit of a long route?

Thanks for any suggestions.
Ian.

 

[Dave M]

You certainly can create charts in Access, although they're not as
convenient to work with as in Excel. There's a lot of info in MSFT's
Knowledge Base, some of which may be useful. You could start here:

http://office.microsoft.com/en-ca/assistance/HP052803671033.aspx

 

[Ian]

Thanks for the advice, it certainly is a start and I'll
get going with that. Thanks again.

Ian.

 

[Arvin Meyer]

I'd use Excel for charts. It is so much nicer. That doesn't mean that you
shouldn't store your data in Access and use automation to display it in an
Excel chart. There's some sample automation code at the Access Web:

http://www.mvps.org/access/modules/mdl0006.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Ian]

Arvin

Thanks for the link. My programming skills are not as good
as they could be and so I'm not sure if the code you
describe sits in Access or Excel. Also can you give me
some pointers on how to take stored data in Access and
then move it into Excel and then graph it. I realsie this
may be too much to detail in this forum but a few pointers
would be really helpful.

Cheers,
Ian.

 

[Arvin Meyer]

Sure Ian.

The code resides in Access. Here is another example, run from a command in
Access, button which builds a recordset from a crosstab query, then opens a
spreadsheet in Excel and dumps the results into cells which are part of an
Excel named range. It is this range upon which the chart is founded:

Private Sub cmdSubmitData_Click()

Dim appXL As Excel.Application
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim strPath As String

On Error GoTo Error_Handler

' Open the current database and audiogram query
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from qryData2Excel Where ID =" &
Me.txtID, dbOpenSnapshot)

Set appXL = New Excel.Application
Set wkb = appXL.Workbooks.Open("C:\MyExcel.xls")
Set wks = wkb.Worksheets(1)
appXL.Visible = True

With wks
'Create the Column Headings
.Cells(1, 1).Value = "ID"
.Cells(1, 2).Value = "Name"
.Cells(1, 3).Value = "StaffName"
.Cells(1, 4).Value = "ColA"
.Cells(1, 5).Value = "ColB"
.Cells(1, 6).Value = "ColC"
.Cells(1, 7).Value = "ColD"
.Cells(1, 8).Value = "ColE"
.Cells(1, 9).Value = "ColF"
.Cells(1, 10).Value = "ColG"
.Cells(1, 11).Value = "ExamDate"

'Fill Values
.Cells(2, 1).Value = rst!ID
.Cells(2, 2).Value = rst!FullName
.Cells(2, 3).Value = rst!StaffName
.Cells(2, 4).Value = rst![Field1]
.Cells(2, 5).Value = rst![Field2]
.Cells(2, 6).Value = rst![Field3]
.Cells(2, 7).Value = rst![Field4]
.Cells(2, 8).Value = rst![Field5]
.Cells(2, 9).Value = rst![Field6]
.Cells(2, 10).Value = rst![Field7]
.Cells(2, 11).Value = rst![DateField]

End With

DoEvents

Exit_Here:
Set wkb = Nothing
Set appXL = Nothing
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Ian]

Cheers Arvin,

Thanks for your valuable help,

Ian.

-----Original Message-----
Sure Ian.

The code resides in Access. Here is another example, run from a command in
Access, button which builds a recordset from a crosstab query, then opens a
spreadsheet in Excel and dumps the results into cells which are part of an
Excel named range. It is this range upon which the chart is founded:

Private Sub cmdSubmitData_Click()

Dim appXL As Excel.Application
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim strPath As String

On Error GoTo Error_Handler

' Open the current database and audiogram query
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from qryData2Excel Where ID =" &
Me.txtID, dbOpenSnapshot)

Set appXL = New Excel.Application
Set wkb = appXL.Workbooks.Open("C:\MyExcel.xls")
Set wks = wkb.Worksheets(1)
appXL.Visible = True

With wks
'Create the Column Headings
.Cells(1, 1).Value = "ID"
.Cells(1, 2).Value = "Name"
.Cells(1, 3).Value = "StaffName"
.Cells(1, 4).Value = "ColA"
.Cells(1, 5).Value = "ColB"
.Cells(1, 6).Value = "ColC"
.Cells(1, 7).Value = "ColD"
.Cells(1, 8).Value = "ColE"
.Cells(1, 9).Value = "ColF"
.Cells(1, 10).Value = "ColG"
.Cells(1, 11).Value = "ExamDate"

'Fill Values
.Cells(2, 1).Value = rst!ID
.Cells(2, 2).Value = rst!FullName
.Cells(2, 3).Value = rst!StaffName
.Cells(2, 4).Value = rst![Field1]
.Cells(2, 5).Value = rst![Field2]
.Cells(2, 6).Value = rst![Field3]
.Cells(2, 7).Value = rst![Field4]
.Cells(2, 8).Value = rst![Field5]
.Cells(2, 9).Value = rst![Field6]
.Cells(2, 10).Value = rst![Field7]
.Cells(2, 11).Value = rst![DateField]

End With

DoEvents

Exit_Here:
Set wkb = Nothing
Set appXL = Nothing
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Arvin

Thanks for the link. My programming skills are not as good
as they could be and so I'm not sure if the code you
describe sits in Access or Excel. Also can you give me
some pointers on how to take stored data in Access and
then move it into Excel and then graph it. I realsie this
may be too much to detail in this forum but a few pointers
would be really helpful.

Cheers,
Ian.
to
display it in an graph
and

.