Physical Address

304 North Cardinal St.
Dorchester Center, MA 02124

Part 1 – Setting Up Your Budget Tracking System

Feeling burdened by your finances? Taking control of your spending starts with understanding where your money goes. In this first part of our “Budget and Expense Tracking” series, we’ll guide you through the essential steps of setting up your budget tracking system in MS Access, including designing tables to store your financial data and building user-friendly forms to enter your monthly budget and expenses easily.

We have tried to make things simple to learn. The project contains the building blocks of the Database related to Tables, Queries, and Forms in design with advanced features.

Benefits of the Budget and Expense Tracking Project

This project can convert time-consuming and complex manual expense data entry tasks into a risk-free migration that eliminates human errors.

After applying the integration of budget and expense-tracking software any business can gain:

  • Automated routine tasks
  • Tracking expenditures in real-time
  • Mitigate the risk of human errors associated with manual processes to increase productivity
  • Saving costs by analyzing
  • And position businesses for sustained growth and success.

Tables for Setting Up Your Budget Tracking System

There are 3 tables to be created:

1. Budget Allocation Table

Create the first table related to Budget Allocation which should include these fields:

Field NameData TypeDescription
bidAutoNumberBudget ID
bdtDate/TimeBudget Date
byearShort TextBudget Year
bmonthShort TextBudget Month
bdurationCalculatedBudget Duration
bamountCurrencyBudget Amount
bdetailShort TextBudget Detail
bstatusShort TextBudget Status

Budget Allocation Table

The first table we will create out of three tables is about the Budget Allocation which contains a unique ID field of course with the Data Type of Auto Number.

Our budget allocation will depend on the interval of a month, so create the Year and Month selection dropdown and combine them in a third field. The date Serial function will be used in Expression Builder to get the date from the Year and Month fields.

Next, we will require the Amount of the Budget, a detail field, and the remaining balance from the budget field. The Status field will decide whether the month is closed or in open status.

The default value of the Year and Month should be the current year and month. To do this we will use the Format function in the Default Value section of the properties. Ok, now converting the date into the Month and Year format like this.

2. Expense Accounts Table

Field NameData TypeDescription
ac_exp_idAutoNumberExpense Account ID
ac_exp_nameShort TextExpense Account Name
ac_exp_catShort TextExpense Account Category
ac_exp_cat2Short TextExpense Account Category 2
ac_exp_percentageNumberExpense Account Percentage
ac_exp_statusShort TextExpense Account Status

Expense Account Table

The second table is related to General Expenses Accounts included in a business to assign a ratio in percentage for each expense. This table contains Expense names with dual categories which we will be used to analyze the expenses later in this project.

3. Expense Table

Field NameData TypeDescription
exp_idAutoNumberExpense ID
bidNumberBudget ID
exp_dtDate/TimeExpense Date
ac_exp_idNumberExpense Account ID
exp_detailShort TextExpense Detail
exp_percentageShort TextExpense Percentage
exp_amountShort TextExpense Amount

Expense Table

The third table is about the expenses made in the business from time to time. The field name “bid” will be used as a relational ID with the Budget table with a “one to many” relationship. We will use Lookup Wizard for a dropdown of expenses based on the Expenses table that we have created.

Tables Relationships

Most probably all 3 tables should join each other based on the ID and “one to many” relationship like this:

Budget and Expense Tables Relationships

Setting Up the Forms

Form Wizard will help us to create a form based on Table fields. There would be 3 forms to be built

Budget Allocation

The first Form we are creating is about Budget Allocation based on the budget Table.

The “Change” button in the subform with each record is used to change the status of the record to close the month.

All objects including text boxes and buttons must have unique names to use the references in coding. In this project, we are going to use the complete MS Access CRUD functions which are to Create, Read, Update, and Delete functions of the Database.

The final Budget Allocation Form should look like this:

Budget Allocation Form

The progress bar will used to indicate the utilization of the budget quota in percentage and only submit the form if the percentage is 100%. Admin may adjust the percentage in between the accounts. We will make it functional in the next posts.

The Delete button will confirm the removal of the account.

With the addition of some data, the form will look like this:

Expense Accounts Form

Expense Entry

Create the Expense Data Entry Form which will be based on both Budget and Expense tables.

At this stage, if you have an error related to no relation between tables, you need to establish the one-to-many relationship between tables.

Apply format (“mmm-yy”) to the Duration field. The form contains a SubForm and after design, finally, it will look like this:

Expense Entry Form

Leave a Reply

Your email address will not be published. Required fields are marked *