Table Design

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi Michelle,

Here is one option. Only relevant fields are included.

budget_schema.png


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
 
Upvote 0
Thanks for your response Denis.

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

Thank you.
 
Upvote 0
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.

Code:
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.
 
Upvote 0
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
 
Upvote 0
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:

Code:
    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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top