B
Brigitte P
I'm in over my head and need much help. Example of data
EventDate Event Injury Unit
5/2/07 Aggression Scratch 1W
4/1/07 Fall Bruise 1W
6/5/07 Fall None GF
My user wants to be able to select the type(s) of Events, type(s) of
Injuries and possible Unit(s) via a multiselect listbox and see data with
some averages on a rolling basis. I have created the unbound form for this
with the appropriate listboxes and data selection boxes.
The data then needs to go into a report showing as follows:
Event in a header (I think I can do this by sorting and creating Headers for
each event). Whether there was an injury or not comes out of the multiselect
box but should someplace show up, something like "If Injury = "None" then ""
else "with injuries" - this is preferrably also in the header. I think I
could figure this out.
Next we need in the body of the report in horizontal fashion.\:
Previous 6 month average, previous 12 month average, 2007 - 04, 2007 - 05,
2007 - 06 etc.
The previous 6 or 12 mo avg is calculated from the first date selected in
the unbound form (which also has the multiselect boxes).
Example of Report:
Falls with Injuries
Prev. 6 Prev. 12 2007-04 2007-05 2007-06
1.3 0.8 1 0
2
I have accomplished the queries by doing first a query that calculates the 6
or 12 mo. averages
Then I did a crosstap that gives the numbers for the selected dates by month.
I then created a third query that takes both sets of the above queries
(crosstab and select) linked by unit and event to show exactly what the user
wants.
My problem is as follows:
1. I did a report with dynamics fields based on kb328320 (even though the
underlying query is now a select query based on the crosstab and another
select query), because the columns from the crosstab can change based on the
dates selected. When the code (copied from the kb 328320) encounters Set
rstReport = qdf.OpenRecordset() it stops. I have the DOA3.6 library selected.
Don't know if the code would go any further.
2. Don't know how to get the selection from the 2 or possibly 3 multiselect
listboxes into the report. I have tried various codes I found, one from
"Fixing Access Annoyances" - great book) and another from
http://allenbrowne.com/ser-50.html but can't get it to work, especially how
to do the selection from more than one listbox.
I'm in way over my head, and maybe there is an easier way to accomplish what
In need to do. Can anyone help with this.
Thanks as always.
Brigitte P
EventDate Event Injury Unit
5/2/07 Aggression Scratch 1W
4/1/07 Fall Bruise 1W
6/5/07 Fall None GF
My user wants to be able to select the type(s) of Events, type(s) of
Injuries and possible Unit(s) via a multiselect listbox and see data with
some averages on a rolling basis. I have created the unbound form for this
with the appropriate listboxes and data selection boxes.
The data then needs to go into a report showing as follows:
Event in a header (I think I can do this by sorting and creating Headers for
each event). Whether there was an injury or not comes out of the multiselect
box but should someplace show up, something like "If Injury = "None" then ""
else "with injuries" - this is preferrably also in the header. I think I
could figure this out.
Next we need in the body of the report in horizontal fashion.\:
Previous 6 month average, previous 12 month average, 2007 - 04, 2007 - 05,
2007 - 06 etc.
The previous 6 or 12 mo avg is calculated from the first date selected in
the unbound form (which also has the multiselect boxes).
Example of Report:
Falls with Injuries
Prev. 6 Prev. 12 2007-04 2007-05 2007-06
1.3 0.8 1 0
2
I have accomplished the queries by doing first a query that calculates the 6
or 12 mo. averages
Then I did a crosstap that gives the numbers for the selected dates by month.
I then created a third query that takes both sets of the above queries
(crosstab and select) linked by unit and event to show exactly what the user
wants.
My problem is as follows:
1. I did a report with dynamics fields based on kb328320 (even though the
underlying query is now a select query based on the crosstab and another
select query), because the columns from the crosstab can change based on the
dates selected. When the code (copied from the kb 328320) encounters Set
rstReport = qdf.OpenRecordset() it stops. I have the DOA3.6 library selected.
Don't know if the code would go any further.
2. Don't know how to get the selection from the 2 or possibly 3 multiselect
listboxes into the report. I have tried various codes I found, one from
"Fixing Access Annoyances" - great book) and another from
http://allenbrowne.com/ser-50.html but can't get it to work, especially how
to do the selection from more than one listbox.
I'm in way over my head, and maybe there is an easier way to accomplish what
In need to do. Can anyone help with this.
Thanks as always.
Brigitte P