Sub-ledger required.

[Frank Martin]

For many years I have been using a ledger based on the Northwind sample
database, and this has the structure:
tblLedgerAccountType.
tblLedgerAccounts.
tblLedgerTransactions.

These tables have a one/many/many relationship.

Now I need to include sub-ledgers (ie each ledger account can have
subaccounts), and I need an efficient new table and relationship to achieve
this, and I need to retain all data in the ledger and I need to enter
various transactions in the new ledger.

Can someone point me in the right direction to do all this? I have
Access2003.

Please help, Frank

 

[mnature]

If each of your LedgerAccounts in tblLedgerAccounts has a unique key, then
you should be able to just create a tblSubLedgerAccounts, where each
SubLedgerAccountID is unique, and the table includes the unique key in the
tblLedgerAccounts. This provides the relationship back to tblLedgerAccounts,
and allows you to have numerous SubLedgerAccounts for each LedgerAccount.

tblSubLedgerAccounts
SubLedgerAccountID (key)
LedgerAccountID (for linking to tblLedgerAccount)

Hope this made sense . . .

 

[Frank Martin]

Thanks, this is a start.
Regards, Frank

If each of your LedgerAccounts in tblLedgerAccounts has a unique key, then
you should be able to just create a tblSubLedgerAccounts, where each
SubLedgerAccountID is unique, and the table includes the unique key in the
tblLedgerAccounts. This provides the relationship back to
tblLedgerAccounts,
and allows you to have numerous SubLedgerAccounts for each LedgerAccount.

tblSubLedgerAccounts
SubLedgerAccountID (key)
LedgerAccountID (for linking to tblLedgerAccount)

Hope this made sense . . .

 

[Frank Martin]

Would this scheme limit me to the existing account names in the existing
tblLedgerAccounts?

If so, could I put new accounts into the new table tblSubLedgerAccounts, and
so avoid confusion with the accounts already present?

Regards, Frank

 

[mnature]

Well, I was envisioning each SubLedgerAccount being tied to a LedgerAccount,
so I may have misunderstood your original question. If you wanted to create
new LedgerAccounts, and still have SubLedgerAccounts tied in to each
LedgerAccount, you could always put a new field in your LedgerAccount table,
that would indicate whether the account was old or new. Could even be a
simple boolean, where any new accounts would set the boolean to "yes," while
keeping the old accounts as "no."

 

[Frank Martin]

Thanks, I'll do it that way.

Well, I was envisioning each SubLedgerAccount being tied to a
LedgerAccount,
so I may have misunderstood your original question. If you wanted to
create
new LedgerAccounts, and still have SubLedgerAccounts tied in to each
LedgerAccount, you could always put a new field in your LedgerAccount
table,
that would indicate whether the account was old or new. Could even be a
simple boolean, where any new accounts would set the boolean to "yes,"
while
keeping the old accounts as "no."