Good Day all,
I am creating a budget database. I have a table called [Transactions] which contains all of my banking transactions. It has many fields, including but not limited to: Vendor, Date, Amount, Account and BudgetAccount (BAccount).
The [BAccount] field tracks which Budget Account each transaction gets assigned to, some examples would be rent, spending money, gas, etc. Currently, each budget account has a given amount with a frequency, so my cell phone might be $50 per month, I have created a query which forecast's the amount of payments till my budget end date (all budget accounts share the same end date, the end of the budget period). So if the budget end date is Aug 31, 2011, the query shows 1 expected $50 transaction for each month till the end of Aug. The problem is, my cashflow is based off earned dollars vs spent.
My database knows which date my cell phone bill is due, and when the date passes, my cashflow will give me the earned money I budgeted for that bill, anything I spend over that amount is considered a loss. However, if I pay the bill 3 days earlier, the database knows that transaction is affiliated with the cell phone budget account, but it doesn't know it is affiliated with the cell phone bill for that SPECIFIC month, and therefore my cashflow will show a incorrect loss until the cell phone due date has passed. Once the cell phone due date has passed, the databased will adjust correctly.
I would like to change this so it doesn't reflect an incorrect loss due to an earlier payment. Originally I had a second table called 'Budgeted Transaction', where the future cell phone bill payment was placed, allowing me to tract transactions against actual budget transaction records, opposed to the query method I am currently utilizing. This created redundancy issues so I tried to place future transaction right on my transaction table and considered them [open], but this becomes a tracking nightmare.
Does anyone have any ideas of how to proceed with this issue?
Any ideas, tiny tricks to major overhauls are more than welcome!
Thank you
Kavy
I am creating a budget database. I have a table called [Transactions] which contains all of my banking transactions. It has many fields, including but not limited to: Vendor, Date, Amount, Account and BudgetAccount (BAccount).
The [BAccount] field tracks which Budget Account each transaction gets assigned to, some examples would be rent, spending money, gas, etc. Currently, each budget account has a given amount with a frequency, so my cell phone might be $50 per month, I have created a query which forecast's the amount of payments till my budget end date (all budget accounts share the same end date, the end of the budget period). So if the budget end date is Aug 31, 2011, the query shows 1 expected $50 transaction for each month till the end of Aug. The problem is, my cashflow is based off earned dollars vs spent.
My database knows which date my cell phone bill is due, and when the date passes, my cashflow will give me the earned money I budgeted for that bill, anything I spend over that amount is considered a loss. However, if I pay the bill 3 days earlier, the database knows that transaction is affiliated with the cell phone budget account, but it doesn't know it is affiliated with the cell phone bill for that SPECIFIC month, and therefore my cashflow will show a incorrect loss until the cell phone due date has passed. Once the cell phone due date has passed, the databased will adjust correctly.
I would like to change this so it doesn't reflect an incorrect loss due to an earlier payment. Originally I had a second table called 'Budgeted Transaction', where the future cell phone bill payment was placed, allowing me to tract transactions against actual budget transaction records, opposed to the query method I am currently utilizing. This created redundancy issues so I tried to place future transaction right on my transaction table and considered them [open], but this becomes a tracking nightmare.
Does anyone have any ideas of how to proceed with this issue?
Any ideas, tiny tricks to major overhauls are more than welcome!
Thank you
Kavy
My Excel file runs so slow (on a really good computer) because it is so big and bulky, especialy when trying to search for transactions and reconcile them even though I archive every year. I so can't wait to get done designing the Access version!
