Solved Update Vehicle Table from Form field...

[Garren.Shannon]

Hey all,

Again, relearning Access after many years away. I am building a ridership database. I have the following sort of working. Each record is associated to a specific vehicle so that's a combo box. Selecting the bus ID pulls the current_OD from the table and puts it into the "Odometer at Depart". The driver enters the "Odometer at Return", fills out the rest of the report and saves the record in the Trip table. When he/she hits save record, I want the Return Odometer data for that trip to replace/update the current_OD in the vehicles database for the currently selected bus.

I think this is done via an Update Query but what I have crafted so far is not getting the job done.

 

[theDBguy]

Can you show us the UPDATE query you created?

 

[Garren.Shannon]

UPDATE Vehicles INNER JOIN Trip ON Vehicles.VehicleID = Trip.VehicleName SET Vehicles.CurrentOD = [OD_Return];

 

[Garren.Shannon]

I set this to run when the Save Button is clicked.

 

[Garren.Shannon]

I think I get what you mean. I had not thought about record entry out of order. That would be a problem.

So instead of writing out the return OD... I should calculate the difference between the start and return OD and increment the bus current OD by that amount. That way, it would be irrelevant what the enter order was. Would that be done with an update query?

If so how?

 

[CarlettoFed]

If the relationship between Trip and Vehicles is correct the query is correct, perhaps if you attach the file it is easier to show you how you should change for what Pat Hartman rightly said.

 

[Garren.Shannon]

Here it is... it has some sample data.

 

[Garren.Shannon]

90% of the trips are short, 2 or 3, 5 to 20 mile trips per bus. On occasion, our "Day Tripper" buses will take kids on a long excursion and sometimes over night. Count is the head count per student. Code is the type of trip - To/From, Field, Activities and Misc (maintenance runs). I still need to put in some cross checking like if the code is M, the counts can be null but if the code is TF, F or A, Base and Max must be > 0.

Good point on Nulls... yes... I will make that happen.

I should be able to get my head around calculating total mileage rather than storing a continuous update... but it seem like its just out of reach. I mean, the bus has it simple. It's just an incrementor ticking away. But collecting and keeping that information updated so we can report to the state keeps baffling me. My kneejerk reaction is to constantly update a digital odometer per bus.. just like the physical ones do.

I swear, I was not this thick 20 years ago.

 

[CarlettoFed]

In the attached file you can see an example.

 

[Garren.Shannon]

Thanks CarlettoFed...