calculate mileage

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I would like to be able to print out a report to show what
vehicles are in need of an oil change after every 3000
miles. I've got a form that has three field dealing with
mileage and oil change miles. The fields
are "beginmiles", "endmiles", and "oilchangemileage".

Each month a record is entered in the form and the user
will give the begin and end miles info and after each oil
change they will enter the mileage at the time of the oil
change.


How should I set my query for the report to print this
info?
 
I think the following will get you going.
I created two queries, the first one checks for the
maximum oilchange mileage and minumum start mileage and
maximum end mileage in a table I called LubeSvc:
SELECT DISTINCTROW Max(LubeSvc.OilChangeMileage) AS [Max-
OilChangeMileage], Min(LubeSvc.BeginMiles) AS [Min-
BeginMiles], Max(LubeSvc.EndMiles) AS [Max-EndMiles]
FROM LubeSvc;
I named this first query LubeSvc1
than I created a second query:
SELECT IIf(IIf([Max-OilChangeMileage] Is Null,[Min-
BeginMiles]+3000,[Max-OilChangeMileage]+3000)>[Max-
EndMiles],IIf([Max-OilChangeMileage] Is Null,[Min-
BeginMiles]+3000,[Max-OilChangeMileage]+3000),[Max-
EndMiles]) AS NextLube
FROM LubeSvc1;
I named this second query LubeSvc2
I think this will get you going.
Fons
 
I'm not sure I've got this straight. Where do I need to
put these queries? Where do I reference them in a
report? Where do I enter the phrase SELECT DISTINCTROW?

Sorry for my lack of knowledge.
-----Original Message-----
I think the following will get you going.
I created two queries, the first one checks for the
maximum oilchange mileage and minumum start mileage and
maximum end mileage in a table I called LubeSvc:
SELECT DISTINCTROW Max(LubeSvc.OilChangeMileage) AS [Max-
OilChangeMileage], Min(LubeSvc.BeginMiles) AS [Min-
BeginMiles], Max(LubeSvc.EndMiles) AS [Max-EndMiles]
FROM LubeSvc;
I named this first query LubeSvc1
than I created a second query:
SELECT IIf(IIf([Max-OilChangeMileage] Is Null,[Min-
BeginMiles]+3000,[Max-OilChangeMileage]+3000)>[Max-
EndMiles],IIf([Max-OilChangeMileage] Is Null,[Min-
BeginMiles]+3000,[Max-OilChangeMileage]+3000),[Max-
EndMiles]) AS NextLube
FROM LubeSvc1;
I named this second query LubeSvc2
I think this will get you going.
Fons
-----Original Message-----
I would like to be able to print out a report to show what
vehicles are in need of an oil change after every 3000
miles. I've got a form that has three field dealing with
mileage and oil change miles. The fields
are "beginmiles", "endmiles", and "oilchangemileage".

Each month a record is entered in the form and the user
will give the begin and end miles info and after each oil
change they will enter the mileage at the time of the oil
change.


How should I set my query for the report to print this
info?
.
Click to expand...
.
Click to expand...
 
I opened the SQL view in a blank query and entered the
first code you suggested but got an error: Syntax error
(missing operator) in query expression 'Max(Mileage and
Maintenance Table.Oil Change Mileage)'

I checked and entered the info. how you suggested. Does
it have something to do with the way my table and fields
are named (with spaces between words)? This was my first
dbase and I didn't know the best format.
-----Original Message-----
I think the following will get you going.
I created two queries, the first one checks for the
maximum oilchange mileage and minumum start mileage and
maximum end mileage in a table I called LubeSvc:
SELECT DISTINCTROW Max(LubeSvc.OilChangeMileage) AS [Max-
OilChangeMileage], Min(LubeSvc.BeginMiles) AS [Min-
BeginMiles], Max(LubeSvc.EndMiles) AS [Max-EndMiles]
FROM LubeSvc;
I named this first query LubeSvc1
than I created a second query:
SELECT IIf(IIf([Max-OilChangeMileage] Is Null,[Min-
BeginMiles]+3000,[Max-OilChangeMileage]+3000)>[Max-
EndMiles],IIf([Max-OilChangeMileage] Is Null,[Min-
BeginMiles]+3000,[Max-OilChangeMileage]+3000),[Max-
EndMiles]) AS NextLube
FROM LubeSvc1;
I named this second query LubeSvc2
I think this will get you going.
Fons
-----Original Message-----
I would like to be able to print out a report to show what
vehicles are in need of an oil change after every 3000
miles. I've got a form that has three field dealing with
mileage and oil change miles. The fields
are "beginmiles", "endmiles", and "oilchangemileage".

Each month a record is entered in the form and the user
will give the begin and end miles info and after each oil
change they will enter the mileage at the time of the oil
change.


How should I set my query for the report to print this
info?
.
Click to expand...
.
Click to expand...
 
The queries below are in SQL, so you would open a new
query in design view and click on the view (just below the
file button on the top tool bar) and select sql
In the SQL window you can than enter the entire query as I
entered them below. Keep in mind that you must sunstitute
the names for the variables if they are different than
what I used.
You would base your report on the second query.
Hope this helps, if you need more help you may contact me
at my privat email"
f o n s p o n s i o @ m s n . c o m
Fons
-----Original Message-----
I'm not sure I've got this straight. Where do I need to
put these queries? Where do I reference them in a
report? Where do I enter the phrase SELECT DISTINCTROW?

Sorry for my lack of knowledge.
-----Original Message-----
I think the following will get you going.
I created two queries, the first one checks for the
maximum oilchange mileage and minumum start mileage and
maximum end mileage in a table I called LubeSvc:
SELECT DISTINCTROW Max(LubeSvc.OilChangeMileage) AS [Max-
OilChangeMileage], Min(LubeSvc.BeginMiles) AS [Min-
BeginMiles], Max(LubeSvc.EndMiles) AS [Max-EndMiles]
FROM LubeSvc;
I named this first query LubeSvc1
than I created a second query:
SELECT IIf(IIf([Max-OilChangeMileage] Is Null,[Min-
BeginMiles]+3000,[Max-OilChangeMileage]+3000)>[Max-
EndMiles],IIf([Max-OilChangeMileage] Is Null,[Min-
BeginMiles]+3000,[Max-OilChangeMileage]+3000),[Max-
EndMiles]) AS NextLube
FROM LubeSvc1;
I named this second query LubeSvc2
I think this will get you going.
Fons
-----Original Message-----
I would like to be able to print out a report to show what
vehicles are in need of an oil change after every 3000
miles. I've got a form that has three field dealing with
mileage and oil change miles. The fields
are "beginmiles", "endmiles", and "oilchangemileage".

Each month a record is entered in the form and the user
will give the begin and end miles info and after each oil
change they will enter the mileage at the time of the oil
change.


How should I set my query for the report to print this
info?
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top