Query the differences between records with same ID

Question

I have a table like this in MS Access 2019:

+-----------+------------+--------+----------+-------+
| BillingID |    Date    | RoomID | Electric | Water |
+-----------+------------+--------+----------+-------+
|         1 | 12/23/2018 |      4 |     1669 |   106 |
|         2 | 12/26/2018 |      1 |     5035 |   289 |
|         3 | 12/27/2018 |      6 |        0 |     0 |
|         4 | 12/31/2018 |      5 |     3158 |   223 |
|         5 | 1/6/2019   |      2 |     3823 |   194 |
|         6 | 1/15/2019  |      3 |     1772 |   125 |
|         7 | 1/23/2019  |      4 |     1796 |   117 |
|         8 | 1/26/2019  |      1 |     5231 |   299 |
|         9 | 1/27/2019  |      6 |        0 |     0 |
|        10 | 1/31/2019  |      5 |     3366 |   242 |
|        11 | 2/14/2019  |      2 |     3975 |   201 |
|        12 | 2/15/2019  |      3 |     1839 |   129 |
+-----------+------------+--------+----------+-------+

I could calculate the electricity and water usage with Index & Match in MS Excel. However, I've had a lot of trouble to achieve this with MS Access. The result I want is as below:

+-----------+------------+--------+----------+---------------+-------+------------+
| BillingID |    Date    | RoomID | Electric | ElectricUsage | Water | WaterUsage |
+-----------+------------+--------+----------+---------------+-------+------------+
|         1 | 12/23/2018 |      4 |     1669 |               |   106 |            |
|         2 | 12/26/2018 |      1 |     5035 |               |   289 |            |
|         3 | 12/27/2018 |      6 |        0 |               |     0 |            |
|         4 | 12/31/2018 |      5 |     3158 |               |   223 |            |
|         5 | 1/6/2019   |      2 |     3823 |               |   194 |            |
|         6 | 1/15/2019  |      3 |     1772 |               |   125 |            |
|         7 | 1/23/2019  |      4 |     1796 |           127 |   117 |         11 |
|         8 | 1/26/2019  |      1 |     5231 |           196 |   299 |         10 |
|         9 | 1/27/2019  |      6 |        0 |               |     0 |            |
|        10 | 1/31/2019  |      5 |     3366 |           208 |   242 |         19 |
|        11 | 2/14/2019  |      2 |     3975 |           152 |   201 |          7 |
|        12 | 2/15/2019  |      3 |     1839 |            67 |   129 |          4 |
+-----------+------------+--------+----------+---------------+-------+------------+

For example, for RoomID = 4, the ElectricUsage is the difference between the Electric in BillingID #7 and BillingID #1 and so on.

I've tried some answer like this or this but Access ran into errors when using those solutions in SQL view (Syntax error in FROM clause).

Thanks.

score 0 · Accepted Answer · answered May 04 '20 at 15:10

You can use a couple of sub-queries to return the Electric/Water for each room on the previous date:

SELECT
B.BillingID, B.BillingDate, B.RoomID, B.Electric,
B.Electric-(SELECT TOP 1 E.Electric FROM tblBilling AS E WHERE B.RoomID=E.RoomID AND E.BillingDate<B.BillingDate ORDER BY E.BillingDate DESC) AS ElectricUsage,
B.Water,
B.Water-(SELECT TOP 1 W.Water FROM tblBilling AS W WHERE B.RoomID=W.RoomID AND W.BillingDate<B.BillingDate ORDER BY W.BillingDate DESC) AS WaterUsage
FROM tblBilling AS B

Note that I've renamed your Date field to be BillingDate, as Date is a reserved word in Access, and will cause you problems in the future.

Regards,

I really don't realize that we can achieve this without doing the LEFT JOIN query, thank you so much — Henry, May 06 '20 at 08:05

Query the differences between records with same ID

1 Answers1