Results 1 to 11 of 11
  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: Inter–rows relationship in a query

    A query/table shows our monthly financial status. There are expense and incomes fields. I want to make a new field named:”sumup”. I want this field to be the result of current month incomes minus expenses plus “sumup” from the previous month record.
    For example on June we had 3000 income and 2000 expenses. We had 3500 left from May (shown on “sumup” field in May record) . So the “sumup” record for June will be 4500.
    In other words, I want to know how can we make a query to use data from previous records on the same table/query.

  2. #2
    Join Date
    May 2012
    Posts
    132
    It seems my question was either unclear or so hard.
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by ariansman View Post
    In other words, I want to know how can we make a query to use data from previous records on the same table/query.
    Here's a simple example.

    1. Table1 (ID: Autonumber, ValueDate: Date/Time, Amount: Currency):
    Code:
    ID	ValueDate	Amount
    1	10/01/2012	150,00 
    2	17/01/2012	400,00 
    3	05/02/2012	 80,00 
    4	18/02/2012	320,00 
    5	19/03/2012	 95,00 
    6	30/03/2012	160,00 
    7	15/04/2012	210,00 
    8	21/04/2012	320,00 
    9	25/04/2012	 85,00 
    10	02/05/2012	185,00
    2. Query1 (notice that as an aggregate function [here: SUM()] is used, every other member of the SELECT query must by submitted to an aggregation [here: GROUP BY]):
    Code:
    SELECT   a.ID, 
             a.ValueDate, 
             a.Amount, 
             Sum(b.Amount) AS PreviousAmount
    FROM     Table1 as a, Table1 as b
    WHERE    b.ValueDate <= a.ValueDate
    GROUP BY a.ID, 
             a.ValueDate, 
             a.Amount
    ORDER BY a.ValueDate;
    Yields:
    Code:
    ID	ValueDate	Amount	PreviousAmount
    1	10/01/2012	150,00	  150,00 
    2	17/01/2012	400,00	  550,00 
    3	05/02/2012	 80,00	  630,00 
    4	18/02/2012	320,00	  950,00 
    5	19/03/2012	 95,00	1.045,00 
    6	30/03/2012	160,00	1.205,00 
    7	15/04/2012	210,00	1.415,00 
    8	21/04/2012	320,00	1.735,00 
    9	25/04/2012	 85,00	1.820,00 
    10	02/05/2012	185,00	2.005,00
    Depending on the complexity of your requirements, you'll probably need to combine several subqueries to obtain the desired result.
    Have a nice day!

  4. #4
    Join Date
    May 2012
    Posts
    132
    thank you very much,
    you guide was very constructive and in fact you made my day,

    I also have another question, unrelated to this topic though,
    Do you approve using lookup in related tables? For example in an order_table, is it better to use a an employe_ID or its name?
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Concerning your other question:

    1. When you have a question that's unrelated to a previous one, please open a new thread. People who saw the first question won't necessary reopen the thread later on and you (and other users) could lose valuable advices about the second question because of that.

    2. If you need to acquire a single value from a table or a query, DLookUp is OK.

    3. If you need to acquire several values in the same row (i.e. several fields or columns) from a table or query, it's more efficient to open a Recordset on the table (or the Query). As a shortcut, you can use:
    Code:
    Dim var as Variant
    Var = CurrentDb.OpenRecordset("Select * FROM <SomeTable> WHERE <SomeColumn> = <SomeValue>;", dbOpenSnapShot).GetRows
    The values can be retrieved in var(0, 0), var(1, 0), var(2,0)... etc.

    4. In a VBA loop, open a Recordset outside the loop whenever it's possible. Otherwise refer to 2. & 3. here above.

    5. Never ever use a domain function in a query. This is very inefficient and can yield unexpected (as in "unwanted") results. Depending on the situation, use a subquery or add a supplemental JOIN to the existing query.

    6. These are general guidelines and specific situations may require specific solutions. In case of doubt, test several solutions in a loop and select the most efficient, or convenient, for a given situation.
    Performing such a test in a loop is necessary because the time for executing the operation once is too short to carry any meaning. Such loops can be easily written:
    Code:
    Public Declare Function GetTickCount Lib "Kernel32" () As Long
    
    Sub TestLoop()
    
        Dim lngStart As Long
        Dim i As Long
        Dim lngParentID As Long
        
        lngStart = GetTickCount
        For i = 1 To 1000
            lngParentID = DLookup("Parent_ID", "Tbl_MenuItems", "Object_ID = 41712062")
        Next i
        Debug.Print "Elapsed time in milliseconds: " & GetTickCount - lngStart
        
    End Sub
    Have a nice day!

  6. #6
    Join Date
    May 2012
    Posts
    132
    thank you for ur time and attention,
    please let get back to the main topic.
    The code is working greatly. But i could not manage to use it in an extended field.
    Table1: (ID date income expense product-cat-ID)
    Code:
    :
    ID   date   income   expense    product-cat-ID 
    1   1/1/12  1000         250              2
    2   1/10/12  1200        350              1
    3   1/17/12  1300        200              1
    4   1/21/12   900        150               3
    :
    product-cat-ID is a foreign key
    Table2: ( Product-cat-ID category_name coefficient)

    Code:
    :
    Product-cat-ID  category_name      coefficient
    1                           syringe                  2
    2                           ampule                  3
    3                              tablet                  1
    :


    What is desired:
    Query1:
    Code:
    :
    ID   date   ext1: income*coefficient     ext2: expense*coefficient                ext3:ext1 - ext2       ext4: total
    1   1/1/12          3000                                           750                                  1250                   1250  
    2   1/10/12        2400                                           700                                   1700                    2950  
    3   1/10/12        1600                                           400                                   1200                    4150
    3   1/10/12         900                                             150                                  750                    4900
    :


    Do you think if all can be done in one single query or we need to make another query first and then run those codes ?
    Last edited by ariansman; 05-23-12 at 07:37.
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A simple INNER JOIN query will provide the fastest solution:
    Code:
    SELECT Table1.ID, 
           Table1.date, 
           Table1.income * Table2.coefficient AS ext1, 
           Table1.expense * Table2.coefficient AS ext2
    FROM Table1 INNER JOIN Table2 
                ON Table1.[product-cat-ID]=Table2.[Product-cat-ID];
    Have a nice day!

  8. #8
    Join Date
    May 2012
    Posts
    132
    can you also instruct me on ext3 and ext4?
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    ext3 can be computed using:
    Code:
    (Table1.income*Table2.coefficient) - (Table1.expense*Table2.coefficient) AS ext3
    I'm not sure of which formula you want to use for computing ext4.
    Have a nice day!

  10. #10
    Join Date
    May 2012
    Posts
    132
    this is also main question:
    Ext4 is like “amount” field that we talked about early on this topic. It adds ext3 on each row with ext4 on the previous row.
    to resemble what you taught there, I added another table1, named “table1_1” and wrote this:
    “sum (Table1_1.income*Table2.coefficient) - (Table1_1.expense*Table2.coefficient)) as ext4” . But it did not worked.
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  11. #11
    Join Date
    May 2012
    Posts
    132
    no answer yet
    was my question ambiguous?
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

Posting Permissions

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