Looping through numbers to add a value to a field

[smellyalater]

Hi guys

I was advised to come to this part of the forum as |I would love to do this through VBA:

These are my before and after code is ran table:
ID, mobile, datetime, unique
1, 07712312312, 14/04/2016 13:02:34, blank
2, 07712312312, 14/04/2016 13:21:13, blank
3, 07712312312, 14/04/2016 13:23:56, blank
4, 07712312312, 14/04/2016 13:02:34, blank

I want to get this:
ID, mobile, datetime, unique
1, 07712312312, 14/04/2016 13:02:34, blank
2, 07712312312, 14/04/2016 13:21:13, 1
3, 07712312312, 14/04/2016 13:23:56, 0
4, 07712312312, 14/04/2016 13:52:34, 1

I want to use vba to do a datediff to make sure there is 15 minutes between each number coming in, so that it is unique again and gets a 1. If it is less than 15 minutes then it is not classed as unique therefore gets a 0, then move the the next number do the checks and put the values in.

Hope this makes sense any help or good examples to follow would be great

Thanks!

 

[jdraw]

Unique in database has a specific meaning.
In plain English what exactly is your description of the field named "unique".

 

[smellyalater]

Unique in database has a specific meaning.
In plain English what exactly is your description of the field named "unique".

Hi

If a 1 is in the unique field the number is unique, if it is 0 then it is not.

Thank you

 

[Grumm]

I like to name field like that in a way it make sense.
In your case i would have picked Is_Unique so i know that it is a yes/no field

 

[arnelgp]

you can use a query:

SELECT T1.ID, T1.mobile, T1.datetime, (SELECT TOP 1 DATEDIFF("n",[datetime],T1.[datetime]) FROM (SELECT [ID],[MOBILE],[DATETIME] FROM table1 ORDER BY [MOBILE],[DATETIME] DESC) WHERE ID<T1.ID AND [mobile]=T1.mobile ) AS MinuteDiff, Switch([MinuteDiff]>=15,1,[MinuteDiff]<15,0) AS [Unique]
FROM table1 AS T1;

 

[Galaxiom]

Your question should be about how best to achieve the result. Choosing which tool to use is not the place to start. Queries are often more efficient.

Storing Unique in the table breaches normalization since it is derived from the data itself. It should be calculated in a query as required.

The following "aircode' (untested). It is probably not quite right but hopefully it will give you some idea. It assumes the ID are in dtatime order. If not then you will have to use the datetime field in the Max and it sources.

 SELECT ID, IIF(PrecedeID Is Null,0,1) AS UniqCall
 FROM
      (
       SELECT tX.ID, Max(qY.EarlierID) AS PrecedeID
       FROM table AS tX
        LEFT JOIN
           (
            SELECT tA.ID AS CallID, tB.ID AS EarlierID
            FROM table AS tA
            LEFT JOIN table as tB
            ON  tA.mobile = tB.mobile
              AND DateDiff("n", tB.datetime, tA.datetime) Between 0 And 14
               AND tA.ID <> tB.ID
            ) AS qY
       ON tX.ID = qY.CallID
       GROUP BY tX.ID
       ) AS qW
 ORDER BY mobile, ID

If you want the other information about the calls then join this query back to the original table.

 

[smellyalater]

Thank you for the replies guys, I will give it a shot today once I get home!

Again many thanks!