Need a nudge in the right direction, please.....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2003.
I need to create a report from a database table I use for tracking Patient
Care Charts for an ambulance service.

I need to create a report for each EMT that has written a Patient Care Chart
within a user-specified time period. Preferrably, with a single button click
(single macro).

The variables I expect that I will need to be using are: "MedicName"
"StartDate" and "EndDate". "MedicName" may occur in 3 different fields in
each record as the medic who writes the chart, medic who starts IV#1 and
medic who starts IV#2.

I will need to create a list of "MedicName"s that have written a chart
between "StartDate" and "EndDate".

Then, for each "MedicName", find the number of times that "MedicName" has
started an IV. The "MedicName" who writes the chart may not be the
"MedicName" who starts the IV. That value will be displayed on the front
page of the report, along with some statistics.

Then, I need to create a query of all the charts written by "MedicName"
between "StartDate" and "EndDate".

I have a report that works (except for the # of IV starts) with a query
based on Allen Browne's date range instructions and a manually entered
"MedicName" for the medic who wrote the chart. Is it possible to have a
macro that passes the needed arguments to an existing query?

I know this is asking a lot, but I've never written a macro in access other
that Allen Browne's. I think that, if I had a push in the right direction, I
could figure out the rest of what I need. I haven't found much in the way of
"help" or "tutorial" assistance on writing macros. I just don't really know
where (or how) to get started....

Thanks in advance!!
 
Rich,

I don't think this is a job for a macro.

If you have a form, which will be open at the time that the report is
created, with 2 textboxes (for the StartDate and EndDate), and a
combobox (to select the required MedicName). You can then reference
these form controls in the criteria of the query that the report is
based on. For example, in the criteria of the date field of the query,
you would have something like this:
Between [Forms]![NameOfForm]![DateFrom] And [Forms]![NameOfForm]![DateTo]
.... and in the criteria of the MedicName column in the query, something
like this...
[Forms]![NameOfForm]![NameOfCombobox]

As regards the specific question about the number of IV Starts, I expect
an easy way to do this would be to put an unbound textbox on the report,
and then in its Control Source property you enter an expression using a
DCount() function. For example, it might be like this...
=DCount("*","NameOfYourTable","[StartIV1]='" &
[Forms]![NameOfForm]![NameOfCombobox] & "' And [ChartDate] Between #" &
[Forms]![NameOfForm]![DateFrom] & "# And #" &
[Forms]![NameOfForm]![DateTo] & "#")
 
Back
Top