Ledger system relationship and checking question
I have three tables that are relevant to this question out of 25 in total:
- Properties (with __pkProperty ID)
- General Ledger (with __pkGeneralLedger ID)
- Property Ledger (with _fkProperty ID & __fkGeneralLedger ID)
Table 3 should be a breakdown of an entry in the general ledger but also connected to a property so I think I need this relationship:
- Table 1 & 2 have NO relationship
- Table 3's '_fkProperty ID' is connected to table 1's '__pkProperty ID'
- Table 3's '__fkGeneralLedger ID' is connected to table 2's '__pkGeneralLedger ID'
is this correct?
one more question:
- Imagine a general ledger entry of $500.000
- this transaction is payment for two properties, Property A for 3K and Property B for 2K
- In the Property A property ledger we should add a 3K entry and pick the '__pkGeneralLedger ID' from a pop-up menu field named 'related general ledger ID'
- In the Property B property ledger we should add a 2K entry and pick the '__pkGeneralLedger ID' from a pop-up menu field named 'related general ledger ID'
Now we know what the related general ledger entry is, now I need a checking system in the general ledger that says something like 'Checked' after a calculation like this:
If( general ledger amount = total of(amounts of property ledger entries with this ID) )
And if NOT say something like 'No'
I can't seem to figure out that calculation! how can I achieve that?