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.
0
Utf
3/24/2010 5:06:01 PM
access 16762 articles. 3 followers. Follow

4 Replies
626 Views

Similar Articles

[PageSpeed] 8

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.
0
Utf
3/24/2010 5:24:02 PM
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
santus@penn.com




"LBPFinance1" <LBPFinance1@discussions.microsoft.com> wrote in message 
news:BE67F626-A2BB-4FD1-A051-AC4D94CAEF98@microsoft.com...
> 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. 


0
Steve
3/24/2010 11:36:12 PM
"Steve" <notmyemail@address.com> wrote in message 
news:exu0Lr6yKHA.3884@TK2MSFTNGP06.phx.gbl...
>
> 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



0
John
3/24/2010 11:56:17 PM
"Steve" <notmyemail@address.com> schreef in bericht 
news:exu0Lr6yKHA.3884@TK2MSFTNGP06.phx.gbl...
>
> If you need help setting up your database, contact me. I provide help with Access, Excel 
> and Word applications for a small fee.
>
> Steve
> santus@penn.com
>


-- 
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 


0
StopThisAdvertising
3/25/2010 9:42:03 AM
Reply:

Similar Artilces:

Problem with Auto recover #2
I was working on a newsletter spread, and was almost finished, when I tried to save it. It gave me an error message that there was 'Not enough memory' and then closed the Publisher file. It gave the dreaded pop-up asking if I wanted to send a report to Microsoft, and told me all unsaved data would be lost. When I tried to open my file using the Auto Save it was non-existent. Is there a hidden file where it may be? Somebody please help or else I have to do the whole newsletter from scratch. Thanks, Alan You can look in the temp folder and hope for the best. Open Windows Expl...

Auto Cell Shading #2
Exactly what I was after! You guys are great, that has been two big problems for me solved in one day. Thank you very much for your help. -- JackJJW ------------------------------------------------------------------------ JackJJW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16251 View this thread: http://www.excelforum.com/showthread.php?threadid=276551 ...

auto click hyperlink?!
Hi, I am a very basic user new to macros and vba. My problem is that I recive online orders to which i need to click an auto generated unique order hyperlink to confirm receipt of the order within a specified time period. This is a mundane task having to be on 'standby' and was wondering if there was a way to have this link automatically clicked when the email arrives. Just to reiterate the link has a unique reference and is different on each email. Please bare in mind that I am a very basic user and would probably require step by step instructions. Many thanks in advan...

auto email advice
Hello all, using Outlook '03... I'm looking for some advice on how to send an automatic e-mail every day/week/month. I've searched this group and have found a few ideas; none, however, seem to fit exactly what I need. I would like to send an e-mail to a group (many people, one address) on a daily, weekly, or monthly basis. This message is a very basic reminder in text (no attachments). I need this message to execute whether I open Outlook or whether I'm on the beach somewhere. The most apparent answer appears to be a recurring Task. I don't want it to get complicate...

Sheet protection code conflicts with Pivot Table "auto refresh"
I received the following code from Debra Dalgleish, to password protect all worksheets, setting certain exceptions for objects, etc. Public Sub ProtectAll() > Const PWORD As String = "mysecretword" > Dim wsSheet As Worksheet > For Each wsSheet In Worksheets > wsSheet.Protect Password:=PWORD, _ > DrawingObjects:=False, _ > AllowFiltering:=True, _ > AllowUsingPivotTables:=True > Next wsSheet > End Sub It works well, except for one thing: when I open the file, I get an error message which emanates fr...

Gannt, Tracking chart
How to set the standard views like Gannt chart, Tracking view, Task usage etc..so that it will be usable in all files... Read about global.mpt in Help. Access via the Menu: Tools/Organize where you can copy fields, calendar, forms, views, groups, toolbars, tables, reports, maps, filters, and modules between files and global.mpt. Put the views et. al. that you want available to all files opening with that global.mpt to make them available. I recommend you not mess with the standard views in global.mpt, but use your own views named with your own nomenclature. --rms ww...

Auto Send Email With Attached Word Doc Created From Merge
I have an excel file. This file has 4 columns - sign, first name, last name and Email. My goal is to create a button on each line (or link) that will automatically create an email using the email address in that record. Not only will it say Dear first name and last name, but it will also have a word document attached. This word document will automatically generate with the sign number and save itself as sign###.doc. (# = number in the sign field) The email will then open in outlook with: Subject of - "Testing 123" Body of - "Dear First name and last name," Attachment of -...

Excel spreadsheet to track MPG
"Geekster" <noemail@xyahoox.com> wrote on 21 Jan 2008 in group microsoft.public.money: > Hey, Steve - I could use one of those if you wouldn't mind sending > details. > > Thank you! Here's a small one with a graph. You put data in the "Date", "Mileage", and "Gallons" fields. Don't edit the other fields or you'll destroy the formulas. I don't know whether this group allows binaries, but I'll try posting. I didn't see any other groups labeled "binaries". There are two attachments -- one in E...

Message Tracking with Multiple Entries
I am experiencing similar issues to this article: http://support.microsoft.com/kb/330093 However, I am running Exchange 2003 SP2 and the sender is not sending from a PDA device and I don't have ActiveSync enabled. Anybody have any ideas? ...

Question about "track Message" in Exchange 5.5
I have a question about using message tracking on Exchange 5.5. Last week our antivirus software blocked a couple of ".cpl" attachments. The user that was sending these normally would not be working with these type of files, so we checked her system. Also, she was on vacation at the time of the outgoing emails. There was no virus, but she did have a ton of spam in her inbox and couple of spyware applications installed (she is one of our "problem users" in this respect). I was using the Exchange "track message" function to search for messages this user sent o...

E-mail auto deleting
In our office one of the users has a problem with Outlook choosing someone (usually from the corporate address book) and deleting their e-mails automatically. It acts just like she'd set up a rule to delete the e-mail, but I cannot locate anything she has done wrong. I've tried uninstalling and re-installing Office on her workstation. It didn't change anything. She's using Outlook XP and we have an Exchange server 5.5. HELP! ...

Outlook - Exchange Rule ?
We are currently using Outlook 2000 SP3 with Exchange 2000 Standard SP3 on a W2k SP4 server. To work around the 16 gig limitation of the Information Store, I want to create a rule within Outlook to move emails messages older than 6 months to a user public folder within Exchange. The idea would be similar to the way auto-archiving works except I don't want the email to be archived in a pst file. Through the rules wizard I can move emails to a specific folder based on dates range, but these dates are static. Is there any way to either adjust a rule to work with the current date (i.e. 'al...

Money expense scheduling problem
I'm using money 2003 standard at home, and I am unable to schedule a reocurring bill every three weeks. They have a multitude of choices available, but not one for that, nor any "custom" schedule. Does anyone have any ideas as to how I might be able to schedule this? Would I be able to do this in 2004? In case anyone is wondering, we get groceries every three weeks and that's why I need the three weeks option - am I the only person that has ever had a reocurring bill for three weeks? I must be special... Tom ...

How to enter and track an ESPP
I have not been able to figure out how to track and ESPP where the following information will be captured Grant Date Purchase Date Purchase Price Discount Can this information be tracked in Money 2004? I am trying to track a ESPP (Employee Stock Purchase Plan) NOT a ESOP (Employee Stock Option Plan). For this, I created a new separate account under etrade and I have my recurring paycheck contributions go into this account as cash (I use the split entry on my paycheck to transfer the money to the etrade account). When the purchase happens, I use the cash to purchase the stock at the specifi...

Message Tracking
Is there a way to track an e-mail all the way to it's destination once it leaves my building. Kevin, In effect no due to technological differences between mail platforms. The most that you can hope for is a read or delivery recipient. -- Jared Gradle Exchange Support Professional ****** Disclaimer ****** This posting is provided "AS IS" with no warranties, and confers no rights. Note: Please do NOT reply to this e-mail address. It is used for newsgroup purposes only. "Kevin" <anonymous@discussions.microsoft.com> wrote in message news:119240F8-2BF8-4AF7...

Auto minimum on Y Axis
Hi all - If someone can help me here I would be most grateful. I have a chart which plots values over a time period of say 15 years. The value is entered in one cell and then a macro is run to generate the returns for each of the 15 years which the chart then plots. The problem is when the fund value is high say 1,000,000 originally and the returns are low, then it doesnt change much, but the Y axis starts at 0 so there is a massive gap and then you can't see much differential in the line graph. I have tried changing the chart options, but as the returns are variable I can't put it ...

Auto Cad 2006 activation code????????
Hi I need an activation code for auto cad 2006 the request code is: QV20 WHLQ 7H5Q FX75 W5UQ E6FW 90P2 Serial number is: 633-12345678 Thanks All. *** Sent via Developersdex http://www.developersdex.com *** ...

Journal tracking of all documents
I'm looking for a way of getting Journal to track ALL documents (mainly photoshop, indesign and autocad) but I'd like to do it from Outlook rather that right plugins for every peice of software that I want to track files for....particulary as not all software is customizable. Not really sure of the best way of doing this....I'm hoping there are some registry hacks to force this or maybe there is some sample VBA / VB.NET code out there.... Any suggestions will be greatly appreciated.. TIA Russ Not possible - Outlook Journal will only track Microsoft Office = programs. ...

tracking exchange rates
Running M2005. While one can set a single currency exchange rate, it does not appear that Money supports exchange rate histories? Is that correct? If my base currency is US $ but I have both US $ and CAD $ assets, it would be nice to know how the change in the USD/CAD rate over the past year has impacted my networth as a function of time. In microsoft.public.money, Dave Programmer wrote: >Running M2005. While one can set a single currency exchange rate, it >does not appear that Money supports exchange rate histories? Is that >correct? Yes. >I my base currency is US $ but I ha...

Auto Number in List Control
What code can I use to automatically display a number, or preferably, text that will display the same "receipt number" as the previous record or the next successive number. For example, I enter a receipt number in a record...when I start the next record, on a form, I want to be able to click in a list box the same receipt number as before or the next receipt number, which would be just the next number in line. -- Gator ...

Deleting logs from tracking.log in 5.5
some logs got deleted "on the fly" from the tracking.log folder (so, with all services running) I'm wondering if this might affect my exchange server adversely ... don't see anything yet, but I'm holding my breath and reserving the right to throw someone in the fire. anyone? thanks in advance, Mauricio. On Thu, 9 Nov 2006 06:09:02 -0800, mcalvo <mcalvo@discussions.microsoft.com> wrote: >some logs got deleted "on the fly" from the tracking.log folder (so, with all >services running) > >I'm wondering if this might affect my exchange s...

Auto Archive #3
Any tips on retrieving emails that have been auto archived by mistake? Can I put them back into outlook automatically? Open the mail archive and drag the messages back to your active .pst file. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: Silver <adrian@grainger.vg> asked: | Any tips on retrieving emails that have been auto archived | by mistake? Can I put them back into outlook | automatically? ...

auto Selecting or Clicking a field
How to I set VB to automatically selecting a field (in the same way as manually clicking on a listbox)? I have a form with [fo_num] combo box field. After making a selection from this field, the list box [fo_mgr] is updated. I have no problems in my form. The proble is, it does not update the [fo_mgr] field in my table. It only saves the data onto the table whenever I click on the [fo_mgr] manager list box. I would like to be able to code this in VB so the user doesn't need to click on the [fo_mgr] to update the table with the new info. (I posted a similar question in public.access b...

Auto-populate fields
Hello, I have command button in the master form, i would like to write code to copy the data from field1 to field2 in the subform, consider that i have many records in the sub form. i wrote this code [Form]![TBL_Invoice Details subform2]![Approved Amount] = [Form]![TBL_Invoice Details subform2]![Billed Amount] but this will execute only for the first line, how i can make it loop for the rest and copy the data from field1 to field2 in the sub form? any help? -- *new access developer* Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200...

Rules
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3347046808_16647027 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit For some reason my auto reply is not working now...I have had the same set up for a few years now. But when I set up auto reply the message is sent to my out box but never gets sent....it just stays there. this is how I have my rule set up...see link to photo.....any suggestions...?? Entourage 12.1.0 OSX 10.5.8 http://i284...