CODEWITHSUNDEEP
sqlms-access
Emman D.
Emman D.

Reputation: 117

MS Access SQL to get the average of two records

Currently, I am using MS Excel to do this but I would like to know if it is possible in MS Access.

I would like to get the average of a value from the previous hour and the current hour, and put that average value in the current hour record. One restriction is not to query the first record since it has no previous hour.

How should go about implementing the pseudo-sql code below?

SELECT Date
      ,Hour
      ,Node
      ,Average (Value,"Value from previous hour, e.g (Hour-1) with the same date and node")
FROM tblInput
WHERE Hour = 2,3,4

tblInput:

+----------+------+------+------+
|   Date   | Hour | Node |Value |
+----------+------+------+------+
|   ...    | ...  | ...  | ...  |
| 1/1/2-18 |   1  | AAA  | 5    |
| 1/1/2-18 |   2  | AAA  | 10   |
| 1/1/2-18 |   3  | AAA  | 15   |
| 1/1/2-18 |   4  | AAA  | 20   |
| 1/1/2-18 |   1  | BBB  | 4    |
| 1/1/2-18 |   2  | BBB  | 8    |
| 1/1/2-18 |   3  | BBB  | 12   |
| 1/1/2-18 |   4  | BBB  | 16   |
|   ...    | ...  | ...  | ...  |
+----------+------+------+------+

Output:

+----------+------+------+------+
|   Date   | Hour | Node | Ave  |
+----------+------+------+------+
| 1/1/2-18 |   2  | AAA  | 7.5  |
| 1/1/2-18 |   3  | AAA  | 12.5 |
| 1/1/2-18 |   4  | AAA  | 17.5 |
| 1/1/2-18 |   2  | BBB  | 6    |
| 1/1/2-18 |   3  | BBB  | 10   |
| 1/1/2-18 |   4  | BBB  | 14   |
+----------+------+------+------+

Upvotes: 0

Views: 56

Answers (1)

Gustav
Gustav

Reputation: 55831

It is:

SELECT 
    [Date],
    Hour,
    Node,
    (Value + 
        (Select Value
        From tblInput As T
        Where 
            T.Node = tblInput.Node And
            T.Date = tblInput.Date And 
            T.Hour = tblInput.Hour - 1)) / 2
FROM 
    tblInput
WHERE 
    Hour In (2,3,4)

Upvotes: 1

Related Questions