Report Calculation

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

Todd

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.
 
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.
 
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
 
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!

Duane Hookom said:
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


Todd said:
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.
Click to expand...
Click to expand...
 
Create a column in the query
NextOilMiles: [LastOilMiles] + 4000
and set the criteria to
<=[MaxMiles]

--
Duane Hookom
MS Access MVP
--

Todd said:
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!

Duane Hookom said:
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


Todd said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.

Duane Hookom said:
Create a column in the query
NextOilMiles: [LastOilMiles] + 4000
and set the criteria to
<=[MaxMiles]

--
Duane Hookom
MS Access MVP
--

Todd said:
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!

Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
--

Todd said:
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.

Duane Hookom said:
Create a column in the query
NextOilMiles: [LastOilMiles] + 4000
and set the criteria to
<=[MaxMiles]

--
Duane Hookom
MS Access MVP
--

Todd said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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?

Duane Hookom said:
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
--

Todd said:
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.

Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
--

Todd said:
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?

Duane Hookom said:
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
--

Todd said:
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.
Click to expand...
Click to expand...
Click to expand...
 
I'm trying to get this straight. I created the totals query, which is the
sql statement you suggested in the beginning. Then I added the additional
column to the totals query with the expression as you explained. I haven't
created the report for this information yet. All I was doing was running the
query. I'm about to create an oilchangeneededreport, at which time I should
use the totals query as my record source. Is this correct?

Sorry for my lack of knowledge on this.

Duane Hookom said:
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
--

Todd said:
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?

Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Try a query like:

SELECT [Inventory #],
Max([Oil Change Mileage]) As LastOilMiles,
Max([Ending Mileage]) as MaxMiles
FROM tblA
GROUP BY [Inventory #]
HAVING Max([Oil Change Mileage])+3000<Max([Ending Mileage]);

--
Duane Hookom
MS Access MVP
--

Todd said:
I'm trying to get this straight. I created the totals query, which is the
sql statement you suggested in the beginning. Then I added the additional
column to the totals query with the expression as you explained. I
haven't
created the report for this information yet. All I was doing was running
the
query. I'm about to create an oilchangeneededreport, at which time I
should
use the totals query as my record source. Is this correct?

Sorry for my lack of knowledge on this.

Duane Hookom said:
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
--

Todd said:
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.
Click to expand...
Click to expand...
Click to expand...
 
That did the trick. Thanks for hanging in there with me. I appreciate the
help! I guess adding that HAVING line in there was the fix.

Duane Hookom said:
Try a query like:

SELECT [Inventory #],
Max([Oil Change Mileage]) As LastOilMiles,
Max([Ending Mileage]) as MaxMiles
FROM tblA
GROUP BY [Inventory #]
HAVING Max([Oil Change Mileage])+3000<Max([Ending Mileage]);

--
Duane Hookom
MS Access MVP
--

Todd said:
I'm trying to get this straight. I created the totals query, which is the
sql statement you suggested in the beginning. Then I added the additional
column to the totals query with the expression as you explained. I
haven't
created the report for this information yet. All I was doing was running
the
query. I'm about to create an oilchangeneededreport, at which time I
should
use the totals query as my record source. Is this correct?

Sorry for my lack of knowledge on this.

Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top