Calculate Mileage Reports

[DFCRJ]

I'm trying to create a table so multiple users can ask for mileage reports.
Currently the database contains a "mileage" table that list the VIN, FLEET #,
Drive ID, and a column for each months ending odometer reading. what i need
to be able to do is give the user to ability to select what time frame they
want to compare.
So if they want to see how many miles somone traveled from Jan thru March or
from June thru August. And of course basic month to month mileage.
I as thinking maybe two list boxes and they can simple select the months
they want. Am I any where close?

 

[Duane Hookom]

Could you provide your entire table structure or at least the field(s) that
contain the mileage? I hope you haven't created separate columns for each
month. If so, how do you handle multiple years?

 

[DFCRJ]

Currently the table they are using contains VIN, FLEET ID, COMPANY ID,
EMPLOYEE ID, JAN, FEB, etc. This is the method they have been using, they
export to the table to excel and then cut and paste the columns they need to
print.
There is about 250 vechicles in all, and they have mileage for each. I have
to retain the current data. Not sure how set this up or why, but cant worry
about that now.

 

[Duane Hookom]

I still would not allow user interface dictate my table structure. You can
keep your structure and create a union query that allows querying months
SELECT VIN, [FLEET ID], [COMPANY ID], [EMPLOYEE ID], JAN as Miles, 1 As Mth
FROM tblMileage
UNION ALL
SELECT VIN, [FLEET ID], [COMPANY ID], [EMPLOYEE ID], FEB, 2
FROM tblMileage
UNION ALL
SELECT VIN, [FLEET ID], [COMPANY ID], [EMPLOYEE ID], MAR, 3
FROM tblMileage
UNION ALL
-- etc for all months --

You can then allow your users to select values like 3 and 7 in form controls
and report back the miles between the months.

 

[DFCRJ]

I do suppose your right about letting them dictate the table. what would be
the best approach, create a new table that holds the vechicle info. and then
another that contains the mileages?

I still would not allow user interface dictate my table structure. You can
keep your structure and create a union query that allows querying months
SELECT VIN, [FLEET ID], [COMPANY ID], [EMPLOYEE ID], JAN as Miles, 1 As Mth
FROM tblMileage
UNION ALL
SELECT VIN, [FLEET ID], [COMPANY ID], [EMPLOYEE ID], FEB, 2
FROM tblMileage
UNION ALL
SELECT VIN, [FLEET ID], [COMPANY ID], [EMPLOYEE ID], MAR, 3
FROM tblMileage
UNION ALL
-- etc for all months --

You can then allow your users to select values like 3 and 7 in form controls
and report back the miles between the months.

--
Duane Hookom
MS Access MVP

Currently the table they are using contains VIN, FLEET ID, COMPANY ID,
EMPLOYEE ID, JAN, FEB, etc. This is the method they have been using, they
export to the table to excel and then cut and paste the columns they need
to
print.
There is about 250 vechicles in all, and they have mileage for each. I
have
to retain the current data. Not sure how set this up or why, but cant
worry
about that now.

 

[Duane Hookom]

Two or more tables is a great idea. The mileage table needs a link to the
vehicle table as well as a date and mileage. Again, if you can't do this,
you can use a UNION query to create the "mileage table".

--
Duane Hookom
MS Access MVP

I do suppose your right about letting them dictate the table. what would be
the best approach, create a new table that holds the vechicle info. and
then
another that contains the mileages?

I still would not allow user interface dictate my table structure. You
can
keep your structure and create a union query that allows querying months
SELECT VIN, [FLEET ID], [COMPANY ID], [EMPLOYEE ID], JAN as Miles, 1 As
Mth
FROM tblMileage
UNION ALL
SELECT VIN, [FLEET ID], [COMPANY ID], [EMPLOYEE ID], FEB, 2
FROM tblMileage
UNION ALL
SELECT VIN, [FLEET ID], [COMPANY ID], [EMPLOYEE ID], MAR, 3
FROM tblMileage
UNION ALL
-- etc for all months --

You can then allow your users to select values like 3 and 7 in form
controls
and report back the miles between the months.

--
Duane Hookom
MS Access MVP

Currently the table they are using contains VIN, FLEET ID, COMPANY ID,
EMPLOYEE ID, JAN, FEB, etc. This is the method they have been using,
they
export to the table to excel and then cut and paste the columns they
need
to
print.
There is about 250 vechicles in all, and they have mileage for each. I
have
to retain the current data. Not sure how set this up or why, but cant
worry
about that now.

:

Could you provide your entire table structure or at least the field(s)
that
contain the mileage? I hope you haven't created separate columns for
each
month. If so, how do you handle multiple years?

--
Duane Hookom
MS Access MVP


I'm trying to create a table so multiple users can ask for mileage
reports.
Currently the database contains a "mileage" table that list the VIN,
FLEET
#,
Drive ID, and a column for each months ending odometer reading. what
i
need
to be able to do is give the user to ability to select what time
frame
they
want to compare.
So if they want to see how many miles somone traveled from Jan thru
March
or
from June thru August. And of course basic month to month mileage.
I as thinking maybe two list boxes and they can simple select the
months
they want. Am I any where close?