What will work best here

[sonofroy]

I have a query with three tables looking at these fields

[Vehicle Number] [Mileage 1] [Mileage 2]

Mileage 1 is the exact mileage for the said vehicle number

Mileage two is when the vehicle is due for an oil change

I would like to build a query that will tell when Mileage 1 is approx. 500
miles from Mileage 2 (When it would be due for an oil change). What is the
best function to use here. I have tried >=,<=, and Max functions but no luck.

Lastly is it possible to have the query automatically quit and close if it
returns no values?

Thanks

 

[Jerry Whittle]

Last question first: a query? No. A report? Yes. There is an On No Data event
that you could use to close the report.

Seems to me that you could use the DMax function on both fields. One thing
that you need to plan for is if the oil change is late.

Seems to me that the following would work in a query. The big problem would
be if there are multiple records being returned for each [Vehicle Number].
You may need to do a Totals query and Group by [Vehicle Number] then do a Max
of the two Mileage fields.

OilChangeDue: [Mileage 2] - [Mileage 1]

Criteria <500

 

[sonofroy]

The Mileage 1 will be inputted every day at the end of the day for each
vehicle that is being used.

Last question first: a query? No. A report? Yes. There is an On No Data event
that you could use to close the report.

Seems to me that you could use the DMax function on both fields. One thing
that you need to plan for is if the oil change is late.

Seems to me that the following would work in a query. The big problem would
be if there are multiple records being returned for each [Vehicle Number].
You may need to do a Totals query and Group by [Vehicle Number] then do a Max
of the two Mileage fields.

OilChangeDue: [Mileage 2] - [Mileage 1]

Criteria <500
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query with three tables looking at these fields

[Vehicle Number] [Mileage 1] [Mileage 2]

Mileage 1 is the exact mileage for the said vehicle number

Mileage two is when the vehicle is due for an oil change

I would like to build a query that will tell when Mileage 1 is approx. 500
miles from Mileage 2 (When it would be due for an oil change). What is the
best function to use here. I have tried >=,<=, and Max functions but no luck.

Lastly is it possible to have the query automatically quit and close if it
returns no values?

Thanks

 

[John Spencer]

Can you explain a bit more about three tables looking at the three fields listed?

I can guess that you have multiple records for a vehicle with its mileage for
recorded every day. And in another table you have data on when the oil change
is due or you have a table with vehicle types and the intervals when oil
changes are due by type. Or you have a table ...

Many variations can exist on how you have structured your data.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[sonofroy]

I have one table with the vehicle type and [tblvehicle].[Vehicle Number],
another table where [tbldispatch].[Mileage 1] is inputed at the end of every
day the vehicle is out and returned back, the last table tells you when the
oil change is due [tblpm].[Mileage 2] these tables are are all link back (one
to many rel.) to the [vechID] on [tblvehicle].

Can you explain a bit more about three tables looking at the three fields listed?

I can guess that you have multiple records for a vehicle with its mileage for
recorded every day. And in another table you have data on when the oil change
is due or you have a table with vehicle types and the intervals when oil
changes are due by type. Or you have a table ...

Many variations can exist on how you have structured your data.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a query with three tables looking at these fields

[Vehicle Number] [Mileage 1] [Mileage 2]

Mileage 1 is the exact mileage for the said vehicle number

Mileage two is when the vehicle is due for an oil change

I would like to build a query that will tell when Mileage 1 is approx. 500
miles from Mileage 2 (When it would be due for an oil change). What is the
best function to use here. I have tried >=,<=, and Max functions but no luck.

Lastly is it possible to have the query automatically quit and close if it
returns no values?

Thanks

 

[John Spencer]

Perhaps something along the lines of the following will work for you

SELECT tblDispatch.[Vehicle Number]
, Max(tblDispatch.[Mileage 1]) as LatestMiles
, tblPM.[Mileage 2] as DateDue
FROM tblDispatch INNER JOIN tblPM
ON tblDispatch.[Vehicle Number] = tblPM.[Vehicle Number]
GROUP BY tblDispatch.[Vehicle Number]
, tblPM.[Mileage 2]
HAVING tblPM.[Mileage 2] - Max(tblDispatch.[Mileage 1]) < 500


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have one table with the vehicle type and [tblvehicle].[Vehicle Number],
another table where [tbldispatch].[Mileage 1] is inputed at the end of every
day the vehicle is out and returned back, the last table tells you when the
oil change is due [tblpm].[Mileage 2] these tables are are all link back (one
to many rel.) to the [vechID] on [tblvehicle].

Can you explain a bit more about three tables looking at the three fields listed?

I can guess that you have multiple records for a vehicle with its mileage for
recorded every day. And in another table you have data on when the oil change
is due or you have a table with vehicle types and the intervals when oil
changes are due by type. Or you have a table ...

Many variations can exist on how you have structured your data.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a query with three tables looking at these fields

[Vehicle Number] [Mileage 1] [Mileage 2]

Mileage 1 is the exact mileage for the said vehicle number

Mileage two is when the vehicle is due for an oil change

I would like to build a query that will tell when Mileage 1 is approx. 500
miles from Mileage 2 (When it would be due for an oil change). What is the
best function to use here. I have tried >=,<=, and Max functions but no luck.

Lastly is it possible to have the query automatically quit and close if it
returns no values?

Thanks