Indentify Duplicate Numbers and Flag first entry

[GILBERT]

I need to do two things 1st to identify numbers that are duplicated in a
column part of a query. In a separate column same query I would like for it
to indicate which rows are duplicated (E.G. Dup, Not Dup). The rows has a
column with date/time stamp with a format of 08/22/2007 12:30:34 is it
possible to identify the row that was enter 1st
Date/Time Numbers Dup
08/22/2007 12:30:34 123 Dup Enter 1st
08/22/2007 12:35:34 123 Dup
08/22/2007 12:35:34 345 Not Dup
08/22/2007 12:36:36 123 Dup Enter 2nd
08/22/2007 12:38:34 345 Dup
08/22/2007 12:35:34 345 Dup Enter 1st
since we will only report out on the last date/time stamp.

 

[Pieter Wijnen]

According to what you're saying you don't need to!
SELECT A.myField, Max(A.DateField) As DateField FROM MyTable A
GROUP BY A.MyField

Should achieve what you're after

HTH

Pieter

 

[GILBERT]

Hi Peter,
Thanks for your quick response.
I’m a newbie. I tried you script it didn’t pull the data I was looking for
… I need to identify the numbers that are duplicated 1st and then identify
which of the duplicated numbers was enter in the system 1st 2nd 3rd … (there
is a time stamp with the format 07/01/07 12:12:26) Thanks again

 

[Pieter Wijnen]

You're after the last entered record for a givven criteria, right?
Create a new Query (add your table) , switch to SQL view & exchange MyTable
for your table, myField for the "ID" Field & DateField for your timestamp
field (note that I've aliased myTable with the alias "A" in the From
Clause). This should Give you the latest Dateentry for each ID

HTH

Pieter

This should provide you with the last

 

[GILBERT]

Thank you so much it worked....I do have a follow up question is there a way
to Identify data that fall with in a 15min increments E.G.
Date Number Critera
2007-08-11 05:24:29. 12 Yes
2007-08-11 05:24:26. 12 Yes

2007-08-11 20:24:29. 12 No
2007-08-11 05:24:26. 12 No

2007-08-11 14:24:29. 12 No
2007-08-11 15:24:26. 12 No

The time criteria is(4:15, 4:30, 4:45, and 5:00).If the call comes in at
4:13 and again at 4:15 is ok but if it falls between 4:00 and 4:14 then we
can't use
Thanks again I hope this makes sense

 

[Pieter Wijnen]

Use the datediff function
or simple math 15 mins = 1/96 (of a day - Access stores the day & time as a
decimal number with 0 beeing 30.dec.1899 0:00:00)
I suspect that what you're after is checking the two latest entries for a
givven id
the query to accomplish this is to complex to use to much time on in this
forum.
therefore I'll attempt to create a function (on the fly) you can use instead
;-)

Pieter

Public Function NextHighest(ID As long, Maxdt As Date) As Date
Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef
Dim Rs As DAO.Recordset
Dim thQ As String

Set Db = Access.CurrentDB
Set QDef = Db.CreateQueryDef(VBA.vbNullString)
QDef.SQL = "SELECT MAX(DateField) AS Mx FROM TheTable WHERE ID=pID AND
DateField < pDt"
Qdef.Parameters("pID").Value = ID
QDef.Parameters("pDt").Value = MaxDt
Set Rs = Qdef.OpenRecordset(DAO.dbOpenSnapshot)
If Rs.EOF Then
NextHighest =0
Else
NextHighest = Rs.Fields("Mx").Value
End If
Rs.Close : Set Rs = Nothing
Set Qdef = Nothing
Set Db = Nothing
End Function

-- which offcourse could easily have been achieved using a sub-query