Personal Accounts - Start of project (1 Viewer)

essaytee

Need a good one-liner.
Local time
Tomorrow, 07:08
Joined
Oct 20, 2008
Messages
512
Personal Accounts - Start of project - 1

I'm wanting to replace my reliance on Reckon Accounts (not to say that it's not a good program, it is, it does everything (nearly) I want). I'm doing this as a challenge and for purely personal reasons. I'll tackle this in stages as and when I have time but in the first instance I'm working on the schema and is the most important stage. I'm no stranger to Access and VBA coding though I've never had reason to code and program account (customers, payments, invoices etc.) type information, my area of expertise (for want of a better term) is in law enforcement. Also I should mention that I'm more familiar with Access 2003 but very soon my department is upgrading to Office 2010. For this project I'm using Access 2010. I know this will not (or should not) detract from the schema required but I'm assuming any differences will apply to the GUI component (cross that bridge when I get to it).

I thought, "why not start a thread showing the start of a project, highlighting concerns, problems and how those concerns and problems are addressed (well, in furture related threads)".

My initial thoughts, obviously will be subject to change over time. Firstly the basic setup for tables followed by my 'questions and notes' to myself (and this forum).

Main account table.
Account_ID (pk)
Account_Name
Account_Type (Asset, Liability, Savings, Investment, Shares etc.)
Account_Start_Balance
Account_Date_Created​

Main transaction type table, will only have three fields (is it really necessary? It could be directly incorporated into Category table.)
Trans_Type_ID (pk)
Trans_Type_Name (Income, Expense, Transfer)​

Main Category containing top level expense or income items (still to figure out xfer aspect).
Category_ID (pk)
Trans_Type_ID (fk)
Category_Name​

Sub Category table linked back to Category table.
Sub_Cat_ID (pk)
Category_ID (fk)
Sub_Cat_Name​

The base transaction record, will always need at least one Line item.
Trans_ID (pk)
Account_ID (fk)
Tran_Date
Tran_Description (Payee, Title)
Tran_Attachment (consider separate table for multiple attachments)​

The actual line items of a split transaction. Will always use this, even if only one transaction.
Line_ID (pk)
Tran_ID (fk)
Sub_Category_ID (fk) (under current schema, will never indicate a transfer method)
Line_Amount
Line_Note​

For the visual types:



Still to figure out how to determine between transfer and category (maybe a union query combining category/sub categories and account names - account names could be enclosed by square brakets).

Still to consider how to handle only Category name if not wanting a Sub Category item. Really don't want to force usage of Sub Categories.

If Category is Expense or Income, then only one entry. (may have to consider utilising Accounts table to record expense/income)

If Category (for want of a better term) is a Transfer, then create second entry automatically (effectively a double entry method). Will probably need a ref 'Tran_ID' or 'Line_ID' to the specific cross-referenced transfer transaction - in both entries (not accounted for at present).

If using only one Amount field then Expense/Income/Transfer will indicate whether it's a positive or negative number and will be applied automatically.

Line amounts will be totalled. Usage: user may be able to enter a total amount and then when entering Line items a remaining balance is there ready to be selected, without thought. If incorrect total amount entered have ability for user to cancel out that amount and use amounts totalled in Line items.

In order to show debit and credit columns, consider using a temporary table which includes these fields. A query will populate this table, figuring out the debit and credit component. Will be easier viewing for user (me).

For data entry of a transaction, also consider usage of a tempory table to store values.

Still to consider how to handle foreign currency. If using only one (local) currency then that simplifies things somewhat, BUT I need to handle my PayPal accounts, would like to record actual foreign amounts incurred. Initial thoughts are that there will be a Transfer Factor involved, even between local accounts which would be 1.0 and between foreign and local whatever the exchange rate is.

Not overly interested or concerned with CLOSING off year end accounts and creating new files for financial years. I much prefer to have access to historical data (loan accounts etc) and can easily report on any time period required.

Still to consider how to handle selling and buying of shares. Share pricing, per share price, can be recorded to three decimal digits. Also to account for 'End of Day' share price, not that there would be a requirement to enter this information everyday but to at least account for this.

Still to consider how best to handle account balances and maybe historical balances.

Still to consider how best to handle bank statement reconciliation. Associated to this there is a 'Date of Reconciliation' and balance.

Conclusion

At this point, my main concern is how to handle a transfer as opposed to an expense/income item. In my mind, I have to get this nutted out before addressing other issues. I do have two ideas in mind but I'll make them the subject of another thread.

I'd welcome anyone to chime in with suggestions and/or questions as to the design and method I've taken. For this post, yes, it's very broad but as it develops no doubt I'll update (and narrow down) in new threads. The above is not perfect but it's a start. Consider this brainstorming.


Steve.
 
Last edited:

llkhoutx

Registered User.
Local time
Today, 15:08
Joined
Feb 26, 2001
Messages
4,018
I don't like your column names. Take a look at the Hungarian naming convention. with its use, one immediately knows the data type of a control, days, weeks, months, years later. I use PK and FK as prefixes to primary and foreign keys, respectively, with the instant table name in the column name. Similarly, in the sql (use and view) of a query, one immediately knows how to referentialkly link tables.

I strongly suggest that you read up on naming conventions.
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 07:08
Joined
Oct 20, 2008
Messages
512
I don't like your column names. Take a look at the Hungarian naming convention. with its use, one immediately knows the data type of a control, days, weeks, months, years later. I use PK and FK as prefixes to primary and foreign keys, respectively, with the instant table name in the column name. Similarly, in the sql (use and view) of a query, one immediately knows how to referentialkly link tables.

I strongly suggest that you read up on naming conventions.

I agree with you entirely.

In this early stage I wasn't worried about precise naming conventions more about the validity of the schema.

This thread was carried on over here.

Steve.
 

Users who are viewing this thread

Top Bottom