Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Table Query

    Can anyone tell me how to auto populate the following, example below:


    Table1

    Example
    --------------- Field 1 ----- Field 2
    Record 1 ----- 100 -------- 150
    Record 2 ----- 150(auto populated from above field 2)


    How do I get field 1 to auto fill with the amount from field 2 from the previous record automatically.
    Going forward so That I only have to ever enter the amount in field 2
    Last edited by Blackhawk; 2015-10-10 at 15:54.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I assume you are doing this during data entry, probably in the Before Insert event of a form. Knowing nothing else about your data, start with this query (I'm assuming the fields are RecordNo, Field1, Field2):

    SELECT Field2 FROM tblData Left Join (SELECT Max(RecordNo) AS MaxOfRecordNo FROM tblData) AS sq ON tblData.RecordNo=sq.MaxOfRecordData

    Let's save this as "qryLastReading", so in the BeforeInsert Event you'd have:
    Field1 = DLookup("Field2","qryLastReading")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Oct 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your help, but it is in the table setup that I was wanting this to be configured if possible.

    Below is the table structure

    example.png

    So if this is not possible how can I get around this?

    The data will be entered using a form called Mileage, which has the table fields in it as above?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    A couple of observations regarding your table design:
    • Database normalization guidelines advise not storing calculated values - do them on the form
    • Ltrs would appear to be Liters in which case I would expect it to be a number, not text
    • Where do you record the mileage for the current fill-up?

    I would approach this by storing the mileage at the current fill-up and having the form open up in data entry mode and having a VBA procedure run to find the mileage at the previous fill-up (using the MAX() function on either the ID number or the mileage number and populate that in a text box that is unbound. The other possibility would be to use a completely unbound form, which would require a good deal more VBA. If you aren't comfortable using VBA, you might consider using a separate data-entry form to get the current fill-up captured, and then open another form that does the calculations. That form could be used by creating a query that does a join on a calculated value that is one less than the current maximum ID number and has two copies of the same table joined so you see the two mileage values. Note however that in real world situations where records are deleted because of an input error, this approach, or any based on using the ID number, tend to be fragile. Hope this is at least somewhat helpful.
    Wendell

  5. #5
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  6. #6
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Quote Originally Posted by WendellB View Post
    A
    • Database normalization guidelines advise not storing calculated values - do them on the form
    In Access 2010 and later the Calculated data type is an expression not a result. The expression is calculated on the fly as needed. The do not violate the rules of data normalization.

    "do them on the form" is not complete. The point is to calculate the value as need. This could be in a query, report and/or form.

    I tend to do my calculations at the query level. This way any form ore report based on the query will have the correct calculation. makes update the calculation a lot easier.

    By moving the calculation to the table level then every query, report, and form that used the field will always have the same calculation. If the calculation does change you only have to change it once at the table level. All forms, reports, and queries will be automatically correct.

    In theory the updated calculated column in 2010 sounds great. Do know there are some limits to the calculated columns.

    I would be very careful using the calculated field only with Access 2010 and later. And then only after doing lots of testing.

    Also see:
    http://allenbrowne.com/casu-14.html
    https://scottgem.wordpress.com/2011/...ted-data-type/
    Last edited by HiTechCoach; 2015-10-14 at 15:25.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •