Theo Todman's Web Page - Notes Pages
Website Documentation
Website Generator Documentation - Recalculation & Housekeeping
(Work In Progress: output at 19/10/2024 18:25:55)
This document covers the following functions performed by clicking buttons on the front screen:-
- Recalculation + Housekeeping (cmdRecalculate_Click)
To see the Code, click on the procedure name above.
Summary
- This procedure imports time from my timesheet system and applies it to the associated books and papers to which it applies.
- It also performs a large number of housekeeping functions, including maintaining the various legacy cross-reference tables which may or may not be required (for performance reasons).
- The processing takes quite a long time – about 3 minutes1 – and adds 250 Mb to the size of the MS Access database, which therefore needs compacting and repairing after a few runs.
- All this will be fully documented in due course. For now, I just list the processes or queries run, adding comments as necessary. Where these require further investigation they appear as footnotes. I doubt there’s much more to add, though headings to highlight the functions performed would be useful.
- There’s a separate section at the end2 for special processing for change of academic year.
Processes
- Import Aeon papers relevant to my Thesis into my Papers table. There’s special processing for time recorded in the previous Academic year. Queries:-
- Regenerate various tables needed for performance reasons:-
- Checks the Page_Regen table, and if more than 5 rows for Called_Type of ‘N’ (Note), asks the user whether they want this number of Notes regenerated. Stores the response in the variable ‘Question’ for later use. Default is vbYes. Note3.
- Oboe Practice Reporting: Run a bunch of queries that import data from my timesheet and maintain the table Oboe_Practice_Hours needed for producing Status: Oboe Practice4, before opening the query Oboe_Practice_Hours_List, which I copy into my timesheet spreadsheet.
- Checks are made to ensure the Titles of Notes, Archived Notes, Books and Papers don’t contain double-quotes (which cause MS Access queries to get confused), and stops the process if any are found.
- Checks that no Book Comments have “double-plus”-style (rather than +NN+-style) Note links, and stops the process if any are found. Note5.
- Check for dud abstracts in Books and Papers, using the following routines:-
→ WebpageGenDud_Abstracts_Books
→ WebpageGenDud_Abstracts_Papers
using the queries
→ Dud_Abstracts_Books
→ Dud_Abstracts_Papers
As part of these processes, the user is asked – for Papers only – whether they are intended. Basically, these routines are checking that colour conventions have been applied (unless the column ‘Abstract_Fixed?’ is ticked.
- Regenerate Time_By_Weekday_Prelist table, using Time_By_Weekday_Prelist_GEN, which needs updating on change of academic year. Note6.
- Update the Note_Alternates table using Note_Alternates_Add, inserting any missing idempotent alternates (only applies to newly-created Notes).
- Encode the WebRefs in Notes, Archived Notes, Books, Papers and Authors using Convert_Webrefs with each Object-type in turn. [This is] Note7 covered in detail in the Note Website Generator Documentation - Web Links8.
- Runs Update_Note_Groups_Latest_Timestamp, which says when the latest Note in each Group was updated (so we can say how out-of-date the concatenated list is). Not of much use really.
- Adds default Sub-Topics for new Topics.
- Adds the dummy papers for new books using Books_Versus_Papers_Add.
- Paper_Book_IDs_Update updates missing entries in column ‘Book’ in the Papers table. Sets it to ‘Q’ & (20,000+Book ID). Note9.
- Paper_Book_IDs_Move_Update updates existing entries in column ‘Book’ in the Papers table, as above, using query qryBooks.
- PID_Missing_Online_Papers_Add is used to maintain table PID_Missing_Online_Papers, used to identify those papers held in electronic form that ought to be in my Thesis reading list (or on which a decision should be made).
- Time Importing: The rationale for my time recording will be covered in my Note Website Generator Documentation - Time Recording10. For now, I just list the routines (omitting the housekeeping bits of SQL):-
- Notes_To_Print_Add and Notes_To_Print_Update
- AuthorAndTitle_Update: I need to investigate why this is needed in the Papers table, as it wastes space.
- Papers - Location Update
- Regenerate the various link-tables: are all these still necessary?
- Recent Book Purchases:-
- Authors:
- Runs various queries to maintain the Book_Abstracts_Archive table (from the Books table). Note11.
- Regenerates the Book_Paper_Solitons table. This is to show that the Book-Paper Abstract, rather than the Paper Abstract, is to be displayed where there is only one Paper for the Book (and its Author(s) are the same as those of the Book).
- Regenerates the Page_Regen table, via query Page_Regen_GEN, which uses the Cross_Reference_Changes table. Note12.
- If the Question alluded to above is vbYes, the ‘called’ Notes in it are regenerated via the table Notes_To_Regen and CreateNotesWebPages (with global variables set not to archive the Notes or to regenerate links or associated Notes and a parameter set not to output modal messages). Presumably this is so that Notes recently referenced by other objects (or other Notes) are regenerated to show the new inward links. Then the associated rows from table Cross_Reference_Changes are deleted, as are these rows on the Page_Regen table.
- The following Notes have their status set to ‘Temp’ and are then regenerated via the table Notes_To_Regen and CreateNotesWebPages (with global variables set not to archive the Notes or to regenerate links or associated Notes and a parameter set not to output modal messages). Note13.:-
- Procedure Check_Types is run. It checks tables Subjects, Sub-Topics, and Topics together with the Website file system to see if the relevant files exist. If they do, the table-rows are updated with File_Type of ‘ID’ (or ‘Top’), otherwise of ‘None’. Note21. The files are:-
- Subjects: BookCatalogCategorised_nnn
- Sub-Topics: PaperCatalogPhilosophyFullCategorisedSubTopic_nnn
- Topics: PaperCatalogPhilosophyFullCategorised_Top_nnn
- Finally, the important procedure Cross_Reference_Changes_Prune is run. This procedure rather crudely just deletes all rows from table Cross_Reference_Changes more than 40 days old. Note22.
Change-of-academic year processing is documented in detail below. It runs just before the ‘Oboe’ updates:-
- On 1st October each year23, the system prompts me to set up the timesheet for the new academic year, and to store the previous year’s actuals in the relevant slot in the database.
- Notes
- It is perfectly possible to run the roll-over before running the final Quarterly Reports for the Academic Year just closed. Indeed, it should be done so that time can be recorded and reported from day one of the new academic year.
- However, it is important not to start recording time for the new Academic Year in the New Year’s timesheet before producing the final Quarterly Reports as this time contaminates some of the cross-tabs.
- It is necessary to ignore the prompt the first time round to import the time for the last day of the Academic Year from the old timesheet, then re-run (after a Compact & Repair).
- Indeed, it’s probably best to do this and simply run sub Change_Of_Year on its own.
- When sub Change_Of_Year is invoked, I’m prompted to make the following data and code changes before running the updates:-
- Update the Project_Plans table for the coming year (only).
- Output the end-September Quarterly Summary & Task-List Reports - the last for the "Old Year", using:-
→ cmdMonthSummary_Click, and
→ cmdMonthlyReports_Click
XChk & re-run without "Temp" when happy. Note24.
- Copy StudyPlan.xlsx to StudyPlan_Old.xlsx
- Update the Reporting_Months and Next_Reporting_Month tables
- Update the Year_Crosstab_List_Prime table
- Update query New_Year_Crosstab_Prime_Zap
- Update query New_Year_Crosstab_Prime_OldYear_ADD
- Once these changes have been made, the following queries are run if I respond positively to the message from sub Change_Of_Year:-
- Year_Crosstab_Old_Zap
- Year_Crosstab_Old_GEN
- New_Year_Crosstab_Prime_Zap
- New_Year_Crosstab_Prime_OldYear_ADD
- New_Year_Crosstab_Prime_NewYear_ADD
- Project_Plans_NextYear_Add. This is for the year after next - it just copies the coming year's plans, that have just been set up above
- Once the above are complete, the processing STOPs. Note25.
- Now set up StudyPlan.xlsx for the new year, and
- Update26 the Oboe-Practice report table & queries:-
→ Oboe_Practice_Hours,
→ Oboe_Practice_Hours_ADD,
→ Oboe_Practice_Hours_Zeroise,
→ Oboe_Practice_Hours_Update and
→ Oboe_Practice_Hours_List
- Update27 various Aeon queries:-
→ Aeon_Papers_LastYearHours_Add,
→ Aeon_Papers_LastYearHours_Total, and then
→ Aeon_Papers_Update_Query_Year_Chk
- All the reports can be produced as normal.
This Note is awaiting further attention28.
In-Page Footnotes:
Footnote 1:
- It used to take 15 minutes on my Laptop back in 2022, so my Desktop copes much better.
- Consequently, various investigations highlighted in the notes are less urgent than they might have been.
Footnote 3:
- I understand why the question is asked here (to save the user hanging on for 15 minutes), but why is the Page_Regen table not regenerated here? As far as I can see, the table is only used in this Function, and only for Notes.
Footnote 5:
- I can’t remember why this is an issue.
Footnote 6:
- This ought to be compared against planned hours / day.
Footnote 7:
- Or should be. Check the Note.
Footnote 9:
- I can’t remember what this column is for.
Footnote 11:
- I’m not sure why I don’t just regenerate it.
Footnote 12:
- Why is this so late in the proceedings, when the Question whether Notes are to be regenerated was asked at the beginning?
Footnote 13:
- Presumably there should be a question asked, as it doubtless takes a while!
Footnote 21:
- I’m not sure what this is all about. Presumably the physical pages could be left as ‘orphans’ even if they aren’t currently relevant?
Footnote 22:
- This can’t really be the best way to do things!
- Investigate using my Cross Referencing Note.
Footnote 23:
- There’s a reminder-warning for the two days prior thereto, but this does nothing.
- No doubt I ought to change the processing in case I’m not working on 1st October, but I’ve not been bothered so far.
- I can just temporarily hard-code a new date.
Footnote 24: Footnote 25:
- It should really END as we don’t want to run the daily time importing until the final daily reports for the academic year have been run.
Footnotes 26, 27:
- Sometime I ought to change the process so they don’t need updating, but it’s hardly worth the bother!
Table of the Previous 5 Versions of this Note:
Summary of Notes Referenced by This Note
To access information, click on one of the links in the table above.
Summary of Notes Citing This Note
To access information, click on one of the links in the table above.
Text Colour Conventions
- Blue: Text by me; © Theo Todman, 2025