Loop to update Annual Budget

[Luthius]

Happy New Year for everybody

I'm developing a personal finance database.
My idea is go beyond of only insert income and expenses/outcomes.
I want to develop an annual budget so I can monitor monthly what is jeopardizing my money.

1. tblBudget
   1. idBudget (primary Key)
   2. BudgetYear (year of budget)
   3. fkCategory (Category such as Food, Health, Transport, Household, etc.)
2. tblBudgetDetails
   1. idBgtDetails (primary Key)
   2. fkBudget (Foreign Key associated to tblBudget)
   3. fkSubCategory (Such as Restaurant, Medicine, Car, etc.)
   4. referenceDate
   5. ExpenseValue

Instead of for each month I prepare budget for each category, my idea is after fill a general one, a loop go through the values and based in what I had inserted there, generate for each month (Jan to Dec) so I can have an annul budget.
Will be like:

• Update a temporary table (tblTmpBudget) with my records inserted on Budget Details table (tblBudgetDetails)
• Delete the records on Budget Details table (tblBudgetDetails)
• Read by recordset all values of tmpBudget
• Insert back on tblBudgetDetails each record splitting it by month inserting dates on referenceDate as per record of tblTmpBudget

I don't know how to generate this loop.

 

[xenou]

Hi,
just write a query. No need for recordset/loops. You update the value based on the keys (primary key and or foreign key).

 

[Luthius]

Hi,
just write a query. No need for recordset/loops. You update the value based on the keys (primary key and or foreign key).

Can you please post some example, because in this case I must "copy" the values inserted as general and replicate them for all months of the year generating the "fixed" expenses of that month.

 

[xenou]

What kind of values are being inserted as general (which I'm not even sure what that means)? Do you mean you have one value and you want to use it 12 times? Give an example.

 

[Luthius]

What kind of values are being inserted as general (which I'm not even sure what that means)? Do you mean you have one value and you want to use it 12 times? Give an example.

Exactly.
I will put one value and I want to replicate this value for all months of the year.

 

[xenou]

There are many ways to do this. Since the value is the same you can ignore the months and just insert the value. for instance, if you have 100 for phone expenses each month then just:

Update TableBudget Set BudgetAmount = 100 WHERE BudgetID = "TelephoneExpenses"

 

[xenou]

I'm not sure how complicated your budget is but in general for a first run you will probably want to fill in all the data by hand. This shouldn't be very hard. Going forward (future years) you just copy the previous years budget as the starting point and then only change the values that will change.