Select records by comparing time stamps in 1 field (1 Viewer)

pizzaballs

New member
Local time
Today, 20:23
Joined
Jun 26, 2014
Messages
5
I can not solve this problem, my Access knoledge is mediocre. :confused:

I have the following table from imported data:

RecNr IPaddres DateandTime
1 178.00.000.01 20-11-2013 21:47:21
2 178.00.000.01 20-11-2013 21:47:59
3 178.00.000.01 20-11-2013 21:48:35
4 178.00.000.01 22-11-2013 20:44:59
5 178.00.000.02 22-12-2013 19:47:59
6 178.00.000.02 22-12-2013 20:47:59
7 178.00.000.03 01-02-2014 12:47:59
8 178.00.000.03 01-02-2014 12:48:30
9 178.00.000.03 01-03-2014 11:47:59
etc

I want to make a query that results in displaying records that have the same IP-address where the difference in the date/time stamp is within 1 minute. When the date/time stamp is > 1 minute the record can be deleted from the table.

Hope someone can help me with this.
 
Last edited:

pizzaballs

New member
Local time
Today, 20:23
Joined
Jun 26, 2014
Messages
5
Is this such a brain cracker or am I not clear with my question?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:23
Joined
Feb 19, 2013
Messages
16,553
I don't think you are being clear but I'll give it a go

I want to make a query that results in displaying records that have the same IP-address where the difference in the date/time stamp is within 1 minute.

Code:
SELECT *
FROM myTable INNER JOIN myTable AS myTable_1 ON myTable.IPaddress = myTable_1.IPaddress
WHERE DateDiff("s",myTable_1.DateAndTime,[myTable].DateAndTime)<=60

When the date/time stamp is > 1 minute the record can be deleted from the table.
You'll need to be clearer what you mean - provide an example - the issue is, (using your sample data) how you define this - for example record 3 is over a minute from record 1 but less than a minute from record 2
 
Last edited:

pizzaballs

New member
Local time
Today, 20:23
Joined
Jun 26, 2014
Messages
5
Thank you!
Your result:

myTable.RecNr myTable.IPaddress myTable.DateandTime myTable_1.RecNr myTable_1.IPaddress myTable_1.DateandTime 3 178.00.000.01 20-11-2013 21:47:59 2 178.00.000.01 20-11-2013 21:47:21 2 178.00.000.01 20-11-2013 21:47:21 2 178.00.000.01 20-11-2013 21:47:21 4 178.00.000.01 20-11-2013 21:48:35 3 178.00.000.01 20-11-2013 21:47:59 3 178.00.000.01 20-11-2013 21:47:59 3 178.00.000.01 20-11-2013 21:47:59 2 178.00.000.01 20-11-2013 21:47:21 3 178.00.000.01 20-11-2013 21:47:59 4 178.00.000.01 20-11-2013 21:48:35 4 178.00.000.01 20-11-2013 21:48:35 3 178.00.000.01 20-11-2013 21:47:59 4 178.00.000.01 20-11-2013 21:48:35 2 178.00.000.01 20-11-2013 21:47:21 4 178.00.000.01 20-11-2013 21:48:35 5 178.00.000.01 22-11-2013 20:44:59 5 178.00.000.01 22-11-2013 20:44:59 4 178.00.000.01 20-11-2013 21:48:35 5 178.00.000.01 22-11-2013 20:44:59 3 178.00.000.01 20-11-2013 21:47:59 5 178.00.000.01 22-11-2013 20:44:59 2 178.00.000.01 20-11-2013 21:47:21 5 178.00.000.01 22-11-2013 20:44:59 6 178.00.000.02 22-12-2013 19:47:59 6 178.00.000.02 22-12-2013 19:47:59 7 178.00.000.02 22-12-2013 20:47:59 7 178.00.000.02 22-12-2013 20:47:59 6 178.00.000.02 22-12-2013 19:47:59 7 178.00.000.02 22-12-2013 20:47:59 9 178.00.000.03 1-2-2014 12:48:30 8 178.00.000.03 1-2-2014 12:47:59 8 178.00.000.03 1-2-2014 12:47:59 8 178.00.000.03 1-2-2014 12:47:59 9 178.00.000.03 1-2-2014 12:48:30 9 178.00.000.03 1-2-2014 12:48:30 8 178.00.000.03 1-2-2014 12:47:59 9 178.00.000.03 1-2-2014 12:48:30 10 178.00.000.03 1-3-2014 11:47:59 10 178.00.000.03 1-3-2014 11:47:59 9 178.00.000.03 1-2-2014 12:48:30 10 178.00.000.03 1-3-2014 11:47:59 8 178.00.000.03 1-2-2014 12:47:59 10 178.00.000.03 1-3-2014 11:47:59
Wanted result; only the records that have the same IP address en are within a minute from each other. The third record within a minute from the second record.

Wanted result with this table:

Wanted result

RecNr
IPaddress DateandTime 2 178.00.000.01 20-11-2013 21:47:21 3 178.00.000.01 20-11-2013 21:47:59 4 178.00.000.01 20-11-2013 21:48:35 8 178.00.000.03 1-2-2014 12:47:59 9 178.00.000.03 1-2-2014 12:48:30
Hope this will clear my question.

I see the text in the editor window is different from the display text in the forum.
I will include two screenprints.
 

Attachments

  • wanted.jpg
    wanted.jpg
    29.5 KB · Views: 142
  • yourresult.jpg
    yourresult.jpg
    81.5 KB · Views: 143
Last edited:

pizzaballs

New member
Local time
Today, 20:23
Joined
Jun 26, 2014
Messages
5
Anyone who can crack this brain cracker? When you want more info: shoot, I will give you all the info you need!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:23
Joined
Feb 19, 2013
Messages
16,553
Look at the code and think about it - it isn't difficult

To eliminate same record matches and to include for earlier times modify the criteria to

Code:
WHERE DateDiff("s",myTable_1.DateAndTime,myTable.DateAndTime)BETWEEN -60 AND 60 AND myTable_1.RecNr<>myTable.RecNr

To only show one record rather than the one it is matched to modify the select to

SELECT myTable.*


If it is not in the right order, I'm sure you can work out how to sort it.
 

Users who are viewing this thread

Top Bottom