If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Tracking Auto Expenses
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. |
#2
|
|||
|
|||
Tracking Auto Expenses
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". "LBPFinance1" wrote: 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. |
#3
|
|||
|
|||
Tracking Auto Expenses
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 "LBPFinance1" wrote in message ... 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. |
#4
|
|||
|
|||
Tracking Auto Expenses - stevie PIMPS again!
"Steve" wrote in message
... 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 |
#5
|
|||
|
|||
Tracking Auto Expenses
"Steve" schreef in bericht ... If you need help setting up your database, contact me. I provide help with Access, Excel and Word applications for a small fee. Steve -- Get lost $teve. Go away... far away.... Again... Get lost $teve. Go away... far away.... No-one wants you here... no-one needs you here... This newsgroup is meant for FREE help.. No-one wants you here... no-one needs you here... OP look at http://home.tiscali.nl/arracom/whoissteve.html (Website has been updated and has a new 'look'... we have passed 10.000 pageloads... it's a shame !!) Arno R |
Thread Tools | |
Display Modes | |
|
|