Reports wiht calculations

[Guest]

Hi,
I am looking to produce a form which will show totals for values when
certain criteria are met, but I have no idea how to do this.
I have a form where a start and finish mileage and registration number are
entered for one journey and then on the form a box calculates the total
mileage.
What I need to do is produce a report that will give me the total mileage of
all journeys travelled by a vehicle over a period of time.
Firstly how do I get my report to destinguish the earliest start and last
finish and then calulate its mileage, whilst also seperating the data by the
vehicle registration.
Thanks in advance.
Steve A

 

[Arvin Meyer]

Hi,
I am looking to produce a form which will show totals for values when
certain criteria are met, but I have no idea how to do this.
I have a form where a start and finish mileage and registration number are
entered for one journey and then on the form a box calculates the total
mileage.
What I need to do is produce a report that will give me the total mileage of
all journeys travelled by a vehicle over a period of time.
Firstly how do I get my report to destinguish the earliest start and last
finish and then calulate its mileage, whilst also seperating the data by the
vehicle registration.

An aggregate (Totals) query with the vehicle number and 2 columns for the
mileage will get the min and max. Something like:

SELECT MyTable.VehicleID, Min(MyTable.Mileage) AS MinOfMileage,
Max(MyTable.Mileage) AS MaxOfMileage
FROM MyTable
GROUP BY MyTable.VehicleID;

Run a second query to calculate the difference, or use a textbox on the form
for the calculation. If you need the total mileage of all vehicles, use a
textbox in the footer to calculate it.

=Sum(txtDifference)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thats Brilliant, I've only just had chance to try it, but it worked just as I
hoped.
Thanks very much!
Steve