Member mainrun

About My Library

Update 12/31/2025
What stars mean:
1 = Books that should not be read.
2 = Books that are bad or forgettable, but more worthy than one star.
3 = Books that are enjoyable, but nothing to write home about.
4 = Books that are hard to put down and can't wait to pick up again.
5 = Books I would like to read again.
_________________________________________________
Entire Collection
Stars Count Percent
1------- 33-- 4.238%
2-------184-- 24.40%
3-------304-- 40.32%
4-------172-- 22.81%
5------- 61-- 8.09%
Total---754--100.00%
Average Rating 3.05835543766578

Books read before joining LibraryThing
Stars Count Percent
1------- 8-- 2.04%
2-------126-- 32.14%
3-------151-- 38.52%
4------- 67-- 17.09%
5------- 40-- 10.29%
Total-- 392--100.00%
Average Rating 3.01275510204082

Books read after joining LibraryThing
Stars Count Percent
1--------25-- 6.91%
2--------58-- 16.02%
3-------153-- 42.27%
4-------105-- 29.01%
5--------21-- 5.80%
Total---362--100.00%
Average Rating 3.10773480662983

Over the last six months I reread a book that is in the "Read after joining LibraryThing" collection. I read it in 2021 and again in 2025. By definition books I re-read get 5 stars. This story, and the mini-series based on it, deserve five stars. The novel: The Institute by Stephen King.

The Microsoft Access query I wrote to populate the numbers only counted books in the BEFORE and AFTER LibraryThing collection. When re-reading a book in the After collection, the number of books did not increase in the AFTER numbers above. This is the first time I re-read a book that is not in the "Read before joining LibraryThing" collection, so this bug was exposed.

I decided to use the AI, https://gemini.google.com/ to fix. Here was my prompt (I edited it a bit):

<START OF GEMINI PROMPT:>
To populate this information...
Books read after joining LibraryThing
Stars Count Percent
1--------25-- 6.93%
2--------58-- 16.07%
3-------153-- 42.38%
4-------105-- 29.09%
5--------20-- 5.54%
Total---361--100.00%
Average Rating 3.10249307479224

...I use a Microsoft Access query with a VBA function. There is a bug: the five star books was 20, same as previous update. It should be 21 because I reread a 5 star book. The total number of books should not be 361; 362 is correct. Finally, the percentages are correct for the incorrect number total books AND 5 star books, so should update when the numbers get fixed.

Here is the SQL I used to get the numbers:
SELECT Current.rating AS Expr1, Count(Current.[book id]) AS [CountOfbook id], ComputePercent(Current!rating,"collections Like '*Read after joining LibraryThing*'") AS Percentage
FROM [Current]
WHERE (((Current.collections) Like "*Read after joining LibraryThing*"))
GROUP BY Current.rating, ComputePercent(Current!rating,"collections Like '*Read after joining LibraryThing*'");

Here is ComputePercent:
Public Function ComputePercent(intRating As Integer, strWhere As String) As String
On Error GoTo err_Handler:
Dim dbsToDo As Database
Dim rstAll As Recordset
Dim rstSub As Recordset
Dim rstAvg As Recordset
Dim intTotal As Integer
Dim intSub As Integer
Dim dblAverage As Double
Dim sSql As String
Set dbsToDo = CurrentDb
sSql = "SELECT "
sSql = sSql & " Count(Current.[book id]) AS CountOfbookid "
sSql = sSql & "FROM "
sSql = sSql & "Current "
If strWhere <> "" Then
sSql = sSql & "WHERE "
sSql = sSql & strWhere
End If
Set rstAll = dbsToDo.OpenRecordset(sSql, dbOpenDynaset)
With rstAll
Do While Not .EOF
intTotal = !CountOfbookid
.MoveNext
Loop
End With
rstAll.Close
sSql = "SELECT "
sSql = sSql & " Count(Current.[book id]) AS CountOfbookid "
sSql = sSql & "FROM "
sSql = sSql & "Current "
sSql = sSql & "WHERE "
sSql = sSql & "Current.rating = " & intRating
If strWhere <> "" Then
sSql = sSql & " AND " & strWhere
End If
Set rstSub = dbsToDo.OpenRecordset(sSql, dbOpenDynaset)
With rstSub
Do While Not .EOF
intSub = !CountOfbookid
.MoveNext
Loop
End With
rstSub.Close
sSql = "SELECT "
sSql = sSql & " Avg(Current.rating) AS AverageOfbookid "
sSql = sSql & "FROM "
sSql = sSql & "Current "
If strWhere <> "" Then
sSql = sSql & "WHERE "
sSql = sSql & strWhere
End If
Set rstAll = dbsToDo.OpenRecordset(sSql, dbOpenDynaset)
With rstAll
Do While Not .EOF
dblAverage = !AverageOfbookid
.MoveNext
Loop
End With
rstAll.Close
ComputePercent = "TOTAL: " & intTotal & " PERCENT: " & Format(intSub / intTotal, "percent") & " AVERAGE: " & dblAverage
exit_Sub:
Exit Function
err_Handler:
Select Case Err.Description
Case "Type mismatch"
Case Else
MsgBox Err.Description
End Select
Resume exit_Sub
End Function

The above only tracks the count of books in the before and after collections. The total number of 5-star books didn't increase because I read a book again that is only in the after collection. Would you propose a Microsoft Access solution that would increase the rating counts and update the percentages if books in the "after" collection are re-read?
</END OF GEMINI PROMPT:>

Here was the response (I also edited this a bit):

<START OF GEMINI RESPONSE:>
The reason your current solution didn't show an increase for The Institute is that Count(Current.[book id]) counts records (rows), not events. Since The Institute is still just one row in your table, the math remains the same regardless of how many years you append to that string.

To fix this, we need to shift from Counting Rows to Summing Occurrences. We can use "Length Difference" logic to treat one row as multiple "reading events."
1. Updated VBA Function: ComputePercent

We need to modify the SQL inside your function. Instead of using Count(), we will use Sum() combined with the Replace logic to calculate the "Reading Volume."

Replace your existing function with this version:
VBA

Public Function ComputePercent(intRating As Integer, strWhere As String) As String
On Error GoTo err_Handler:
Dim dbsToDo As Database
Dim rst As Recordset
Dim intTotalReadings As Integer
Dim intSubReadings As Integer
Dim dblWeightedAvg As Double
Dim sSql As String
Dim strOccurrences As String

Set dbsToDo = CurrentDb

' This string calculates how many times "Read in" appears in a single row
strOccurrences = "((Len([collections]) - Len(Replace([collections], 'Read in', ''))) / 7)"

' 1. Calculate Total Readings for this category
sSql = "SELECT Sum(" & strOccurrences & ") AS Total FROM Current "
If strWhere <> "" Then sSql = sSql & "WHERE " & strWhere
Set rst = dbsToDo.OpenRecordset(sSql, dbOpenDynaset)
intTotalReadings = Nz(rst!Total, 0)
rst.Close

' 2. Calculate Readings for this specific Rating
sSql = "SELECT Sum(" & strOccurrences & ") AS SubTotal FROM Current WHERE rating = " & intRating
If strWhere <> "" Then sSql = sSql & " AND " & strWhere
Set rst = dbsToDo.OpenRecordset(sSql, dbOpenDynaset)
intSubReadings = Nz(rst!SubTotal, 0)
rst.Close

' 3. Calculate Weighted Average (important for re-reads!)
' A 5-star book read twice should count as 10 (5+5) towards the average
sSql = "SELECT Sum(rating * " & strOccurrences & ") / Sum(" & strOccurrences & ") AS WeightedAvg FROM Current "
If strWhere <> "" Then sSql = sSql & "WHERE " & strWhere
Set rst = dbsToDo.OpenRecordset(sSql, dbOpenDynaset)
dblWeightedAvg = Nz(rst!WeightedAvg, 0)
rst.Close

ComputePercent = "TOTAL: " & intTotalReadings & " PERCENT: " & Format(intSubReadings / intTotalReadings, "0.00%") & " AVG: " & dblWeightedAvg

exit_Sub:
Exit Function
err_Handler:
MsgBox Err.Description
Resume exit_Sub
End Function

2. Updated SQL Query

Your main query also needs to change the Count to a Sum. This ensures the "Count" column in your report shows "2" for The Institute instead of "1".
SQL

SELECT
Current.rating AS Stars,
Sum((Len([collections]) - Len(Replace([collections], 'Read in', ''))) / 7) AS [Count of Readings],
ComputePercent(Current.rating, "collections Like '*Read after joining LibraryThing*'") AS [Stats]
FROM [Current]
WHERE (((Current.collections) Like "*Read after joining LibraryThing*"))
GROUP BY
Current.rating,
ComputePercent(Current.rating, "collections Like '*Read after joining LibraryThing*'");

Why this works

The Multiplier: If The Institute has "Read in 2021, Read in 2025", the formula (Len - Len(Replace)) / 7 returns 2.

The Sum: When the SQL hits that row, instead of adding 1 to your "5-star" bucket, it adds 2.

The Weighted Average: In the VBA, we now calculate the average by multiplying the rating by the number of times it was read. This ensures that a book you've read four times carries four times the weight of a book you've read once.

A Quick Note on Low Ratings:

Since you occasionally give 1 and 2 stars rating, this new math ensures that if you ever re-read a book you disliked, it will pull your average down twice as hard!
</END OF GEMINI RESPONSE:>

I copied/pasted the changes and BANG, the results I wanted appeared. I tested a bit. This AI stuff is amazing.
__
There were seven four-star books over the last six months I would recommend: Grandma Gatewood's Walk: The Inspiring Story of the Woman Who Saved the Appalachian Trail by Ben Montgomery, Orphan X: A Novel by Gregg Hurwitz, Starter Villain by John Scalzi, The Martian by Andy Weir, To Kill a Mockingbird by Harper Lee, Tom Clancy Defense Protocol (A Jack Ryan Novel) by Brian Andrews, and Tom Clancy Line of Demarcation (A Jack Ryan Jr. Novel) by M.P. Woodward.

I am happy to say I did not give a book one star over the last six months.

Another bug that always bothered me was the Entire Collection books did not increase if I re-read a book. If you added the Books read before joining LibraryThing, 392, to the Books read after joining LibraryThing, 362, you get 754 (392 + 362). The Entire Collection had 723 books. The difference, 31 (754 - 723), is the number of books I have reread. Here are the books. Demonstrating the rebel I am, not all re-read books are 5 stars. Two of them actually suck. I'm talking about The Celestine Prophecy and Prayers for Rain.
#; Known Year Read; Rating; Title; Author
1; 2009; 1; The Celestine Prophecy; James Redfield
2; 2011; 5; Salem's Lot; Stephen King
3; 2011; 5; Anne of Green Gables; L. M. Montgomery
4; 2011; 2; Anne of Avonlea; L. M. Montgomery
5; 2013; 3; The Summons; John Grisham
6; 2014; 4; The Shining; Stephen King
7; 2015; 3; The Mouse and the Motorcycle; Beverly Cleary
8; 2016; 3; The Mote in God's Eye; Larry Niven
9; 2016; 3; Cujo; Stephen King
10; 2017; 4; Firestarter; Stephen King
11; 2017; 4; Pet Sematary (Signet); Stephen King
12; 2018; 3; Balance of Power: A Novel; James W. Huston
13; 2018; 3; Monkeewrench; P. J. Tracy
14; 2019; 4; Night Shift; Stephen King
15; 2019; 3; Town Like Alice; Nevil Shute
16; 2020; 5; Harry Potter 1 And The Sorcerer's Stone; J.K Rowling
17; 2020; 5; Harry Potter 2 and the Chamber of Secrets; J.K Rowling
18; 2020; 5; Harry Potter 3 and the Prisoner of Azkaban; J.K Rowling
19; 2020; 5; Harry Potter 4 And The Goblet Of Fire; J.K Rowling
20; 2020; 5; Harry Potter 5 and the Order of the Phoenix; J.K Rowling
21; 2020; 5; Harry Potter 6 and the Half-Blood Prince; J.K Rowling
22; 2020; 4; Memorial Day; Vince Flynn
23; 2020; 4; The Third Option; Vince Flynn
24; 2020; 3; Eddie the Dog Holder; Carolyn Haywood
25; 2021; 5; Harry Potter 7 and the Deathly Hallows; J.K Rowling
26; 2021; 5; Dolores Claiborne; Stephen King
27; 2021; 5; Where the Red Fern Grows; Wilson Rawls
28; 2023; 5; Ender's Game; Orson Scott Card
29; 2024; 1; Prayers for Rain; Dennis Lehane
30; 2025; 5; The Institute: A Novel; Stephen King
31; 2025; 2; The Hunt for Red October; Tom Clancy

I asked Gemini again to change the process to count all books to include re-reads. It was up to the challenge.

My "Read before joining LibraryThing" collection has 30 more books than my Read after joining LibraryThing collection (392 - 362.) This is compared to 48 in June 2025 and 59 in December 2024. Last time I predicted the collections will have the same number in March of 2027. We will see! I asked my Gemini boss and he thought my "After LibraryThing" collection will match and then overtake my "Before" collection in November 2026.

Location

Maine

Also On

LinkedIn, Wordpress, Wordpress

Currently Reading

Bel Canto by Ann Patchett

Favorite Authors

Douglas Adams, S. J. Bolton, David Brin, Max Byrd, Orson Scott Card, John le Carré, Tom Clancy, Michael Crichton, Nelson DeMille, William Goldman, John Grisham, Stephen King, Robert Ludlum, Larry Niven, J. K. Rowling, Brandon Sanderson, Jeff Shaara, Dan Simmons, J. R. R. Tolkien, Mark Twain, Gore Vidal

Member Connections

Friends

ClubStephenKing, FreddyKrug11, lindawilkinson, runner56

Contacts

rocketjk