Update table with previous record value? 1

[misscrf]

I know this is not ideal, but it is quick and dirty. I need to run an update query, to update a table column for "Previous Mileage". going forward, I will have this table on the entry form and the user can fill it in.

This is an app for tracking costs for multiple cars, and when they run reports, they can choose any kind of date range. The mileage for that range needs the min and max mileage, but the min needs to actually be the last mileage from before the range.

Mileage is entered as a car cost (subform relationship to each car). It is only entered for gas cost types. For others, it is empty. By adding the field, the user will enter in a previous mileage always, so that we always have that value. I have given up doing this at the report. I just want to back fill the existing records and let the user take care of setting this value, going forward.

This is what the data looks like:

Cost Date - Cost Type - Cost - mileage - previous mileage
1/1/01 - gas - $32.00 - 10,100 - 0
2/15/01 - oil change - $40.00 - (null) - 10,100
2/28/01 - gas - $34.99 - 13,258 - 10,100
3/12/01 - body work - $515.00 - (null) - 13,258

That shows what the previous mileage needs to looks like. I am not sure how to write a query that will update that field.

If anyone could help, I really want to finish this project and get it back to the people who need it, before the holidays.

Thanks!

p.s. I originally tried to ask this as a form update question, but now I just want to update the table once, so that we can enter it manually going forward.

That thread is here:

thread702-1664286



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Aren't you missing a significant field for car? Also, what is the data type of the missing car field? Is Cost Type actuall a text value?

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Sorry. There is FKCar, to look up the car id from tblCar (PKCarID).

Car cost is actually FKCarCost, and is an id field look up to tblCarCostType, which has PKCarCostType and txtCarCostType.


I am attaching a picture of the relationships, in case that helps.

Thanks for taking a look at this, dhookom.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

See if this looks ok:

SELECT PKCarCostID, FKCar, DtCostDate, IntMileagePrev, Dmax("intMileage","tblCarCost","fkCar=" & fkCar & " AND dtCostDate < #" & DtCostDate & "#") As PrevMileage
FROM tblCarCost;

If it looks good, try an update query:

UPDATE tblCarCost, SET intPrevMileage = Dmax("intMileage","tblCarCost","fkCar=" & fkCar & " AND dtCostDate < #" & DtCostDate & "#")
WHERE intPrevMileage is Null;

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks! I'll try it out. I was trying to do that same thing, but had the dmax/dlookup all wrong. lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Thanks, it worked!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

It works but I still think it's a bad idea to store it but apparently you know your app better than I do.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

No it is a horrible idea, but it is too complicated and I am not getting anywhere, trying to get this value on the fly. I just need to finish the project at this point. Not the way I like to do things, but the entire environment is not set up right. This is the least of the problems for where this is going. Better to have a functioning solution at this point.

misscrf

It is never too late to become what you could have been ~ George Eliot