Calculating incremental differences in a given col

2020-04-20 14:43发布

问题:

i was searching web and stackoverflow but didn,t find an answer. :( So please help me i am still learning and reading, but i am not yet thinking correctly, there are no IF and FOR LOOPs to do stuff. :)

I have table1:

  id|  date    |state_on_date|year_quantity
   1|30.12.2013|23           |100
   1|31.12.2013|25           |100 
   1|1.1.2014  |35           |150 
   1|2.1.2014  |12           |150 
   2|30.12.2013|34           |200 
   2|31.12.2013|65           |200 
   2|1.1.2014  |43           |300 

I am trying to get:

table2:

id|  date    |state_on_date|year_quantity|state_on_date_compare
 1|30.12.2013|    23       |100          |23
 1|31.12.2013|    25       |100          |-2 
 1|1.1.2014  |    35       |150          |-10 
 1|2.1.2014  |    12       |150          |23 
 2|30.12.2013|    34       |200          |34
 2|31.12.2013|    65       |200          |-31 
 2|1.1.2014  |    43       |300          |22 

Rules to get numbers:

id|date  |state_on_date|year_quantity|state_on_date_compare
 1|30.12.2013|   23    |100| 23 (lowest state_on_date for id 1)
 1|31.12.2013|   25    |100| -2 (23-25)
 1|  1.1.2014|   35    |150|-10 (25-35)
 1|  2.1.2014|   12    |150| 23 (35-12)
 2|30.12.2013|   34    |200| 34 (lowest state_on_date for id 2)
 2|31.12.2013|   65    |200|-31 (34-65)
 2|  1.1.2014|   43    |300| 22 (65-43)

Thanks in advace for every suggestion or solution you will make.

回答1:

You have to understand that SQL is misleading because of presentation issues. Like in The Matrix ("there is no spoon"), in a query there is no previous record.

SQL is based on set theory, for which there IS NO ORDER of records. All records are just set members. The theory behind SQL is that anything you do normally should be considered as though you are doing it to ALL RECORDS AT THE SAME TIME! The fact that a datasheet view of a SELECT query shows record A before record B is an artifact of presentation - not of actual record order.

In fact, the records returned by a query are in the same order as they appear in a table UNLESS you have included a GROUP BY or ORDER BY clause. And the order of record appearance in a table is usually the order in which they were created UNLESS there is a functional primary key on that table.

However, both of these statements leave you with the same problem. There is no SYNTAX for the concepts of NEXT and PREVIOUS because it is the CONCEPT of order that doesn't exist in SQL.

VBA recordsets, though based on SQL as recordsources, create an extra context that encapsulates the SQL context. That is why VBA can do what you want and SQL itself cannot. It is the "extra" context in which VBA can define variables holding what you wanted to remember until another record comes along.

Having now rained on your parade, here are some thoughts that MIGHT help.

  1. When you want to see "previous record" data, there MUST be a way for Access to find what you consider to be the "previous record." Therefore, if you have not allowed for this situation, it is a design flaw. (Based on you not realizing the implications of SET theory, which is eminently forgivable for new Access users, so don't take it too hard.) This is based on the "Old Programmer's Rule" that says "Access can't tell you anything you didn't tell it first." Which means - in practical terms - that if order means something to you, you must give Access the data required to remember and later impose that order. If you have no variable to identify proper order with respect to your data set, you cannot impose the desired order later. In this case, it looks like a combination of id and date together will give you an ordering variable.

  2. You can SOMETIMES do something like a DLookup in a query where you look for the record that would precede the current one based on some order identifier.

e.g. if you were ordering by date/time fields and meant "previous" to imply the record with the next earlier time than the record in focus, you would choose the record with the maximum date less than the date in focus. Look at the DMax function. Also notice I said "record in focus" not "current record." This is a fine point, but "Current" also implies ordering by connotation. ("Previous" and "Next" imply order by denotation, a stronger definition.)

Anyway, contemplate this little jewel:

DLookup( "[myvalue]", "mytable", "[mytable]![mydate] = #" & CStr( DMax( "[mydate]", "mytable", "[mytable]![mydate] < #" & CStr( [mydate] ) & "# )" ) & "#" )

I don't guarantee that the parentheses are balanced for the functions and I don't guarantee that the syntax is exactly right. Use Access Help on DLookup, DMax, Cstr, and on strings (in functions) in order to get the exact syntax. The idea is to use a query (implied by DMax) to find the largest date less than the date in focus in order to feed a query (implied by DLookup) to find the value for the record having that date. And the CStr converts the date/time variable to a string so you can use the "#" signs as date-string brackets.

IF you are dealing with different dates for records with different qualifiers, you will also have to include the rest of the qualifies in BOTH the DMax and DLookup functions. That syntax gets awfully nasty awfully fast. Which is why folks take up VBA in the first place.



回答2:

Johnny Bones makes some good points in his answer, but in fact there is a way to have Access SQL perform the required calculations in this case. Our sample data is in a table named [table1]:

id  date        state_on_date  year_quantity
--  ----------  -------------  -------------
 1  2013-12-20             23            100
 1  2013-12-31             25            100
 1  2014-01-01             25            150
 1  2014-01-02             12            150
 2  2013-12-30             34            200
 2  2013-12-31             65            200
 2  2014-01-01             43            300

Step 1: Determining the initial rows for each [id]

We start by creating a saved query in Access named [StartDatesById] to give us the earliest date for each [id]

SELECT id, MIN([date]) AS MinOfDate
FROM table1
GROUP BY id

That gives us

id  MinOfDate 
--  ----------
 1  2013-12-30
 2  2013-12-30

Now we can use that in another query to give us the initial rows for each [id]

SELECT 
    table1.id, 
    table1.date, 
    table1.state_on_date, 
    table1.year_quantity, 
    table1.state_on_date AS state_on_date_compare
FROM
    table1
    INNER JOIN
    StartDatesById
        ON table1.id = StartDatesById.id
            AND table1.date = StartDatesById.MinOfDate

which gives us

id  date        state_on_date  year_quantity  state_on_date_compare
--  ----------  -------------  -------------  ---------------------
 1  2013-12-30             23            100                     23
 2  2013-12-30             34            200                     34

Step 2: Calculating the subsequent rows

This step begins with creating a saved query named [PreviousDates] that uses a self-join on [table1] to give us the previous dates for each row in [table1] that is not the first row for that [id]

SELECT
    t1a.id,
    t1a.date,
    MAX(t1b.date) AS previous_date
FROM
    table1 AS t1a
    INNER JOIN
    table1 AS t1b
        ON t1a.id = t1b.id
            AND t1a.date > t1b.date
GROUP BY
    t1a.id,
    t1a.date

That query gives us

id  date        previous_date
--  ----------  -------------
 1  2013-12-31  2013-12-30   
 1  2014-01-01  2013-12-31   
 1  2014-01-02  2014-01-01   
 2  2013-12-31  2013-12-30   
 2  2014-01-01  2013-12-31   

Once again, we can use that query in another query to derive the subsequent records for each [id]

SELECT
    curr.id,
    curr.date,
    curr.state_on_date,
    curr.year_quantity,
    prev.state_on_date - curr.state_on_date AS state_on_date_compare
FROM
    (
        table1 AS curr
        INNER JOIN
        PreviousDates
            ON curr.id = PreviousDates.id
                AND curr.date = PreviousDates.date
    )
    INNER JOIN
    table1 AS prev
        ON prev.id = PreviousDates.id
            AND prev.date = PreviousDates.previous_date

which returns

id  date        state_on_date  year_quantity  state_on_date_compare
--  ----------  -------------  -------------  ---------------------
 1  2013-12-31             25            100                     -2
 1  2014-01-01             35            150                    -10
 1  2014-01-02             12            150                     23
 2  2013-12-31             65            200                    -31
 2  2014-01-01             43            300                     22

Step 3: Combining the results of steps 1 and 2

To combine the results from the previous two steps we just include them both in a UNION query and sort by the first two columns

    SELECT 
        table1.id, 
        table1.date, 
        table1.state_on_date, 
        table1.year_quantity, 
        table1.state_on_date AS state_on_date_compare
    FROM
        table1
        INNER JOIN
        StartDatesById
            ON table1.id = StartDatesById.id
                AND table1.date = StartDatesById.MinOfDate
UNION ALL
    SELECT
        curr.id,
        curr.date,
        curr.state_on_date,
        curr.year_quantity,
        prev.state_on_date - curr.state_on_date AS state_on_date_compare
    FROM
        (
            table1 AS curr
            INNER JOIN
            PreviousDates
                ON curr.id = PreviousDates.id
                    AND curr.date = PreviousDates.date
        )
        INNER JOIN
        table1 AS prev
            ON prev.id = PreviousDates.id
                AND prev.date = PreviousDates.previous_date
ORDER BY 1, 2

returning

id  date        state_on_date  year_quantity  state_on_date_compare
--  ----------  -------------  -------------  ---------------------
 1  2013-12-30             23            100                     23
 1  2013-12-31             25            100                     -2
 1  2014-01-01             35            150                    -10
 1  2014-01-02             12            150                     23
 2  2013-12-30             34            200                     34
 2  2013-12-31             65            200                    -31
 2  2014-01-01             43            300                     22


回答3:

I hope this would be helpful http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/calculating-mean-median-and-mode-with-sq

you can use select * from table1 into table2 where specify your conditions,I am not sure whetehr this would work