Report Page(s) Count

[ielmrani via AccessMonster.com]

Hi,
Can anyone help me on how to do the following:

I have a report grouped by Hospital name (total report 20 Pages).
Let say Page 1 for HopitalX, page 2 and 3 for HopspitalY Page 4 for HospitalZ
and pages 5 to 20 for
HospitalT
I would like a query or table to list page numbers based on the report like
this:

HospitalName Beg Page End Page
HospitalX 1 1
HospitalY 2 3
HospitalZ 4 4
HospitalT 5 20

Is it possible? Thanks in advance

Dean

 

[Michel Walsh]

Hi,



SELECT hospital, MIN(page), MAX(page)
FROM myData
GROUP BY hospital


assuming the table (query) myData has two fields: hospital and page, like:

hospital page
HospitalX 1
HospitalY 2
HospitalY 3
HospitalZ 4
HospitalT 5
HospitalT 6
HospitalT 7
.... and so on
HospitalT 20



Hoping it may help,
Vanderghast, Access MVP

 

[ielmrani via AccessMonster.com]

Hi Mchel,
Thanks for your reply,
i did not get any answers so I started to think that this can't be done.
You suggetion did not work for me. I have a report grouped by hospital name
but I do not have a table or query to list the hospital name and pages.
you sql is pulling data from tabel/query myData. The page number should be
based on the report.
Thanks

Hi,

SELECT hospital, MIN(page), MAX(page)
FROM myData
GROUP BY hospital

assuming the table (query) myData has two fields: hospital and page, like:

hospital page
HospitalX 1
HospitalY 2
HospitalY 3
HospitalZ 4
HospitalT 5
HospitalT 6
HospitalT 7
... and so on
HospitalT 20

Hoping it may help,
Vanderghast, Access MVP

Hi,
Can anyone help me on how to do the following:

[quoted text clipped - 17 lines]

Dean

 

[strive4peace]

Make the following table in your database

*RptPages*
Hospital, text
FirstPage, integer
LastPage, integer

make a unique index on Hospital

NOTE: Even better to use HospitalID if you have it

On your report, make the following textbox control:
Name--> PageNo
controlSource --> =Page

in the ReportHeader section OnFormat event, clear records
that were already in the RptPages table

dim s as string
s = "DELETE * FROM RptPages;"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

then, in the OnFormat event of each horpital group header
section

dim s as string
s = "INSERT INTO RptPages " _
& " (Hospital, FirstPage) "
& " SELECT '" & me.hospital_controlname _
& "', " & me.pageNo & ";"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

then, in the OnFormat event of each horpital group footer
section

dim s as string
s = "UPDATE RptPages " _
& " SET LastPage = "
& me.pageNo _
& " WHERE Hospital ='" _
& me.hospital & "';"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

Now your table will be populated when you get to the report
footer...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hi Mchel,
Thanks for your reply,
i did not get any answers so I started to think that this can't be done.
You suggetion did not work for me. I have a report grouped by hospital name
but I do not have a table or query to list the hospital name and pages.
you sql is pulling data from tabel/query myData. The page number should be
based on the report.
Thanks

Hi,

SELECT hospital, MIN(page), MAX(page)

FROM myData

GROUP BY hospital

assuming the table (query) myData has two fields: hospital and page, like:

hospital page
HospitalX 1
HospitalY 2
HospitalY 3
HospitalZ 4
HospitalT 5
HospitalT 6
HospitalT 7
... and so on
HospitalT 20

Hoping it may help,
Vanderghast, Access MVP

Hi,
Can anyone help me on how to do the following:

[quoted text clipped - 17 lines]

Dean

 

[ielmrani via AccessMonster.com]

Sorry to reply this late, but I had no idea you replied. To honest with you
I gave up on this issue. I'll try your suggestion and I'll let you know.

Thanks

Make the following table in your database

*RptPages*
Hospital, text
FirstPage, integer
LastPage, integer

make a unique index on Hospital

NOTE: Even better to use HospitalID if you have it

On your report, make the following textbox control:
Name--> PageNo
controlSource --> =Page

in the ReportHeader section OnFormat event, clear records
that were already in the RptPages table

dim s as string
s = "DELETE * FROM RptPages;"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

then, in the OnFormat event of each horpital group header
section

dim s as string
s = "INSERT INTO RptPages " _
& " (Hospital, FirstPage) "
& " SELECT '" & me.hospital_controlname _
& "', " & me.pageNo & ";"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

then, in the OnFormat event of each horpital group footer
section

dim s as string
s = "UPDATE RptPages " _
& " SET LastPage = "
& me.pageNo _
& " WHERE Hospital ='" _
& me.hospital & "';"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

Now your table will be populated when you get to the report
footer...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hi Mchel,
Thanks for your reply,

[quoted text clipped - 35 lines]

 

[ielmrani via AccessMonster.com]

Worked. You're genius. Thank you very much.
Ismail

Make the following table in your database

*RptPages*
Hospital, text
FirstPage, integer
LastPage, integer

make a unique index on Hospital

NOTE: Even better to use HospitalID if you have it

On your report, make the following textbox control:
Name--> PageNo
controlSource --> =Page

in the ReportHeader section OnFormat event, clear records
that were already in the RptPages table

dim s as string
s = "DELETE * FROM RptPages;"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

then, in the OnFormat event of each horpital group header
section

dim s as string
s = "INSERT INTO RptPages " _
& " (Hospital, FirstPage) "
& " SELECT '" & me.hospital_controlname _
& "', " & me.pageNo & ";"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

then, in the OnFormat event of each horpital group footer
section

dim s as string
s = "UPDATE RptPages " _
& " SET LastPage = "
& me.pageNo _
& " WHERE Hospital ='" _
& me.hospital & "';"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

Now your table will be populated when you get to the report
footer...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hi Mchel,
Thanks for your reply,

[quoted text clipped - 35 lines]

 

[strive4peace]

you're welcome, Ismail happy to help

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Worked. You're genius. Thank you very much.
Ismail

Make the following table in your database

*RptPages*
Hospital, text
FirstPage, integer
LastPage, integer

make a unique index on Hospital

NOTE: Even better to use HospitalID if you have it

On your report, make the following textbox control:
Name--> PageNo
controlSource --> =Page

in the ReportHeader section OnFormat event, clear records
that were already in the RptPages table

dim s as string
s = "DELETE * FROM RptPages;"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

then, in the OnFormat event of each horpital group header
section

dim s as string
s = "INSERT INTO RptPages " _
& " (Hospital, FirstPage) "
& " SELECT '" & me.hospital_controlname _
& "', " & me.pageNo & ";"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

then, in the OnFormat event of each horpital group footer
section

dim s as string
s = "UPDATE RptPages " _
& " SET LastPage = "
& me.pageNo _
& " WHERE Hospital ='" _
& me.hospital & "';"
currentdb.execute s
currentdb.tabledefs.refresh
DoEvents

Now your table will be populated when you get to the report
footer...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hi Mchel,
Thanks for your reply,

[quoted text clipped - 35 lines]

Dean