Tracking Auto Expenses

[LBPFinance1]

I'm an accountant and want to create a database to track rental, repair, tax
and fuel expenses for our leased vehicles. I want to enter an expense item
in Access and have the database fill in the Branch and GL Expense account.
But, I can't think of a way to do this because an auto can be transferred to
a different branch.

How can I set up a database that will look at the date of the expense and
look to see where that vehicle was located at that time to determine what GL
expense account to use?

I have limited exposure to Access and just don't know where to start with
this problem.

 

[Dorian]

You could have a Vehicle Location table with:
Vehicle Id (refers to Vehicle table)
Location Id (refers to Location table)
From Date
To Date

Whenever you enter an expense, you query this table to determine where the
vehicle was then apply to the appropriate account.
You could also look to see if there are any pre-built databases you could
use and avoid reinventing the wheel.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Steve]

Given the question you asked, you imply the expense accounts are set up by
branch. So you need the following expense account tables:
TblLeasedVehicleExpenseAccountType
LeasedVehicleExpenseAccountTypeID
LeasedVehicleExpenseAccountType (rental, repair, tax, fuel)

TblBranch
BranchID
Branch

TblGLExpenseAccount
GLExpenseAccountID
LeasedVehicleExpenseAccountTypeID
GLExpenseAccount

TblBranchGLExpenseAccount
BranchGLExpenseAccountID
BranchID
GLExpenseAccountID

When you record a vehicle expense, you need to record
BranchGLExpenseAccountID and the amount of the expense.
BranchGLExpenseAccountID identifies the Branch to charge the expense to and
identifies the expense account type.

So, to identify the Branch, you need a set of tables that lists your leased
vehicles and lists where each vehicle was at any date:

TblLeasedVehicle
LeasedVehicleID
<fields that define a specific vehicle>

TblLeasedVehicleLocation
LeasedVehicleLocationID
LeasedVehicleID
BranchID
LocationFromDate
LocationToDate

When you record a vehicle expense, first you need to record the
LeaseVehicleID and the date of the expense. From TblLeasedVehicleLocation
you will then be able to get the BranchID to charge the expense to. Second
you need to record the type of expense, LeasedVehicleExpenseAccountTypeID
from TblLeasedVehicleExpenseAccountType.

TblLeasedVehicleExpense
LeasedVehicleExpenseID
LeasedVehicleID
LeasedVehicleExpenseDate
LeasedVehicleExpenseAccountTypeID
LeasedVehicleExpenseAmount

Note that when you record a vehicle expense you don't record the BranchID or
the GL Expense Account. They are determined by the data you record in
TblLeasedVehicleExpense.

When you want to display your expense data in a form or report, all you need
is a query to return the data you want from the above tables.

If you need help setting up your database, contact me. I provide help with
Access, Excel and Word applications for a small fee.

Steve
(e-mail address removed)

 

[John... Visio MVP]

If you need help setting up your database, contact me. I provide help with
Access, Excel and Word applications for a small fee.

Steve

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP