You write the obvious query: sum GLTOTAL.SMTD across every account for a property and book, group by account, and expect the whole thing to foot to zero. It doesn't. Not by a rounding error — by exactly the amount of that period's net income. Before you assume the data is wrong, it isn't. GLTOTAL is doing precisely what Voyager designed it to do; a trial balance just isn't the question it answers.
The retained-earnings system row
Voyager's GL setup includes a retained-earnings account, and Voyager itself — not a user, not a journal entry — writes to it every period. That system-maintained row sets the account's monthly SMTD equal to the period's net P&L. It has no offsetting posting anywhere else, and no backing row in GLDETAIL at all.
That's the mechanism, and it's deliberate: balance sheet reports need retained earnings to reflect cumulative net income so the balance sheet foots, and this system row is how Voyager makes that happen without waiting for anyone to post a manual closing entry.
The side effect is what catches people building a trial balance: sum GLTOTAL across every account — P&L accounts and the retained-earnings account together — and you count that period's earnings twice. Once inside the P&L accounts that actually generated it, and again in the system row that rolled it into retained earnings. A real double-entry trial balance can't do that and still foot to zero. This one won't, and it's not a bug in your query.
Why GLDETAIL is the table to build from
GLDETAIL only carries real, double-entry postings — actual transactions with actual offsetting entries. It has no knowledge of Voyager's system-maintained retained-earnings row, because that row was never posted as a transaction; Voyager derives and writes it straight into GLTOTAL.
Which means a trial balance built from GLDETAIL — beginning balance, period activity, ending balance per account, summed with SUM(DAMOUNT) and bucketed by post date — foots to zero by construction. There's no synthetic earnings row to double-count, because GLDETAIL never had one to begin with.
The practical shape of that query:
SELECT
a.SCODE AS acct_code,
a.SDESC AS acct_desc,
SUM(CASE WHEN gd.DTPost < '{PERIOD_START}' THEN gd.DAMOUNT ELSE 0 END) AS beginning_balance,
SUM(CASE WHEN gd.DTPost >= '{PERIOD_START}' THEN gd.DAMOUNT ELSE 0 END) AS month_activity,
SUM(gd.DAMOUNT) AS ending_balance
FROM GLDETAIL gd
JOIN PROPERTY p ON gd.HPROP = p.HMY
JOIN ACCT a ON gd.HACCT = a.HMY
WHERE p.SCODE = '{PROPERTY_CODE}'
AND gd.IBOOK = 1 -- 0 = cash, 1 = accrual
AND gd.DTPost < '{NEXT_PERIOD_START}'
AND ISNULL(gd.bSupressFromGl, 0) = 0
GROUP BY a.SCODE, a.SDESC
ORDER BY a.SCODE
Two details matter beyond the retained-earnings trap. First, GLDETAIL is a high-volume table on any mature install — always bound it by property, book, and a DTPost range rather than scanning it unfiltered. Second, filter out rows with bSupressFromGl set, or you'll pull in postings Voyager itself excludes from GL reporting.
GLTOTAL still has a job
None of this makes GLTOTAL wrong to use — it's the right table for balance sheet and P&L reporting, where you want the system-maintained retained-earnings row doing its job of making the balance sheet foot. The mistake is reaching for GLTOTAL for an all-accounts trial balance, where the same row that makes a balance sheet correct is exactly what breaks a TB's zero-sum check.
If you ever need to prove this to yourself on a live database, the diagnostic is simple: pull the retained-earnings account's balance from GLTOTAL and separately from GLDETAIL for the same period. The gap between them is the system-maintained portion — and it will equal that period's net P&L, and the cumulative gap across all periods will equal your original all-accounts imbalance. It's a clean, checkable fingerprint, not a guess.
GLTOTAL also has a second wrinkle worth knowing before you query it for anything: its grain isn't unique per property/account/book/month the way you'd assume — rows also split by department segment, and segment rows don't sum to the unsegmented row. Always GROUP BY explicitly rather than trusting one row per account.
Why this is worth writing down
Nobody documents this. Voyager's schema doesn't carry a comment explaining why a retained-earnings account behaves differently from every other account in GLTOTAL, and the standard reporting tools built on top of it don't surface the distinction either — they just work, because they were built to use GLTOTAL correctly for the reports it's meant for. It's only when someone reaches for a general-purpose trial balance query that the gap opens up, and the failure mode — a TB that's close to zero but not exactly zero, or off by an amount that looks suspiciously like net income — is exactly subtle enough to get explained away as a rounding issue instead of traced to its real cause.
This is the kind of table-level mechanism that PropETL's SQL Assistant carries as verified knowledge, not a guess Claude reconstructs from column names — the trial-balance query pattern it drafts from is already built on GLDETAIL, with the retained-earnings trap documented alongside it so you get the right query the first time instead of after a debugging session. It ships in every tier, including the free 7-day trial — ask it to draft a trial balance for a property and book and see the GLDETAIL version, not the one that silently double-counts.