Simon Matthews wrote:
Hope someone can help an Access beginner!
I've just started keeping my surgical logbook on access and it's a
simple flat-file affair. I have created several queries that will
list cases performed at different hospitals and reports based on the
queries to print out the relavent details.
What I would like to do is have a summary sheet in the Report Footer
section that lists a grid of each type of procedure performed as well
as the degree of involvement (1 to 4, depending on how much assistance
was required). I have tried using unbound text boxes and playing
around with the ControlSource doohickey to no avail. I thought it
would be something like:
=count([procedure]="Hip Arthroplasty" AND [code]="1"), repeated for
all of the different boxes with different values of procedure and
code....
...but I can't figure the exact syntax - dcount seems even more
difficult. I've tried my access book and various ng's/knowledge base
with no success.
I am sure there must be a simple answer but I'm going around in
circles - any help would be gratefully received!
Thanks in advance.
Simon Matthews
Dcount() is OK to get counts. Just remember if records don't exit,
DCount() returns null. So you might want to enter
NZ(Dcount("FieldNameToCount","Table","YourFilterCl ause"),0)
But it the grand scheme of things you need to hardcode the whole thing
and then the report turns into a PITA everytime something new comes
about, and then you spend time programming an Access report instead of
operating and filling out medical forms and your nurse quits because you
don't work on patients but computerprograms so...
I would suggest you create another report that lists the procedures and
counts. You can do this with a totals query as the report's recordsource.
I'm not sure of your skill level or how you are calling the report. I
usually call reports from a form. The form usually contains fields I
will be filtering on. Then in the OnOpen event of the report I might
set my filter. Ex:
Sub Report_OnOpen
Me.Filter = "Between #" & Forms!CallingForm!FromDate & "# And #" &
Forms!CallingForm!ToDate & "# And Code >=1 And Code <= 4"
Me.FilterOn = True
Or in the calling form, I might add a field and set it invisible. When
I press the button to call/open the report, I might create the filter. Ex:
Sub CommandPrint_Click
Me.FilterValue = "Between #" & Me!FromDate & "# And #" & Me.ToDate &
"# And Code >=1 And Code <= 4"
Docmd.OpenReport "YourReport"
End Sub
and in the OnOpen event enter
Me.Filter = Forms!CallingFormName!FilterValue
Me.FilterOn = True
OK, you can now call the filter for this sub report that lists your
summary. Now open the main report. Click on the sub-report control in
the toolbox and drop that in the ReportFooter band. Make sure the
ReportFooter band can grow (dbl-click on the footer band, view the
properties). BTW, there is no master/child link in your case.
What I usually do on a case like yours is to create the main report on a
query. I then add another column Ex:
Master : "M"
Now I open up the main report and from the menu press View/Sort&Group.
I create a group called Master and I slide it up to the top....it
becomes the first group. Then I tell Access that Master would like a
footer. And then I drop the subreport into that band, not the report
footer. Report and page footers print at the bottom of a page. I like
to keep my extra data near the area where the last record printed.
Good luck.
