Counting Records/Limiting

[Vel.]

Hello

I have a database for tracking payments and charges for a small clinic. I need to print on claim forms, which can have six lines of activity on each form. Because I can't modify the size or location of any of the data on the form, I had to substitute a subform where I would generally just place the detail section (since multiple records will place the header/footer in a different place). Here's the explanation of my question

The main query includes records by ClientID, ProviderID, and AuthorizationN
The query for the subform includes records by ClientID, ProviderID, AuthorizationNo, and ActivityI

I need to add another field (call it PageCount) attached to each activityID. I need every six records by Client, Provider, and AuthorizationNo to have a new PageCount value. For instance

ClientID ProviderID AuthorizationNo ActivityID PageCoun
1211 68 00444 1233
1211 68 00444 1234
1211 68 00444 1235
1211 68 00444 1237
1211 68 00444 1240
1211 68 00444 1241
1211 68 00444 1249
1211 68 00444 1350
4898 101 03SF7 1898
4898 101 03SF7 1925
..

I could then add this value to each query, and join them by it to force a new page for every six lines of activity. If I can't do this I will have to break it down by month or some other value, which will force only one or two lines per page, which is too small a number.

 

[Gary Walter]

Hi Vel,

I suspect (rightly or wrongly) you are not using
full power of Access report.

A page header and footer (with nothing in it) can be made
as big as you need to give Detail section only so much space
in middle of page....so you only get 6 records on a page.

You can set up a group footer for

[ClientID] & [ProviderID] & [AuthorizationNo]

and set its "Force New Page" to "After Section"

so will start new page when encounter new "group."

So it is possible to tweak heights of

page header
Detail
page footer
(page margins in Page Setup)

to always get no more than 6 records per page
and force new page when get new group.

Please respond back if I have misunderstood.

Good luck,

Gary Walter

Hello,

I have a database for tracking payments and charges for a small clinic. I need to

print on claim forms, which can have six lines of activity on each form. Because I
can't modify the size or location of any of the data on the form, I had to substitute
a subform where I would generally just place the detail section (since multiple
records will place the header/footer in a different place). Here's the explanation
of my question:

The main query includes records by ClientID, ProviderID, and AuthorizationNo
The query for the subform includes records by ClientID, ProviderID,

AuthorizationNo, and ActivityID

I need to add another field (call it PageCount) attached to each activityID. I

need every six records by Client, Provider, and AuthorizationNo to have a new
PageCount value. For instance:

ClientID ProviderID AuthorizationNo ActivityID PageCount
1211 68 00444 1233 1
1211 68 00444 1234 1
1211 68 00444 1235 1
1211 68 00444 1237 1
1211 68 00444 1240 1
1211 68 00444 1241 1
1211 68 00444 1249 2
1211 68 00444 1350 2
4898 101 03SF7 1898 1
4898 101 03SF7 1925 1
...

I could then add this value to each query, and join them by it to force a new page

for every six lines of activity. If I can't do this I will have to break it down by
month or some other value, which will force only one or two lines per page, which is
too small a number.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

There is an MS Knowledge Base article that will show how to get X number
of lines per group on a report. Perhaps you can change it to your
needs.

ACC: How to Print a Constant Number of Lines Per Group

http://support.microsoft.com:80/support/kb/articles/Q119/0/73.asp

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIAkP4echKqOuFEgEQKUUACfbpoZH76t5Wo5JhE0LQvB4w2F1qMAoNz7
POI7YBb4fT6ZMDmysVXHtbJt
=xE/n
-----END PGP SIGNATURE-----

 

[Vel]

I actually never considered the page header/footer section for some reason, but there is still a problem. I can't get the sum of each page in the footer. I can easily get a sum for a group, but if there are eight lines I get the sum of all eight on both pages as opposed to 6 on one and 2 on the other.

 

[Gary Walter]

I actually never considered the page header/footer section for some reason, but

there is still a problem. I can't get the sum of each page in the footer. I can
easily get a sum for a group, but if there are eight lines I get the sum of all eight
on both pages as opposed to 6 on one and 2 on the other.

Hi Vel,

I had to design a report that had
to look just like a State report. It
had to have so many "Detail" lines
per page....AND it had to provide
page totals in the page footer.

I used the technique I referred to
previously, and for the page totals,
I used Allen Browne's method:

http://members.iinet.net.au/~allenbrowne/ser-11.html


Thank you Allen.