How do I..?

[lcline]

Thanks in advance for any help rendered!
I think that this example will be easier to follow than the actual DB.

I have two tables. One contains car id, date, driver, and maintenance activities (over 100 different activities fall under this column including oil changes, engine overhaul). The second table has date, car id, driver and mileage. Each time an activity occurs, a single record is written to the mileage table and any number of records is written to the activities table depending on what tasks were performed (oil change, brake job, wiper blades changed would equal 3 records). The odometer is reset at each activity even if it is for wiper blades alone (so there is no running total, just in between every maintenance event).

My question is: how do/can I querry the two tables to get back all tasks performed between overhauls and the total mileage between overhauls.

And yes as you have guessed, I am new at this!
Thanks,
Lee

 

[David R]

Speaking from much painful experience (thanks Pat!), I'd say your database layout needs to be looked at before it's going to be very useful to you, Icline.

You're storing mileage, date, and driver in two (minimum) places, which is probably not necessary. Try to lay it out on paper or in the Relationships window to see what you really need on your main table and what goes on the activities table.

When I tried to recreate your information, I used three tables to avoid duplication:
tblCars: CarID [Prim. key, Text], ActiveStatus [Yes/No checkbox].
tblMileage: MileageID [Prim. key, Autonumber], CarID [Text], Mileage [Number], DriverName [Text, possibly from a Combo box], MaintDate [Date/Time].
tblActivities: ActivityID [Prim. key, Autonumber], MileageID [Number], MaintType [Text, presumably from a Combo box].

Link CarID->CarID one-to-many, and MileageID->MileageID one-to-many in the Relationships window.

If you then make a query from all three tables, you should be able to specify start and end dates and get the mileage and activities in question.
The way it's set up now, I can't see how to avoid getting duplicated mileage from each activity in a maintenance incident. Someone else might be able to, but you're going to find your data a LOT more workable in the long run if you go through the steps now of figuring out what needs to be where.

If you've already got data in your tables, you might look at the action queries to make your new tables (append, delete, update).

Hope that helps,
David R

 

[The_Doc_Man]

Another question:

Is "overhaul" an entry in your maintenance table or is it a generic name for any action taken at all, including wiper blade replacement?

A big part of database design involves defining terms before you have to make choices based on those terms.

 

[Pat Hartman]

Let me offer a slightly different structure. This structure is hierarchical and translates to 1-to-many-to-many.

tblCars
CarId (primary key)
etc.

tblMaintenance
MaintId (primary key)
CarId (foreign key to tblCars)
MaintDate
Mileage
etc.

tblActivities
ActId (primary key)
MaintId (foreign key to tblMaintenance)
ActivityId (foreign key to tblActivityType)

With this structure, the mileage and maintenance date are only stored once for a maintenance event.

See the following link for techniques on using values from previous rows of a query.
http://support.microsoft.com/default.aspx?scid=kb;EN-AU;q101081

The article includes a link to the query samples database available in the download center. It will be well worth your while to download the sample db and the others available in the download center also.

 

[lcline]

Sorry for the delay in replying! Needed to help my daughter with school project.
Yes as it is obvious, I am new at this. I will go back and modify my DB for the three tables. To answer Doc's question, overhaul is a specific task. In fact, through the use of check boxes, there are a possibility of over 100 different tasks that may be performed during a maintenace cycle. I am now putting all the tasks into 1 column via the name of the check box. The entry form allows users to enter all tasks done during that maintenace cycle by checking however many checkboxes neccessary then clicking submit.

As stated I will go back and add the third table and make changes to the entry form. Please don't give up on me as I have 3 projects going on at the same time and it may be tomorrw before I can make those changes.

Again Thanks,
Lee