Results 1 to 8 of 8
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Post Calculation challenge

    I posted similar, but maybe made it harder by writing too much. Ill try it this way.

    If I have a table and query with 4 fields. ID, Date, Some Name, Amount.
    Every record is on the first day of a month and every next record is in the following month. Is there a calculation for the 5th field to return the previous months amount.

    Example
    Field 1 2 3 4 5


    ID12 01apr2016 Any 110
    ID13 01May2016 Some 235 110
    ID14 01Jun2016 Blabla 385 235
    ID15 01Jul2016 More 474 385

    In row with ID13 is there an expression or way to return the amount 110 of the previous row, and every record thereafter?
    Click image for larger version. 

Name:	Roberta Vinci.png 
Views:	42 
Size:	28.7 KB 
ID:	36792

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Years ago, I created a mileage dB to keep track of my vehicle mileages/cost/mpg. Allen Browne's example creates a non-updateable query, but I wanted to be able to make corrections in the form, so I wrote a UDF.


    As an alternative, here is the UDF example:
    Attached Files Attached Files

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thanks Steve, brilliant. I'm working at it, will let you know.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Steve's solution won't work if you have a non-US date format.
    Formatting the date as mm/dd/yyyy will fix that:
    Code:
    SELECT Table1.id, Table1.TheDate, Table1.SomeName, Table1.Amount, GetPreviousAmount(Format([TheDate],"mm/dd/yyyy")) AS PreviousMonthAmount 
    FROM Table1
    ORDER BY Table1.TheDate;

    This can also be done using a query with 2 copies of the table. No function needed

    At its simplest, this is something like
    Code:
    SELECT Table1.id, Table1.TheDate, Table1.SomeName, Table1.Amount, Table1_1.Amount AS PrevAmount
    FROM Table1, Table1 AS Table1_1
    WHERE (((Table1.id)=[Table1_1].[ID]+1));
    However that will 'lose' the first record and will only work where ID values are consecutive

    To regain the first record, save the above as e.g Query2 then create a new query with that and the original table using an outer join
    Code:
    SELECT Table1.id, Table1.TheDate, Table1.SomeName, Table1.Amount, Table1_1.Amount AS PrevAmount
    FROM Table1, Table1 AS Table1_1
    WHERE (((Table1.id)=[table1_1].[ID]+1));

    For more advanced solutions including handling non consecutive ID values, see this article on my website
    http://www.mendipdatasystems.co.uk/g...lue/4594484854
    Last edited by isladogs; 01-05-2019 at 06:22 AM. Reason: Added code
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Remember that DateSerial(Year([01apr2016]);Month([01apr2016]);1) equals DateSerial(Year([01May2016]);Month([01May2016])-1;1)

    so, for Field5, you can tell:
    Code:
    DLookUp("[Field4]";"ThisQuery";"DateSerial(Year([Field2]);Month([Field2]);1)=#" & DateSerial(Year([Field2]);Month([Field2])-1;1) & "#")
    or the shorter:
    Code:
    DLookUp("[Field4]";"ThisQuery";"[Field2]=#" & DateSerial(Year([Field2]);Month([Field2])-1;1) & "#")

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by ridders52 View Post
    Steve's solution won't work if you have a non-US date format.
    Formatting the date as mm/dd/yyyy will fix that:
    Code:
    SELECT Table1.id, Table1.TheDate, Table1.SomeName,  Table1.Amount, GetPreviousAmount(Format([TheDate],"mm/dd/yyyy")) AS  PreviousMonthAmount 
    FROM Table1
    ORDER BY Table1.TheDate;
    @Colin, Thanks for pointing that out... and showing the fix. Apologies, I guess I forget to take off my tunnel vision glasses......

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No problem. As i have to deal with it every day, date formatting is one of the first things I look at when something doesn't work for me
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Calculation challenge
    By Perfac in forum Programming
    Replies: 6
    Last Post: 01-03-2019, 10:52 AM
  2. A Security Challenge
    By isladogs in forum Sample Databases
    Replies: 4
    Last Post: 07-16-2018, 12:49 AM
  3. joining different reports challenge!
    By agosfernandes in forum Reports
    Replies: 4
    Last Post: 01-25-2017, 08:53 AM
  4. Printer Form Challenge
    By RA99 in forum Reports
    Replies: 10
    Last Post: 07-11-2014, 02:35 PM
  5. Anyone fancy a challenge????!!!!!!
    By gregh in forum Database Design
    Replies: 1
    Last Post: 03-14-2011, 05:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums