Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

Form / Table Design

Discussion in 'Information Technology' started by Jason, Jul 28, 2006.

Tags:
  1. Jason

    Jason
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Jason wrote:
    > 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


    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
     
  4. Jason

    Jason
    Expand Collapse
    Guest

    "Joseph Meehan" wrote:

    > Jason wrote:
    > > 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

    >
    > 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.
     
  5. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    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

    "Jason" wrote:

    > 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
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page