Your earlier statement "it prompts me for a parameter for LastOilMiles
and
then prompts me for a parameter for MaxMiles" suggests you didn't include
the totals query in with your report's record source.
--
Duane Hookom
MS Access MVP
--
I created the query you said and wrote it the way you did in sql view.
After
that, I pulled it up under the normal query view and then created
another
field with the last information you gave me. Was doing it this way not
correct?
:
Did you ever create a totals query as I suggested earlier? Adding that
query
into your existing query would provide these fields.
--
Duane Hookom
MS Access MVP
--
I created a new column and entered the information you stated below.
When
I
run the query, it prompts me for a parameter for LastOilMiles and
then
prompts me for a parameter for MaxMiles. I would assume that it
shouldn't
do
this. Any idea where I went wrong? I'd assume that it should just
pull
records that fall into the category of oil changes that are 4000
miles
or
greater overdue and display it when I create a report from the
query.
:
Create a column in the query
NextOilMiles: [LastOilMiles] + 4000
and set the criteria to
<=[MaxMiles]
--
Duane Hookom
MS Access MVP
--
Last question on this, I hope. After I add the query, where do I
enter
the
criteria for LastOilMiles + 4000<= MaxMiles? Do I put it in the
criteria
field of LastOilMiles and is this the expression I need to enter?
I added the query you suggested but not the expression above,
yet.
I
just
want to know where and what should be entered as criteria.
Thanks
for
your
help!
:
I would probably start by creating a totals query that finds the
most
recent
oil change mileage and Ending Mileage for each Inventory#:
SELECT [Inventory #], Max([Oil Change Mileage] As LastOilMiles,
Max([Ending
Mileage]) as MaxMiles
FROM tblA
GROUP BY [Inventory #];
You could then use this query and find where the LastOilMiles +
4000
<=
MaxMiles
--
Duane Hookom
MS Access MVP
Here is some sample data I've got in the table for the
vehicles:
Month/Day/Year Inventory # Beginning Mileage Ending Mileage
Oil
Change
Mileage
8/31/2003 552170 112,694 116284 113,900
9/30/2003 552170 116,284
118,200
8/31/2003 552222 110,985
119,248
116,800
9/30/2003 552222 119,248
120,999
This is not all the fields in the table but they are the ones
dealing
with
mileage information. The Inventory # in the primary key in
the
table
since
the inventory # of the car is unique and there are no
duplicates
for
it.
At
the end of each month, you will enter the last day of the
month
to
identify
that month's activity.
Hope this helps. Let me know if you need anything else.
:
How about providing sample records with table and field
names?
Duane Hookom
MS Access MVP
I've got a dbase that is used for tracking our company
vehicles activity (maintenance, service dates, mileage,
etc). What would be the best way to create a report to
show when a vehicle is overdue an oil change (every 3000
miles)?
Every month information is entered on each vehicle. In my
data entry form I've got fields for beginning mileage,
ending mileage, and oil change mileage (mileage would be
entered if it was changed that month). So when I enter oil
change mileage one month and then 3 or 4 months later the
oil is changed again, the mileage is entered for the most
recent mileage in the current months record.
I'd imagine that these are the fields I would use but
don't really know how to set up the query for such a
report. Any suggestions would be appreciated.
