AC442 Unit 6 Multi-Table Join Exercise Example

$116,150 Missing From One Wrong Total: A Unit 6 Multi-Table Join Exercise Reconciled to Three Control Totals

[Student Name]

Purdue University Global

AC442: Data Management and Analysis

Unit 6 Multi-Table Join Exercise

[Instructor Name]

[Date]

Cobble Hill Estate Winery, its distributors and every result are composite; the textbooks and guide cited are real; queries are written in standard SQL.

The Question

For each of 46 distributors, how much was billed in fiscal 2025, how much has been collected and how much remains open?

Because the Unit 4 design stores no invoice totals, both versions read from a view, v_invoice, that sums quantity times unit price from INVOICE_LINE for each invoice and carries the distributor and document type. Building the total once, in one place, keeps every later query consistent with the lines.

Version 1: Inner Joins

SELECT d.distributor_id, SUM(i.invoice_total) FROM distributor d JOIN v_invoice i ON i.distributor_id = d.distributor_id JOIN cash_application a ON a.invoice_no = i.invoice_no GROUP BY d.distributor_id;

Result: 1,131 rows before grouping, $1,168,510 billed. The ledger's distributor sales balance is $1,284,660. The row count is the first warning: there are 1,322 documents, so a result built on documents should not show 1,131.

Reconciliation of the $116,150 Gap

1. Unpaid invoices: 214 invoices worth $196,420 have no cash application, so the inner join drops them. Effect: minus $196,420.

2. Credit memos: 38 documents worth negative $21,340 also have no application and are dropped. Effect: plus $21,340, since removing negatives raises the total.

3. Invoices paid by two checks: 61 invoices worth $58,930 match two application rows each and are counted twice. Effect: plus $58,930.

Net: minus $196,420 plus $21,340 plus $58,930 equals minus $116,150. The three causes explain the gap exactly.

An inner join silently removes every invoice nobody has paid and silently doubles every invoice somebody paid twice, so a total built on it can be wrong in both directions at once and still look plausible.

Version 2: Aggregate First, Then Left Join

SELECT d.distributor_id, SUM(i.invoice_total) AS billed, SUM(COALESCE(p.applied, 0)) AS collected, SUM(i.invoice_total + COALESCE(-p.applied, 0)) AS open_amt FROM distributor d JOIN v_invoice i ON i.distributor_id = d.distributor_id LEFT JOIN (SELECT invoice_no, SUM(amount_applied) AS applied FROM cash_application GROUP BY invoice_no) p ON p.invoice_no = i.invoice_no GROUP BY d.distributor_id;

The derived table turns many application rows into one row per invoice before the join, so an invoice paid by two checks contributes its total once. The left join keeps unpaid invoices and credit memos, with COALESCE turning their missing applications into zero. Result: 1,322 documents across 46 distributor rows.

Tie-Out to Control Totals

1. Billed: $1,284,660, equal to the general ledger's distributor sales balance.

2. Collected: $1,088,240, equal to distributor receipts posted in the cash receipts journal.

3. Open: $196,420, equal to the receivables subledger. The open column includes the 38 credit memos at negative $21,340 and $21,340 still owed on 27 invoices that were short-paid.

Each control total comes from outside the query, which is what makes the tie-out a test rather than a restatement (Romney & Steinbart, 2018). A join that cannot be reconciled to an independent total should not be relied on, however clean its syntax (Hoffer et al., 2019). Reconciling analytic results to the ledger before using them is also the first step the audit analytics guide recommends (American Institute of Certified Public Accountants, 2017).

References

American Institute of Certified Public Accountants. (2017). Guide to audit data analytics. AICPA.

Hoffer, J. A., Ramesh, V., & Topi, H. (2019). Modern database management (13th ed.). Pearson.

Romney, M. B., & Steinbart, P. J. (2018). Accounting information systems (14th ed.). Pearson.