Form / Table Design

[Jason]

I am trying to set up a table and form for the purposes of tracking vehicle
mileage. I already have a QRY that will supply the vehicle number. I would
like to end up with a form that allows the user to enter the date in one
place and then in a sub form pick the vehicle and add the mileage or better
yet have the form list all the vehicles.. The user will be adding mileages
for 15-30 vehicles depending on the day of the week. It is my goal to have
the table end up with a record that contains the date vehicle and mileage. I
can’t seem to link the date in my form. I am thinking that I am way off on
the whole design. Any ideas? Thank you

 

[Jason]

Let's start with the data you will be collecting.

You have vehicles, users dates and mileage. Is that correct?

Users use vehicles on specific dates for various amounts of miles.

Can we say that a user may use one or more vehicles in one day and maybe
more than one user use the same vehicle in the same day.

You likely will want the following tables

User
Name
Department
etc.

Vehicle
Description
Date of purchase
etc.

Trips
User
Miles driven
Miles at end of trip (odometer reading)
Date-time
etc.

I suggest that you record the odometer miles at the end of each trip and
the miles drive. At the end of the day after all trips have been recorded,
I would then run a query to assure that the miles driven were consistent
with the number of miles on the odometer for each trip.

After we get all the tableS designed, then let's look at the form(S)

--
Joseph Meehan

Dia duit


This is going to be used by one user. That person will have all the information from each vehicle each morning. Our goal is to use that information to run reports on when maintenaice is due. Based on the miles from the maintenance table.

 

[Ken Sheridan]

Firstly I'd create a Calendar table, which is simply a table of all dates
over a given period. On your (unbound) parent form add a combo or list box
which lists all the dates by means of a RowSource such as:

SELECT calDate
FROM
Calendar
ORDER BY CalDate;

Your table for recording the mileages should include a date field, along
with fields for the vehicle number and mileage. Create a continuous form
based on a sorted query on this table such as:

SELECT *
FROM Mileages
ORDER BY MileageDate, VehicleNumber;

Add this form as a subform to your parent form and set the subform control's
LinkMasterFields property to the name of the combo or list box on the parent
form and the LinkChildFields property to MileageDate (or whatever you call
your date field; but don't call it date, that could cause confusion with the
built in Date function.

Set the opening value of the combo or list box on the parent form to the
current date by putting code along these lines in the parent form's Load even
procedure:

Me.cboDates = Date()

Whenever you select a date from the combo or list box you can enter records
into the subform for as few or as many vehicles as you like. The date will
be automatically entered by virtue of the linking mechanism with the parent
form. If records have already been entered for a given date then these will
show in the subform once you select a date in the parent form.

If there can be only one record for each vehicle for any given date you
should either make the date field and the vehicle number field the composite
primary key of the table, or create a unique index on these two fields in
table design.

Tip: to quickly create a calendar table serially fill down a column in
Excel with sequential dates over your chosen date range and import this into
Access as a table. You can also do it in code within Access, which I can let
you have if you wish.

Ken Sheridan
Stafford, England