I have a base with two tables:
Checks
- Check number (string)
- Amount (currency)
Itemizations
- Check number (string; primary; the view is grouped by check number)
- Item name (string)
- Amount (currency)
Part of the purpose of this table is to ensure that the checks I received are for the correct amount (otherwise I need to call accounting to resolve the discrepancy). If everything is correct, then the query
SELECT "Check number", SUM("Amount") AS "Total"
FROM "Itemizations"
GROUP BY "Check number"
ORDER BY "Check number" ASC
should produce the same result as
SELECT "Check number", "Amount" AS "Total"
FROM "Checks"
ORDER BY "Check number" ASC
I’d like each record in Itemizations to have a link to the corresponding check, that is, a linked record field whose contents are
SELECT {a link to the record}
FROM "Checks"
WHERE "Check number" = {Check number}
So essentially, I want to perform a left join, and the field I want to join on happens to be a primary key in both tables. I don’t want to have to manually enter both the check number and the linked record. Is there any good way to do this?
(Perhaps the best way is to do away with the primary key in Itemizations and instead just group by the linked record and have the primary key be some dummy field.)
In general, the ability to do record lookup based on the contents of an existing field would be useful.
