Results 1 to 5 of 5
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Calculated Field

    Hello, need some advise:

    Working on Canadian Gazettes which are in a PDF format, have a structured filename and number some 14700. The filenames do give the of issue date, but NO indication of page number. Typical on the 2nd page is a page number, (2, ii, or carried on from the last issue (typical)) but being up to 150 years old some are unreadable. Each issue averages 43 pages, but not all are complete as some have duplicated and/or missing pages, and there are missing issues. I have not found an easy method of identifying which issues carry on the numbering sequence, so the last/next issue can be a guess. Typically, there are 70 issues in a year with at least 52 using the sequence.

    Currently I do not think I need 2/3s of these, but the annual indexes list page numbers for indexed items that I am interested in, so I need page numbers to find the PDF file. Because of the missing/extra pages I acknowledge that I will not be able get the PDF to open to the right page.

    What I need a structure/method that allows me to find the issue/PDF page given a page number and will adjust as corrected page numbers are entered. I also understand that including calculated fields can create problems.
    What I Have now is:
    StartPage Integer Actual first page number.
    NumberofPages Byte That are in the PDF, as given by a PDF Summary report
    Correction Byte A value (Typical is 0), that sums the missing or duplicate pages.

    StartPage can be calculated by the summing these three fields for last “standard” issue.
    Correction can be calculated by next issues StartPage minus this issues StartPage and NumberofPages.

    Is there a better way of doing this, then marking which issues have checked data and recalculating the issues after the now corrected issue?

    Thanks for looking Neil

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Number of pages in PDF is not relevant to the published page numbering.

    How would code know how to 'adjust' an input page number to correspond with PDF paging? Depends whether missing pages are before or after the requested page as well as adjust for page number scheme (roman numerals for TOC, then Arabic for narrative pages?) and any blank/unnumbered pages.

    IMO, there is no 'magic' algorithm that will get what you want. Advise just to open desired issue by issue date and user finds page. Less than 50 pages is not too burdensome to search through.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Thanks June7;
    Trying hard to avoid opening each one (ref 2/3 unneeded and the number of). In the first ten Years (184x), most are 1,2 Page corrections being blank pages with one being 45 pages.
    So are you suggesting enter a guess in startingpage, drop the other 2 fields, and update startingpage as needed, the H with the holes?
    Thanks Neil

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The index you refer to has issue date? And date is in PDF name? My suggestion is to just open the PDF based on issue date and let user find page. If you want to save a starting page number in data and try to use that to calculate a guesstimate for PDF page corresponding to specified page, go ahead. Just make sure users understand what is happening.

    And the starting page number for each issue record can be entered as a calculated guesstimate for initial population of field. Probably use VBA looping through recordset. However, I expect this calculation would get out of sync rather quickly because of issues identified in earlier post. Future updates would be manual edits of specific records.

    Just read your OP again. So this index does NOT have issue date nor issue number just page numbers and you must rely on page number to locate correct issue? ~14700 issues? Bummer!
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Index’s are by year with a date of yyyy0000, and issue’s are dated like yyyymmdd. There are about 12 different types of Issues, most in the sequence, but not all (I have manual look at those). So the index will list “Appointments” with page numbers afterward covering the year.
    I think you are right, guesstimate the best I can get without a pile of work and using two fields, StartingPage and Actual (T/F), will allow for revaluation as needed.

    Thanks again Jane7

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-09-2019, 05:38 PM
  2. Replies: 1
    Last Post: 12-10-2017, 01:47 AM
  3. Replies: 1
    Last Post: 05-22-2017, 05:59 AM
  4. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  5. Replies: 2
    Last Post: 12-15-2013, 02:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums