Results 1 to 2 of 2
  1. #1
    gflowers is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2017
    Posts
    2

    Calculating Month to Date numbers using Inception to Date data in a calculated field

    Hello,



    I was wondering if something like this would be possible... I have data that includes only inception to date numbers, but I need a calculated field in my query that will solve for Month to date. For instance, (September 2017 ITD) - (August 2017 ITD) = (September 2017 MTD).

    This is how my data looks:

    Company Date (yyyymm) Gross Profit ITD
    Apple Co. 201706 200,000.00
    Apple Co. 201707 220,000.00
    Apple Co. 201708 240,000.00
    Apple Co. 201709 260,000.00
    Grape Co. 201706 100,000.00
    Grape Co. 201707 105,000.00
    Grape Co. 201708 107,000.00
    Grape Co. 201709 115,000.00

    In this example, we can tell that the month to date numbers would be 20,000 each month for Apple Co.

    The new table would look like this:

    Company Date (yyyymm) Gross Profit MTD Gross Profit ITD
    Apple Co. 201706 200,000.00
    Apple Co. 201707 20,000.00 220,000.00
    Apple Co. 201708 20,000.00 240,000.00
    Apple Co. 201709 20,000.00 260,000.00
    Grape Co. 201706 100,000.00
    Grape Co. 201707 5,000.00 105,000.00
    Grape Co. 201708 2,000.00 107,000.00
    Grape Co. 201709 8,000.00 115,000.00

    I'm thinking that a dlookup function could help here, but I'm not sure. It's difficult because there are several companies that could be scattered in the data, and it might incorrectly take the current ITD value and subtract a previous ITD value from a different company. Any thoughts on how to tackle this? Thanks!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This is my table (tblTest)

    Data_ID Company_ID ActivityDate GrossProfit
    1 1 201706 $200,000.00
    2 1 201707 $220,000.00
    3 1 201708 $240,000.00
    4 1 201709 $260,000.00
    5 2 201706 $100,000.00
    6 2 201707 $105,000.00
    7 2 201708 $107,000.00
    8 2 201709 $115,000.00


    Make this query:

    SELECT tblTest.Data_ID, tblTest.Company_ID, tblTest.ActivityDate, tblTest.GrossProfit, Format(DateAdd("m",-1,DateSerial(Left([activitydate],4),Right([activitydate],2),1)),"yyyymm") AS PrevMonth
    FROM tblTest;


    Save it as qryRunningSumPre


    Make this query:

    SELECT qryRunningSumPre.Data_ID, qryRunningSumPre.Company_ID, qryRunningSumPre.ActivityDate, qryRunningSumPre.GrossProfit, qryRunningSumPre.PrevMonth, tblTest.GrossProfit AS PrevProfit
    FROM qryRunningSumPre LEFT JOIN tblTest ON (qryRunningSumPre.PrevMonth = tblTest.ActivityDate) AND (qryRunningSumPre.Company_ID = tblTest.Company_ID);

    From here it's pretty easy to do the calculation you want in another column.

    NOTE: this will only work if each company has consecutive months. If it does not you'd have to do more wrangling with the data but could still use the same type of method.

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

Similar Threads

  1. Replies: 6
    Last Post: 05-26-2017, 04:10 PM
  2. Replies: 1
    Last Post: 04-29-2016, 04:03 AM
  3. Calculating Year to Date data in Form
    By barnes434 in forum Forms
    Replies: 3
    Last Post: 05-16-2012, 02:56 PM
  4. Converting date field in numbers
    By mercapto in forum Queries
    Replies: 5
    Last Post: 04-19-2012, 11:35 AM
  5. Replies: 3
    Last Post: 04-01-2012, 01:40 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