Check Register

[speakers_86]

I am thinking of adding account tracking to my current db. The idea is the user can record the transaction, classify the transaction, and at the end of the year, export the list to excel for an accountant.

The design will be something like this:

tblAccounts
AccountID
AccountTypeID
AccountName

lstAccountType
AccountTypeID
AccountType

tblTransactions
TransactionID
AccountID
CategoryID
DateofTransaction
AmountofTransaction
VendorName

lstCategories
CategoryID
Category


Thats the easy part. Here are the harder questions.

1. Split transactions
If one transaction is made, how could the total be split across two different categories? My first thought was just adding another table with TransactionID as foreign.

2. Direct Costs
If one transaction is made specifically for a work order, where would the work order id go? What if one transaction is made for 2 work orders (split!)? What if current WorkOrderIDs (by current I mean work in progress) are included as a category? I suppose that when a new WorkOrderID is created, I could use an append query to add [WorkOrderID]+" "+[CustomerLastName]+", "+[CustomerLastName] as a category. The question then would be how to remove this option from the drop down list once the work order is no longer active. To include direct costs in the work order form (there is a subform for expenses), I could query the transaction table and the sub transaction table (from number 1 above) for categories that start with numbers.

3. How could I handle transfers? Transfering money from one account to another, like paying a visa bill? Perhaps another table like this
tblTransfers
TransferID
TransferDate
TransferAmount
FromAccountID
ToAccountID

Then the issue would be getting the same transfer to appear as a transaction in each accounts form, as each account will have it's own form, but only one table.

Maybe a better idea is just to include each account name as a category, just like with the work orders.

 

[speakers_86]

I think asking those questions helped me clear my head and figure this out. I am double posting to keep the clear logic separate from the rambling.

Heres the plan.

Splits will be handled by using a sub table with TransactionID as foreign.
Work Orders and Account names will be included in the list of categories. The drop down box for categories will query all categories except for old Work Orders.

There will be two types of forms for accounts. One form for banks, that will be opened when the selected account is a bank. This form will include check number (a field I missed in the OP). The other form is for credit cards, and will not show the check number field.

What do you guys think about my solutions? How hard will it be to export this to excel?

 

[speakers_86]

I think I changed my mind. For simplicity reasons, I will include a field for WorkOrderID in both tblTransactions and subTransactions. I don't think I want to append work orders numbers like I thought initially.