Adventures In Microsoft Access...
This is a quite a bit off-topic and possibly more than a little boring for some people, but, I felt like sharing, so, get over it. I've been collecting movies for many years and managed to amass over 4000 DVDs. As detailed in my post on the Ultimate Otaku Fall Cleanup, I used to have over 2000 VHS tapes. For many years, I kept a spreadsheet with the various titles in my collection. Way back in the misty dawn of time, I produced printed books with a list of movies, but, after the movie list began to exceed 200 pages of printed text, I moved to presenting the list in electronic format using e-readers like Kindle. With e-readers, lists of boring text proved to be ideal as the early generations of e-readers did not really appreciate being asked to render lots of pretty pages with pictures and text.
Eventually, the simple spreadsheet that held my movie list became a bit too unwieldy, so, I migrated the movie list to an Access database. This had immediate advantages as I could now generate reports with the movie list data rather than having to manually manipulate a spreadsheet to present the movie list in an appropriate format for printing. More recently, with the advent of cell phones with large screens, I could finally ditch the plain boring text preferred by e-readers in favor of newer phone-friendly formats that included posters, descriptions and even links to Youtube.
A couple weeks ago, after my primary and backup drives both decided to fail, I lost the master list of my DVDs that I had carefully curated for many years. It took a couple of days to get my PC back up on its wobbly feet and two more days to reconstruct the movie list from a third, but, slightly older partial backup of the list combined with partial lists from other sources.
It took a few days to verify the integrity of the movie list as I ended up performing a full audit of the DVDs by checking every entry on the list against the actual DVDs on hand. There were 90 DVDs that were not listed, 6 DVDs that were actually missing (Assault On Precinct 13 [2005], The Burbs, Cadillac Records, Eagle Eye, Romeo Must Die and Snow White And The Huntsman) and a handful of misfiled DVDs. Thankfully, there were no anime among the missing.
I wasn't planning on a full audit, but, after I ran across some inconsistencies in a spot check, I felt it would be best to go with a full audit. As it turned out, quite a few of the unlisted DVDs appear to have been the result of issues with the initial list reconstruction process. So, in the end, the audit turned out to be a good thing.
Unfortunately, I lost all of the queries I use to build the standalone pages and maintain some of the widget content throughout the site. After verifying the integrity of the reconstructed list, which, in and of itself, was a major undertaking, the first order of business was to recreate the query used to build all the reports used for the movie list:
Cover art is stored locally and the filename is maintained in the LocalImageFilename field. I calculate the field (ImageLink) to derive the full path to the local image folder. I use the JPG format for all of my cover art, so, the file extension is hard-coded:
I'm using Access 2013, so, to place an image in a report, given a full file path in the ImageLink field:
Another important calculated field is CategoryName, which, as the name implies, is for the category names (i.e.: Action, Comedy, Drama, etc...). Each entry in the database has a two-letter category code that corresponds to a category name and I use the following code to derive the name given a category code:
Last, but, not least in the calculated fields, is the YoutubeLink field which is a combination of a hard-coded link name and a Youtube hyperlink field called YoutubeURL:
With these reports in hand, there are four important queries: two queries for the page link web rings -- one for anime and the other for mainstream -- and two queries for the anime ratings -- one sorted alphabetically and the other sorted by rating.
These queries assume that entries using the IsHeader flag exist for each rating and each letter in the alphabet. The AltTitle field contains the preferred titles for each entry. In addition, each header entry has the WebImageURL field populated. After looking at the anime list pages, I was able to reverse engineer the following scripts. The first script shown below displays only anime with ratings while the second script displays all anime:
Eventually, the simple spreadsheet that held my movie list became a bit too unwieldy, so, I migrated the movie list to an Access database. This had immediate advantages as I could now generate reports with the movie list data rather than having to manually manipulate a spreadsheet to present the movie list in an appropriate format for printing. More recently, with the advent of cell phones with large screens, I could finally ditch the plain boring text preferred by e-readers in favor of newer phone-friendly formats that included posters, descriptions and even links to Youtube.
A couple weeks ago, after my primary and backup drives both decided to fail, I lost the master list of my DVDs that I had carefully curated for many years. It took a couple of days to get my PC back up on its wobbly feet and two more days to reconstruct the movie list from a third, but, slightly older partial backup of the list combined with partial lists from other sources.
It took a few days to verify the integrity of the movie list as I ended up performing a full audit of the DVDs by checking every entry on the list against the actual DVDs on hand. There were 90 DVDs that were not listed, 6 DVDs that were actually missing (Assault On Precinct 13 [2005], The Burbs, Cadillac Records, Eagle Eye, Romeo Must Die and Snow White And The Huntsman) and a handful of misfiled DVDs. Thankfully, there were no anime among the missing.
I wasn't planning on a full audit, but, after I ran across some inconsistencies in a spot check, I felt it would be best to go with a full audit. As it turned out, quite a few of the unlisted DVDs appear to have been the result of issues with the initial list reconstruction process. So, in the end, the audit turned out to be a good thing.
Unfortunately, I lost all of the queries I use to build the standalone pages and maintain some of the widget content throughout the site. After verifying the integrity of the reconstructed list, which, in and of itself, was a major undertaking, the first order of business was to recreate the query used to build all the reports used for the movie list:
The hash value is just the title or alternate title with spaces and special characters removed and is created using a simple script:SELECT * FROM MovieList WHERE MovieList.IsHeader=False AND MovieList.WasOnlyOnVHS=False AND MovieList.IsHentai=False AND MovieList.IsMisc=False ORDER BY MovieList.HashValue;
UPDATE MovieList SET MovieList.HashValue = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
IIF(MovieList.Override_HashSort=True, CSTR(MovieList.Title),
IIF(ISNULL(MovieList.AltTitle),CSTR(MovieList.Title), CSTR(MovieList.AltTitle))
),' ','') ,'''',''),':',''),';',''),'-',''),'(',''),')',''),'&','And'),',',''),'.',''),'!',''),'+',''),'@','At'),'/',''),'"','');
The title is used to describe a particular entry and is the primary field used for sorting (i.e.: Space Battleship Yamato Series 1: Quest For Iscandar). The alternate title field is the name to display and is used to override the title for sorting purposes (i.e.: Starblazers). Occasionally, I'll have an alternate title for a particular entry, but, still want to use the title for sorting purposes (i.e.: Title: Three-Hundred; Alternate Title: 300), so, there's a flag (Override_HashSort) for that function. By the way, I prefer to spell out titles with numbers so they can be sorted alphabetically rather than by number, so, the aforementioned 300, would be found in the T's.Cover art is stored locally and the filename is maintained in the LocalImageFilename field. I calculate the field (ImageLink) to derive the full path to the local image folder. I use the JPG format for all of my cover art, so, the file extension is hard-coded:
The images do not need to be the same size as Access will automatically scale the images to fit the available space, but, I usually set the height and width of all the cover art to a consistent size so that the images will fit within the report more precisely. By the way, the image shown in Design View for the category report is a generic image that indicates that there is no image available. In the reports, the general format has the cover art on the left, the description on the right in large print, the title in small print at the bottom of the cover art area, the category code as an overlay on the top right corner of the cover art and a clickable link to a video just below the description.UPDATE MovieList SET ImageLink = 'C:\DVD_Covers\' + MovieList.LocalImageFilename + '.jpg'
I'm using Access 2013, so, to place an image in a report, given a full file path in the ImageLink field:
- First, in Design View, from the controls menu, select Image.
- Then, place any image from your local machine into the report.
- Then, in the Other tab in the Image Properties pane, set Name to ImagePlaceholder.
- Finally, in the Data tab in the Image Properties pane, set Control Source to [ImageLink].
Another important calculated field is CategoryName, which, as the name implies, is for the category names (i.e.: Action, Comedy, Drama, etc...). Each entry in the database has a two-letter category code that corresponds to a category name and I use the following code to derive the name given a category code:
In the reports, sorted by category, the CategoryName field would be used for the report header. The category code is a hold-over from the spreadsheet days where column widths mattered when the time came to print a hard-copy of the movie list.UPDATE MovieList SET MovieList.CategoryName = IIf(MovieList.Category='AA','Action', IIf(MovieList.Category='AN','Animation', IIf(MovieList.Category='CM','Comedy', IIf(MovieList.Category='DC','Documentary', IIf(MovieList.Category='DR','Drama', IIf(MovieList.Category='HR','Horror', IIf(MovieList.Category='JA','Japanese Animation', IIf(MovieList.Category='MU','Musical', IIf(MovieList.Category='SF','Science Fiction', IIf(MovieList.Category='WS','Western', IIf(MovieList.Category='XM','Holiday', 'N/A')))))))))))
Last, but, not least in the calculated fields, is the YoutubeLink field which is a combination of a hard-coded link name and a Youtube hyperlink field called YoutubeURL:
In the report, I added a text box and set the Control Source to [YoutubeLink] and the link worked in Report View and, amazingly, the links still worked after I exported the report to PDF. It took a while to fine tune everything and many trips into the depths of various forums to make it happen. Here are a few tips for those of you attempting to implement clickable links in a PDF file from an Access report:UPDATE MovieList SET MovieList.YoutubeLink = IIf(IsNull(MovieList.YoutubeURL),"No Video","Play Video" + REPLACE(MovieList.YoutubeURL,"embed/","watch?v="))
I ended up producing 37 reports which include reports for each letter of the alphabet from A to W, one report that combines X, Y and Z, one report for each category and two reports that combine the alphabetical and category reports.
- The URL should be in a hyperlink field.
- The default link text is the URL, so, if you want the link text to be something else, append the link text before the hyperlink.
- The hyperlink field comes with # delimiters, so, if you're NOT using a hyperlink field, you'll need to append # before and after your URLs (i.e. Google#https://www.google.com#).
With these reports in hand, there are four important queries: two queries for the page link web rings -- one for anime and the other for mainstream -- and two queries for the anime ratings -- one sorted alphabetically and the other sorted by rating.
These queries assume that entries using the IsHeader flag exist for each rating and each letter in the alphabet. The AltTitle field contains the preferred titles for each entry. In addition, each header entry has the WebImageURL field populated. After looking at the anime list pages, I was able to reverse engineer the following scripts. The first script shown below displays only anime with ratings while the second script displays all anime:
SELECT MovieList.AltTitle, IIf(MovieList.IsHeader=True, CStr('</div><div class=''clear''></div><div class=''aargHeader''><img alt='''' src='''+REPLACE(MovieList.WebImageUrl,'s72-c','s144-c')+''' /><br />'+MovieList.AltTitle+'</div><div class=''aargContent''>'),
IIf(IsNull(MovieList.WebURL),
CStr('<div class=''aargRatingContainer''><span class=''aargRating''>')+CStr( IIf(Nz(MovieList.Rating,-1)<0,'NR', CStr(MovieList.Rating)))+CStr('</span><span class=''aargLink''>'+MovieList.AltTitle+'</span></div>'),
CStr('<div class=''aargRatingContainer''><span class=''aargRating''><a href='''+MovieList.WebURL+'''>')+CStr( IIf(Nz(MovieList.Rating,-1)<0,'NR', CStr(MovieList.Rating)))+CStr('</a></span><span class=''aargLink''><a href='''+MovieList.WebURL+'''>'+MovieList.AltTitle+'</a></span></div>'))) AS AnimeList
FROM MovieList
WHERE (MovieList.IsHeader=False AND MovieList.IsAnime=True AND Nz(MovieList.Rating,-1)>=0) OR (MovieList.IsHeader=True AND MovieList.IsRatingHeader=True)
ORDER BY MovieList.Rating DESC, MovieList.HashValue;
SELECT MovieList.AltTitle, IIf(MovieList.IsHeader=True, CStr('</div><div class=''clear''></div><div class=''aargHeader''><img alt='''' src='''+REPLACE(MovieList.WebImageUrl,'s72-c','s144-c')+''' /><br />'+MovieList.AltTitle+'</div><div class=''aargContent''>'),
IIf(IsNull(MovieList.WebURL),
CStr('<div class=''aargRatingContainer''><span class=''aargRating''>')+CStr( IIf(Nz(MovieList.Rating,-1)<0,'NR', CStr(MovieList.Rating)))+CStr('</span><span class=''aargLink''>'+MovieList.AltTitle+'</span></div>'),
CStr('<div class=''aargRatingContainer''><span class=''aargRating''><a href='''+MovieList.WebURL+'''>')+CStr( IIf(Nz(MovieList.Rating,-1)<0,'NR', CStr(MovieList.Rating)))+CStr('</a></span><span class=''aargLink''><a href='''+MovieList.WebURL+'''>'+MovieList.AltTitle+'</a></span></div>'))) AS AnimeList
FROM MovieList
WHERE (MovieList.IsHeader=False AND MovieList.IsAnime=True) OR (MovieList.IsHeader=True AND MovieList.IsAlphaHeader=True)
ORDER BY MovieList.HashValue;
The Blogger navigation system is dependent on dates, so, I came up with a system of web rings to allow visitors to move in a circular fashion through the various posts in alphabetical order. After peeking at the page links on the site, I was able to reverse engineer the web ring page link queries -- one for the anime web ring and the other for the mainstream web ring. It's a fairly simple piece of coding that requires some maintenance behind the scenes, but, it serves my humble needs reasonably well:
SELECT IIF(ISNULL(MovieList.AltTitle),CSTR(MovieList.Title), CSTR(MovieList.AltTitle)) As DisplayName,
IIf(IsNull(MovieList.WebURL),'Page Link Missing',
IIf(IsNull(MovieList.WebImageURL),'Image Missing',CStr('<div imgurl='''+MovieList.WebImageURL+''' pagetitle='''+IIf(IsNull(MovieList.AltTitle),CStr(MovieList.Title), CStr(MovieList.AltTitle))+''' pageurl='''+MovieList.WebURL+''' style=''display: none;'' tag=''aarg''></div>'))) AS PageIDs,
IIf(Nz(MovieList.Rating,-1)<0,'No Rating',CStr('<b aargrating='''+CStr(MovieList.Rating)+'''></b>')) AS BoldRating,
MovieList.WebImageURL,
MovieList.WebURL,
MovieList.YoutubeURL
FROM MovieList
WHERE (((MovieList.IsAnime)=True) AND ((MovieList.IsPageLink)=True))
ORDER BY MovieList.HashValue;
SELECT IIF(ISNULL(MovieList.AltTitle),CSTR(MovieList.Title), CSTR(MovieList.AltTitle)) As DisplayName,
IIf(IsNull(MovieList.WebURL),'Page Link Missing',
IIf(IsNull(MovieList.WebImageURL),'Image Missing',CStr('<div imgurl='''+MovieList.WebImageURL+''' pagetitle='''+IIf(IsNull(MovieList.AltTitle),CStr(MovieList.Title), CStr(MovieList.AltTitle))+''' pageurl='''+MovieList.WebURL+''' style=''display: none;'' tag=''aarg''></div>'))) AS PageIDs,
MovieList.WebImageURL,
MovieList.WebURL,
MovieList.YoutubeURL
FROM MovieList
WHERE (((MovieList.IsMainstream)=True) AND ((MovieList.IsPageLink)=True))
ORDER BY MovieList.HashValue;
This incident, by way of this long boring post, allowed for the documentation of these critical scripts for future reference and, of course, has also highlighted the need for more frequent and more extensive backups of my important data. Hello kiddies. Can you say: Google Drive?May 2, 2019
Update
Recently, I decided to upgrade the Anime Rating Guide by bringing more automation into the inner workings of the site. First, I needed to render a JavaScript array that would form the basis of the upgraded automated functions:
SELECT MovieList.DisplayName, CSTR('anime.push({name: ''' + REPLACE(CSTR(MovieList.DisplayName), '''','\''') + ''', webpage: ''' + CSTR(MovieList.WebURL) + ''', image: ''' + CSTR(MovieList.WebImageURL) + ''', rating: '''+IIf(Nz(MovieList.Rating,-1)<0,'NR',CStr(MovieList.Rating))+'''});') AS AnimeArray
FROM MovieList
WHERE ((MovieList.IsMainstream=False) AND (MovieList.IsOther=False) AND (MovieList.IsPageLink=True))
ORDER BY MovieList.HashValue;
The Anime List also received an upgrade to allow for filtering and sorting and with that upgrade came the need to generate a table with sorting and filtering keys:
SELECT MovieList.AltTitle, MovieList.Rating, MovieList.Genre, IIf(IsNull(MovieList.WebURL),
CStr('<tr><td')+CStr(IIf(Nz(MovieList.Rating,-1)<0,' sorttable_customkey=''-1''>','>')) +CStr(IIf(Nz(MovieList.Rating,-1)<0,'NR', CStr(MovieList.Rating)))+CStr('</td><td n='''+REPLACE(REPLACE(CStr(MovieList.IsNew),'-1','1'),'0','')+''' g='''+IIf(IsNull(MovieList.Genre),'',MovieList.Genre)+'''>'+MovieList.AltTitle+'</td></tr>'),
CStr('<tr><td')+CStr(IIf(Nz(MovieList.Rating,-1)<0,' sorttable_customkey=''-1''>','>'))+CStr('<a href='''+MovieList.WebURL+'''>')+CStr( IIf(Nz(MovieList.Rating,-1)<0,'NR', CStr(MovieList.Rating)))+CStr('</a></td><td n='''+REPLACE(REPLACE(CStr(MovieList.IsNew),'-1','1'),'0','')+''' g='''+IIf(IsNull(MovieList.Genre),'',MovieList.Genre)+'''><a href='''+MovieList.WebURL+'''>'+MovieList.AltTitle+'</a></td></tr>')) AS TableList
FROM MovieList
WHERE (MovieList.IsHeader=False AND MovieList.IsAnime=True)
ORDER BY MovieList.HashValue;
Enjoy.
