Table Design

[koolwaters]

Hi,

I need some assistance with the designing of tables to track budget. The scenario is:

Each department receives a budget at the beginning of every fiscal, which is used for expenses in the department. At the end of the fiscal, the balance on the budget is cleared to zero and a new amount is allocated for the upcoming fiscal.

I need to be able to keep track of all transactions made per fiscal as well as maintain the historical data.

So if in 2009 the budget was $1,000 and only $800 was spent, at the beginning of the 2010 fiscal, the budget will be $1,000 and at the beginnning of 2011 fiscal, $1,000. The budget allocated can be increased or decreased by management.

So essentially, I need to be able to:

Record the allocation of the budget by department
Record all of the transactions by department
"Reset" the budget allocated at the end of each fiscal
Maintain all historical data

I hope someone can assist me with the best approach for designing the tables to capture this information.

Thanks for any feedback.

 

[SydneyGeek]

Hi Michelle,

Here is one option. Only relevant fields are included.

To use, you would have a Budgets subform on the Departments form, with DeptID as the Master and Child fields.
Put a button on the subform to go to another form (or create a second subform for transactions), using BudgetID as the link.
This setup should let you put any desired budget for a dept; keep historical data; and create reports / queries showing unspent / overspent budget allocations.

Denis

 

[koolwaters]

Thanks for your response Denis.

I will try your suggestions today and let you know if it works.

Thank you.

 

[koolwaters]

Hi Denis,

Thanks again for your help. I have made one modification, instead of FiscalYear, I have FiscalDate where I entered the start of our Fiscal.

Your suggestions work but I am having an issue.

I have a form that captures employee loans. The loan amount is also deducted from the department to which that empployee belongs. On my loan form, I have a command button, where I want to deduct the loan amount disbursed from the department's budget for that employee. I have a created a form based on tblTransactions as you advised below.

Put a button on the subform to go to another form (or create a second subform for transactions), using BudgetID as the link.

My challange is linking back the Budget ID in tblTransactions to the BudgetID in tblBudget.

The BudgetID is a combo box on the form but I would like to have the FiscalDate being displayed as below.

SELECT tblBudget.BudgetID, tblFiscalYears.FiscalYearID, tblFiscalYears.FiscalYear FROM tblFiscalYears INNER JOIN tblBudget ON tblFiscalYears.FiscalYearID = tblBudget.FiscalYearID;

I have entered the budget for the previous and current fiscal and because there are 28 departments, I have 56 rows in tblBudgets. When I select the BudgetID combo box, I am seeing 56 fiscal dates, but I only want to see the two years I have entered.

I am not sure if I have gone about it the right way so I would appreciate your guidance.

Thanks.

 

[SydneyGeek]

Hi Michelle,

You need criteria in that combo box statement to limit the department to the current main form record. Something like:

WHERE tblBudget.DeptID=Forms!WhateverYouCalledTheMainForm!DeptID

Denis

 

[koolwaters]

Hi Denis,

I think I have sorted it.

On the Loans Details form, I have a command button called cmdDedFromBudget.

This opens a dialog form, where the fiscal year is selected from a combo box called txtFiscalYear. I also have a text box on this form, which pulls the FiscalID based on the Fiscal Year selected in the combo box. =[txtFiscalYear].[column](0)

On this dialog form, I have a command button and in the On Click property, I open the Budget Transactions form, and using a Dlookup, I assign the BudgetID:

    Forms!frmBudgetTransactions.BudgetID = DLookup("[BudgetID]", "tblBudget", "[FiscalYearID]=" & Me.txtFiscalYearID)

I could not get it done using a Where clause. I am not sure if this the best way to get it done but it appears to be working.

Thanks again for your help and feel free to offer a better suggestion if you think of one.