Substract previous record from current with a condition

Question

0.00/5 (No votes)

See more:

Hello experts,
I need your expert guidance to complete this MS-Access query.
i have a table named 'FO' which have 3 columns ID, Reading, Date_time

I don't know from where data gets inserted, but it is vulnerable :(
there are many duplicate data with different ID(may be treat as primary column).

My task is to first eliminate duplicate rows(with same Date_time & Reading but with different ID) then subtract 'Reading' from previous record, if previous 'Reading' have smaller or equal to current. If current 'Reading' is smaller than previous then current will be treat as result with in a given date range.
sample data:

ID	Reading	Date_Time
7	1465564	09-04-2018 03:31:52
8	1465564	09-04-2018 03:31:52
13	1465564	09-04-2018 10:53:09
14	1465564	09-04-2018 10:53:09
15	1465568	09-04-2018 13:35:11
16	1465568	09-04-2018 13:35:11
20	1465568	09-04-2018 18:37:01
21	1465595	09-04-2018 19:37:15
22	1465601	09-04-2018 19:37:38
23	1465601	09-04-2018 19:37:38
24	1465608	09-04-2018 22:24:11
30	1465608	09-04-2018 22:24:11
31	1465809	10-04-2018 05:32:25
35	2832	10-04-2018 09:07:15
43	1465833	10-04-2018 09:42:53
44	1466228	10-04-2018 18:37:15
45	1466301	10-04-2018 19:22:47
46	6322	10-04-2018 20:36:32
47	1466331	10-04-2018 21:22:51
48	1466361	10-04-2018 22:36:15
49	1466376	10-04-2018 23:37:13
53	1469714	11-04-2018 03:37:15
54	9365	11-04-2018 04:37:15
55	1471514	11-04-2018 05:37:15
56	1472185	11-04-2018 06:21:59
61	1472192	11-04-2018 11:19:07
62	1472192	11-04-2018 11:19:07
63	2192	11-04-2018 13:04:17
68	1472208	11-04-2018 18:23:41
69	1472212	11-04-2018 19:20:27
70	1472212	11-04-2018 19:20:27
71	1472212	11-04-2018 19:20:27
72	1472212	11-04-2018 19:20:27
73	1472212	11-04-2018 19:20:27
74	1472443	12-04-2018 00:37:15
75	1473078	12-04-2018 01:37:15
76	1473978	12-04-2018 02:37:15
77	1474299	12-04-2018 03:37:13
78	1474301	12-04-2018 03:42:45
79	5182	12-04-2018 05:37:15
80	1476082	12-04-2018 06:37:15
81	1476242	12-04-2018 06:47:55
96	1476242	12-04-2018 06:47:55
97	1476752	12-04-2018 23:37:15
98	1477370	13-04-2018 00:18:30
101	1477370	13-04-2018 00:18:30
102	1477370	13-04-2018 00:18:30
103	1477371	13-04-2018 05:16:03
104	1477554	13-04-2018 06:37:15
105	1478454	13-04-2018 07:37:15
106	9354	13-04-2018 08:37:15
107	1480254	13-04-2018 09:37:15
108	1481154	13-04-2018 10:37:15
109	1482053	13-04-2018 11:37:15
110	1482953	13-04-2018 12:37:15
111	1483853	13-04-2018 13:37:15
112	1484753	13-04-2018 14:37:15
113	1485614	13-04-2018 15:37:15
114	1486094	13-04-2018 16:37:15
115	1486933	13-04-2018 17:37:15
116	1487833	13-04-2018 18:37:15
117	1488733	13-04-2018 19:37:15
118	1489633	13-04-2018 20:37:15
119	1490533	13-04-2018 21:37:15
120	1491403	13-04-2018 22:37:15
121	1492292	13-04-2018 23:37:15
122	1493186	14-04-2018 00:37:15
1035	1728982	21-05-2018 05:21:43
1036	1728982	21-05-2018 05:21:43
1333	1736122	01-06-2018 23:56:45

What I have tried:

i tried to first filter the records with following:

SELECT T1.* FROM (SELECT DISTINCT(DATE_TIME) AS D1, READING AS R1 FROM FO ORDER BY DATE_TIME DESC) AS T1

i don't know how to proceed with this :'(

Posted 2-Jun-18 6:22am

KManishS

Updated 4-Jun-18 3:18am

v3

Add a Solution

Comments

Richard MacCutchan 3-Jun-18 4:41am

You probably need to select each record in turn, and delete any with a different id but the same reading and date time. Depending on how many records in the database this may require some extra coding to build the list of duplicates in memory.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-06-04T03:19:00

If you would like to get unique rows (eliminate duplicates on Reading and Date_Time columns), you have to use one of aggregate functions. In this case, i'd suggest to use MAX()[^]

SQL

SELECT MAX(ID) AS ID, Reading, Date_Time
FROM YourTable
GROUP BY Reading, Date_Time;

A result:

ID	Reading	Date_Time
7	1465564	09-04-2018 03:31:52
8	1465564	09-04-2018 03:31:52
13	1465564	09-04-2018 10:53:09
14	1465564	09-04-2018 10:53:09
...