Results 1 to 8 of 8
  1. #1
    jfca283 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2014
    Posts
    9

    Create field with Quarterly growth?

    Hi
    I need to creat a field named Q.
    Q is the quarterly growth from the YYZ field.
    I have another field, Date, which is obviously the date of the record YYZ.
    There is one record per date.


    So, how do i compute
    Q=Y(t+3)-Y(t)
    ?
    I'm very lost doing this operation.
    Thanks for your comments and time.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Are all the values on the same record, or are you trying to compare the current record to a previous record?

    If Y and T are value son the current record your formula would be

    Q: ([Y]*(T+3)) - ([Y]*[T])

    but that may be just the same as

    Q: [Y]*3

  3. #3
    jfca283 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2014
    Posts
    9
    I'm trying to do something like this
    Area yyz date Q
    1 122 01-01-2010
    2 108 01-01-2010
    1 118 01-04-2010 -4
    2 105 01-04-2010 -3
    1 117 01-07-2010 -1
    2 122 01-07-2010 17
    1 126 01-10-2010 9
    2 126 01-10-2010 4

    By every record on Area, i need to compute the quarterly growth using YYZ.
    That's why on the third record in Q i have -4(118-122) for the quarterly growth for Area=1 on 01-04-2010.
    I hope i made myself clear.
    Thanks for your time.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    is this data summarized or is this a view of your actual data? If it's your actual data (it might work if this is summarized too, but I'm not sure).

    Let's assume this is your full dataset and it's on a table named 'TABLE1'

    This query would give you the most recent quarter prior to each of your records:

    Code:
    SELECT Table1.Area, Table1.YYZ, Table1.Quarter_Date AS ThisQuarter, Max(Table1_1.Quarter_Date) AS LastQuarter
    FROM Table1 LEFT JOIN Table1 AS Table1_1 ON Table1.Area = Table1_1.Area
    WHERE (((Table1_1.Quarter_Date)<[table1]![Quarter_Date]))
    GROUP BY Table1.Area, Table1.YYZ, Table1.Quarter_Date;
    Save this query as QUERY1

    then create this query

    Code:
    SELECT Query1.Area, Query1.YYZ, Query1.ThisQuarter, Table1.YYZ AS ThisQ, Table1_1.YYZ AS LastQ, [table1]![yyz]-[table1_1]![yyz] AS NetChange
    FROM (Query1 LEFT JOIN Table1 ON (Query1.ThisQuarter = Table1.Quarter_Date) AND (Query1.Area = Table1.Area)) LEFT JOIN Table1 AS Table1_1 ON (Query1.LastQuarter = Table1_1.Quarter_Date) AND (Query1.Area = Table1_1.Area)
    ORDER BY Query1.Area, Query1.ThisQuarter;
    This will link the quarter dates back to the original table, pull the correct yyz values and give you the net change

  5. #5
    jfca283 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2014
    Posts
    9
    Thanks for your reply.
    Unfortunately, i'm not so advanced using access.
    I'm sorry.

  6. #6
    jfca283 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2014
    Posts
    9
    I tried doing the growth like Q: yyz-DLookUp("yyz","[Table1]"," "[Area]=[Area]" AND "[Date]=DateAdd("m", "-1", "[Date]")) But it failed. Can you tell me why?

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Don't use domain functions in queries. The queries I gave you will work as stated in your original post.

  8. #8
    jfca283 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2014
    Posts
    9
    I prefer the function way.
    Why? Because i need to perform this task for a lot of tables.
    Imagine doing that.
    Besides, i learn to use the DLookUp function, which is so powerful.

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

Similar Threads

  1. Growth Calculation?
    By jininho in forum Queries
    Replies: 2
    Last Post: 10-06-2013, 05:40 PM
  2. Query to calculate quarterly growth rate.
    By gromit1 in forum Queries
    Replies: 15
    Last Post: 03-07-2013, 04:00 PM
  3. quarterly calculation
    By jamo in forum Queries
    Replies: 7
    Last Post: 11-20-2012, 06:59 PM
  4. YTD and MTD Sales growth by activity
    By dudumomo in forum Reports
    Replies: 3
    Last Post: 03-13-2012, 01:42 AM
  5. Quarterly Amounts
    By Brian62 in forum Queries
    Replies: 9
    Last Post: 10-16-2009, 02:18 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