HELP - Finding Max/Min

[Steve A]

Hi,
I have three tables... Bus, Dead and Runs which are linked together through
the Bus table. I am wanting to know how to find the minimum and maximum
values of miles travelled for each vehicle within bus. The mileage values
are stored in two tables however as they relate to different things. Not
every Reg in tblBus is necessarily in each of the other two tables, but I
need to know that vehicles lowest recorded mileage and its highest recorded
value, so that I can calculate the total mileage travelled by each vehicle
and the total cumulative mileage travelled.

The table set up is below.

I'm sure this is possible, but am having real trouble working out how. Any
help will be greatly appreciated.

Thanks

Steve A
-----------------------------------------------------------------------------------------------
tblBus
-------
Reg(Pk)

tblDead
---------
JobNo(Pk)
Reg(Fk)
StartM
FinishM
other irrelevant fields

tblRuns
--------
tblDead
---------
JobNo(Pk)
Reg(Fk)
StartM
FinishM
other irrelevant fields

 

[Wayne Morgan]

The mileage should be in one table with a field to indicate the type of
mileage. Until then, create a Union query that will bring together the two
mileage tables. Add a "static" calculated field to each table to indicate
the type of mileage. You can then find the minimum or maximum mileage by
using this query as your record source for DMin/DMax or another query.

Example:
SELECT tblDead.BusID, tblDead.StartMiles, tblDead.EndMiles, "Dead" AS
MileageType
FROM tblDead
UNION SELECT tblRuns.BusID, tblRuns.StartMiles, tblRuns.EndMiles, "Runs"
AS MileageType
FROM tblRuns
ORDER BY BusID, StartMiles;

 

[Steve A]

Thats brilliant, thanks very much!

The mileage should be in one table with a field to indicate the type of
mileage. Until then, create a Union query that will bring together the two
mileage tables. Add a "static" calculated field to each table to indicate
the type of mileage. You can then find the minimum or maximum mileage by
using this query as your record source for DMin/DMax or another query.

Example:
SELECT tblDead.BusID, tblDead.StartMiles, tblDead.EndMiles, "Dead" AS
MileageType
FROM tblDead
UNION SELECT tblRuns.BusID, tblRuns.StartMiles, tblRuns.EndMiles, "Runs"
AS MileageType
FROM tblRuns
ORDER BY BusID, StartMiles;