 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / June 2006Report Page(s) Count
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 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 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, > [quoted text clipped - 23 lines] >> >> Dean 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 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 Ismail >Make the following table in your database > [quoted text clipped - 65 lines] >>> >>>>Dean Worked. You're genius. Thank you very much. Ismail >Make the following table in your database > [quoted text clipped - 65 lines] >>> >>>>Dean 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 [quoted text clipped - 69 lines] >> >>>>>Dean |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.