473,883 Members | 1,692 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multiple Vehicle Mileage Calculation

In:

http://groups.google.com/group/comp....d7faa790c65db1

james.ea...@gma il.com said:

:Ok, I've answered the first half of my own question. From a MSDN
:article, I can now use the following to reference the last primary
:key's values:
:
:
:DLookUp("milea ge_at_purchase" , "tblGas", "[prikey]=" & [prikey]-1)

I started generalizing this problem after seeing a post at
AccessMonster from someone who was about to go back to using Excel.
The subqueries work wonderfully at providing the necessary information
but the particular subqueries needed to make it all work well caused
the grouping when it came time to put it into a report to fail because
of multilevel grouping in the subqueries. I don't want to use any Make
Table queries. I will continue to look for a way to avoid those
problems. I think I understand now why the poster resorted to the use
of DLookup as an alternative to a subquery.

James A. Fortune

Dec 9 '05 #1
6 5308
James,
Can you ask your question again, I am scratching my head trying to
understand what you want to accomplish.

Dec 10 '05 #2
Pachydermitis wrote:
< James,
< Can you ask your question again, I am scratching my head trying to
< understand what you want to accomplish.
Dcbrown428 posted at accessmonster.c om on 05 Dec 2005: I'm setting up a access database to keep up with gas receipts, mileage, etc
for company vehicles. I need to be able to calculate for each vehicle the
number of miles traveled each month based on the odometer reading. On every
gas receipt the odometer reading is recorded. What do I need to do so the
program will know to take the odometer reading from the earliest entry and
subtract it from the last entry?
Response from Tom Ellison:
Dear DC:

Write a query that returns the beginning date/mileage for the month.

Use a correlated subquery to retrieve the ending date/mileage for the month.

Take the difference.

Now, this may not seem like much of an answer. If you new what a correlated
subquery is, you probably wouldn't have asked the question. You'd just know
to use one.

However, for me to give you specific query examples, I'd have to have the
name of your table and columns. If you want that, post the table name and
some data with column headings being your column names. I can work from
that to try to perform what you need. I then need you to study what I've
written and get an idea of what it's all about.

Tom Ellison You're right, I have no idea about the subquery, but I can learn! The table
name is gas info. My column headings are: date, $ amount, gallons, mileage
(this is the column where the actual odometer reading is entered). I need to
calculate the total mileage for the month. Within my query I have already
set up formulas to add the $ amt and gallons (that was the easy part).
Thanks for your help! Response from Tom Ellison is omitted. I'm sorry for leaving you with so many open variables. For the date
parameter - we will pull the report by date parameters (between [start] and
[end]). This way we can pull for a month, quarter or ytd for each vehicle by
specifying the date range. I'm not sure if the subquery info you gave me
will work with the way I have this set up, but I'm going to try just to see.
The following is an example of what the table looks like: * * * * Vehicle Date Amount Mileage Gallons MPG
3 11/1/05 32.00 15,500 15
3 11/7/05 37.50 15,800 16.2
3 11/12/05 34.00 16,100 15.7
------------------------------------------------
Totals 103.50 600 46.9 12.8 * * * * We have 20 vehicles and all the data will be sorted by vehicle with totals
for each vehicle. I hope this gives you a better idea. Response from Tom Ellison is omitted.

I think we're trying to make this more complicated than it needs to be. I
have set up several access databases, but haven't run into this type of
calculation yet. I just thought access would be a better way of doing this
report. Thanks for your help. I hate that I took up so much of your time.
I'll figure it out or just leave it in an excel spreadsheet.


After writing the following information:

:StartInfo

[gas info]
GIID VehicleID ReceiptDate Amount Mileage Gallons
1 3 11/1/05 32 15500 15
2 3 11/7/05 37.5 15800 16.2
3 3 11/12/05 34 16100 15.7
4 4 11/2/05 39.04 22001 18.3
5 4 11/9/05 45.68 22555 21.2
The goal is to get MPG values for each record in a query assuming that
the report can group on the VehicleID and do some totaling. If not, we
can create other queries that can be used by the report to get the
totals using a public function in the total box's ControlSource by
passing the SQL string for the totals query to the function.

qryComputeMPG:
SELECT GIID, VehicleID, ReceiptDate, Amount, Mileage, Gallons,
((SELECT A.Mileage FROM [gas info] AS A WHERE A.VehicleID = [gas
info].vehicleID AND A.ReceiptDate IN (SELECT Max(B.ReceiptDa te) FROM
[gas info] As B WHERE B.ReceiptDate < [gas info].ReceiptDate AND
B.VehicleID = [gas info].VehicleID))) AS [PrevMileage],
IIf(IsNull([PrevMileage]), Null, (Mileage - Nz([PrevMileage])) /
Gallons) AS MPG FROM [gas info] ORDER BY VehicleID, ReceiptDate;

!qryComputeMPG:
GIID VehicleID ReceiptDate Amount Mileage Gallons PrevMileage MPG
1 3 11/1/05 32 15500 15 Null Null
2 3 11/7/05 37.5 15800 16.2 15500 18.518...
3 3 11/12/05 34 16100 15.7 15800 19.108...
4 4 11/2/05 39.04 22001 18.3 Null Null
5 4 11/9/05 45.68 22555 21.2 22001 26.132...

The problem with simply excluding records for the report from
qryComputeMPG that have Null MPG values is that you lose the gallons
for the first record of the month for that vehicle if you do. Maybe
IIf() can be used in the totals to weed this case out. If another
query is required to obtain the totals:

qryTotalPerVehi cle:
SELECT qryComputeMPG.V ehicleID, CCur(Sum(qryCom puteMPG.Amount) ) AS
TotalAmount, Sum(qryComputeM PG.Gallons) AS TotalGallons, (SELECT
Avg(Val(A.MPG)) FROM qryComputeMPG As A WHERE A.VehicleID =
qryComputeMPG.V ehicleID AND A.MPG IS NOT NULL) AS AvgMPG FROM
qryComputeMPG WHERE (((qryComputeMP G.ReceiptDate)> =#11/1/2005# And
(qryComputeMPG. ReceiptDate)<=# 11/30/2005#)) GROUP BY
qryComputeMPG.V ehicleID;

!qryTotalPerVeh icle:
VehicleID TotalAmount TotalGallons AvgMPG
3 $103.50 46.9 18.8133993866
4 $84.72 39.5 26.1320754717

:EndInfo

I created a Mileage.mdb database to continue trying out these ideas. I
didn't feel that what I had come up with so far was adequate because of
the way the report errored at the grouping. Also, I didn't like the
stipulations for joining AccessMonster.c om so I didn't try to answer
the question directly there.

James A. Fortune

Dec 12 '05 #3
ji********@comp umarc.com wrote:
After writing the following information:

:StartInfo

[gas info]
GIID VehicleID ReceiptDate Amount Mileage Gallons
1 3 11/1/05 32 15500 15
2 3 11/7/05 37.5 15800 16.2
3 3 11/12/05 34 16100 15.7
4 4 11/2/05 39.04 22001 18.3
5 4 11/9/05 45.68 22555 21.2
The goal is to get MPG values for each record in a query assuming that
the report can group on the VehicleID and do some totaling. If not, we
can create other queries that can be used by the report to get the
totals using a public function in the total box's ControlSource by
passing the SQL string for the totals query to the function.

qryComputeMPG:
SELECT GIID, VehicleID, ReceiptDate, Amount, Mileage, Gallons,
((SELECT A.Mileage FROM [gas info] AS A WHERE A.VehicleID = [gas
info].vehicleID AND A.ReceiptDate IN (SELECT Max(B.ReceiptDa te) FROM
[gas info] As B WHERE B.ReceiptDate < [gas info].ReceiptDate AND
B.VehicleID = [gas info].VehicleID))) AS [PrevMileage],
IIf(IsNull([PrevMileage]), Null, (Mileage - Nz([PrevMileage])) /
Gallons) AS MPG FROM [gas info] ORDER BY VehicleID, ReceiptDate;

!qryComputeMPG:
GIID VehicleID ReceiptDate Amount Mileage Gallons PrevMileage MPG
1 3 11/1/05 32 15500 15 Null Null
2 3 11/7/05 37.5 15800 16.2 15500 18.518...
3 3 11/12/05 34 16100 15.7 15800 19.108...
4 4 11/2/05 39.04 22001 18.3 Null Null
5 4 11/9/05 45.68 22555 21.2 22001 26.132...

The problem with simply excluding records for the report from
qryComputeMPG that have Null MPG values is that you lose the gallons
for the first record of the month for that vehicle if you do. Maybe
IIf() can be used in the totals to weed this case out. If another
query is required to obtain the totals:

qryTotalPerVehi cle:
SELECT qryComputeMPG.V ehicleID, CCur(Sum(qryCom puteMPG.Amount) ) AS
TotalAmount, Sum(qryComputeM PG.Gallons) AS TotalGallons, (SELECT
Avg(Val(A.MPG)) FROM qryComputeMPG As A WHERE A.VehicleID =
qryComputeMPG.V ehicleID AND A.MPG IS NOT NULL) AS AvgMPG FROM
qryComputeMPG WHERE (((qryComputeMP G.ReceiptDate)> =#11/1/2005# And
(qryComputeMPG. ReceiptDate)<=# 11/30/2005#)) GROUP BY
qryComputeMPG.V ehicleID;

!qryTotalPerVeh icle:
VehicleID TotalAmount TotalGallons AvgMPG
3 $103.50 46.9 18.8133993866
4 $84.72 39.5 26.1320754717

:EndInfo

I created a Mileage.mdb database to continue trying out these ideas. I
didn't feel that what I had come up with so far was adequate because of
the way the report errored at the grouping. Also, I didn't like the
stipulations for joining AccessMonster.c om so I didn't try to answer
the question directly there.

James A. Fortune


In an unrelated post, Randy Harris said:

<You should check out the Domain Aggregate Functions - DMax, DMin,
DAvg, etc

I will use DMax or DAvg if it turns out to be absolutely necessary. It
should solve the problem though.

James A. Fortune

Dec 13 '05 #4
ji********@comp umarc.com wrote:
In an unrelated post, Randy Harris said:

<You should check out the Domain Aggregate Functions - DMax, DMin,
DAvg, etc

I will use DMax or DAvg if it turns out to be absolutely necessary. It
should solve the problem though.

James A. Fortune


That idea didn't even work. After trying nested queries and different
GROUP BY combinations I tried:

SELECT VehicleID, ReceiptDate, Amount, Mileage, Gallons, (SELECT
A.Mileage FROM [gas info] AS A WHERE A.ReceiptDate =
DMax('[ReceiptDate]', '[gas info]', '[ReceiptDate] < #' & [gas
info].ReceiptDate & '# AND VehicleID = ' & [gas info].VehicleID) AND
A.VehicleID = [gas info].VehicleID) AS PrevMileage,
IIf(IsNull([PrevMileage]),Null,CDbl((Mi leage-Nz([PrevMileage]))/Gallons))
AS MPG FROM [gas info] ORDER BY VehicleID, ReceiptDate;

I still got the error message:

Multi-level GROUP BY clause is not allowed in a subquery.

when trying to use sorting and grouping within the report on VehicleID.
It may have to do with PrevMileage being used later in an expression
rather than from using an extra subquery. I'll keep experimenting.

James A. Fortune

Dec 14 '05 #5
Hi James,
Your explanation was good.
A couple things:
Simplified your second qry considerable by removing the nested qry in
it.
Beware of averaging an average - your numbers will be skewed.
This qry has no iifs in it, but you can add one if you want to prevent
division by 0. This ignores the missing mileage by multiplying it by
0.
Hope this helps!

Result set looks like
VehicleID DateRange Amount Miles Gallons MPG
3 11/1/2005-11/12/2005 103.5 600 46.9 18.808777429467 1
4 11/2/2005-11/9/2005 84.72 554 39.5 26.132075471698 1

SELECT qryComputeMPG.V ehicleID, Min([ReceiptDate]) & "-" &
Max([ReceiptDate]) AS DateRange, Sum(qryComputeM PG.Amount) AS Amount,
Sum([Mileage]-nz([PrevMileage],[Mileage])) AS Miles,
Sum(qryComputeM PG.Gallons) AS Gallons,
Sum([Mileage]-nz([PrevMileage],[Mileage]))/Sum([qryComputeMPG].[gallons]*(-Not
IsNull([PrevMileage]))) AS MPG
FROM qryComputeMPG
GROUP BY qryComputeMPG.V ehicleID

Dec 14 '05 #6
Pachydermitis wrote:
Hi James,
Your explanation was good.
A couple things:
Simplified your second qry considerable by removing the nested qry in
it.
Beware of averaging an average - your numbers will be skewed.
This qry has no iifs in it, but you can add one if you want to prevent
division by 0. This ignores the missing mileage by multiplying it by
0.
Hope this helps!

Result set looks like
VehicleID DateRange Amount Miles Gallons MPG
3 11/1/2005-11/12/2005 103.5 600 46.9 18.808777429467 1
4 11/2/2005-11/9/2005 84.72 554 39.5 26.132075471698 1

SELECT qryComputeMPG.V ehicleID, Min([ReceiptDate]) & "-" &
Max([ReceiptDate]) AS DateRange, Sum(qryComputeM PG.Amount) AS Amount,
Sum([Mileage]-nz([PrevMileage],[Mileage])) AS Miles,
Sum(qryComputeM PG.Gallons) AS Gallons,
Sum([Mileage]-nz([PrevMileage],[Mileage]))/Sum([qryComputeMPG].[gallons]*(-Not
IsNull([PrevMileage]))) AS MPG
FROM qryComputeMPG
GROUP BY qryComputeMPG.V ehicleID


I'll consider the problem solved now since I don't know what the OP
wants to show. Note though that the OP was concerned about returning
only the records for a particular month. The Avg I showed was only an
example of something that could be done. Your solution keyed on
grouping by the VehicleID which may be fine depending on whether or not
more detailed information is required. As you point out, the nested
query is only required for more detailed information for a given
VehicleID. Thanks for posting your SQL. I tried it in a report and
had no error when grouping by VehicleID with it.

James A. Fortune
CD********@Fort uneJames.com

Dec 14 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
6353
by: noname | last post by:
Hello all, I use Access to keep track of approx. 80 vehicles. I have a table of vehicles with specs. I have another of service records linked to the vehicle table. Works great! I can expand each vehicle to see what service I have performed on just that vehicle. I also have tables for billing categories, employees and departments. So far so good. Now I need to make it to where I can enter the fuel used by each vehicle when someone fills...
4
2392
by: james.eaton | last post by:
Hi All, I have some experience with SQL, but this one stumps me. I have created a table to track my gas purchases (see below). But, each time I enter a new purchase, I'm manually calculating gas mileage as (gallons_purchased / mileage_at_purchase - mileage_at_purchase). I can't figure out I can easilly get gallons_purchased and mileage_at_purchase into a SQL statement, but how do i reference the last entry's mileage_at_purchase in my...
10
1241
by: Kishor | last post by:
Hi, I am looking for the solution to my problem. this is related to inheritance (Inheriting properties of base class in multiple quantity) . I am writing code in VB.net language, I am having two classes clsVehicle and clsWheel. clsWheel has two properties diameter and pressure. clsVehicle has two properties Name and Make. I want to implement these classes this way
5
2129
by: Neelesh Bodas | last post by:
This might be slightly off-topic. Many books on C++ consider multiple inheritence as an "advanced" concept. Bruce Eckel says in TICPP, volume 2 that "there was (and still is) a lot of disagreement about whether is essential in C++". Are there any disadvantages of using multiple inheritence?
4
3148
by: tlyczko | last post by:
Hello, I have read about currency calculations, etc. in this newsgroup, and I understand that currency math will be sufficiently accurate for what I need to do for a mileage/expense report. Where I'm stuck is what data type etc. I should use for the mileage distance number -- single?? decimal?? for the amount of mileage distance traveled, to two decimal places maximum, but allowing for no decimals or one decimal place or two decimal...
1
1294
by: Victor | last post by:
Hi, I am trying to define a Key element for a vehicle schema. I have three ways to uniquely identify a vehicle such as URN, UnitID and Vin. I'd like to make a rule that ensures that at least one of the three is present, but 2 or all 3 can be present. I tried something like below, but it does not seem valid. Thanks in advance for any assistance anyone may be able to provide. Portion
6
3524
by: xdeath | last post by:
Hi guys, at the moment, im rushing to get my assignment done as the dateline is soon. Haven't had much time lately, so i'm in need of some help. Im supposed to create a program that : Has a Vehicle superclass, and a Taxi subclass. 1) Can accept new Taxi into an array 2) Can delete old records of Taxi from the array 3) Have to be able to retain the previous added data (which i think most likely im gonna do it by saving and reading the...
2
3815
by: Warren Hoskins | last post by:
I'm going to have to break this up into files however I receiving errors when I try to compile this. I am confused about this program Please help.I.m using Visual C++ platform, XP operating system enclosed are the errors I'm getting and the code Vehicle.cpp Vehicle.cpp(35) : error C2511: 'Person::Person(Person &)' : overloaded member fu nction not found in 'Person' Vehicle.cpp(10) : see declaration of 'Person'
1
2007
by: Kye | last post by:
It has been a while since using vb an now I have started a project and I am not sure where my mistake is in my code. Basically what I am doing is trying to build a multiple search string with certain criteria being asked, the criteria is in 4 separate fields with a search where I am able to enter multiple information, or just search a single field . Now it works to a certain extent but when trying to enter a double field for model and colour...
0
9932
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9778
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10730
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10405
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9559
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5980
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4602
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3228
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.