INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Update table with previous record value?

Update table with previous record value?

(OP)
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: New form record set control to value of previous record other control

 

misscrf

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

RE: Update table with previous record value?

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

RE: Update table with previous record value?

(OP)
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

RE: Update table with previous record value?

See if this looks ok:

CODE

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:

CODE

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

Duane
Hook'D on Access
MS Access MVP

RE: Update table with previous record value?

(OP)
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

RE: Update table with previous record value?

(OP)
Thanks, it worked!

misscrf

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

RE: Update table with previous record value?

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

RE: Update table with previous record value?

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close