LOGIC/HOW TO PROCESS A SPECIFIC RECORD IN A TABLE THAT HAS DUPLICATES.

[Brianus]

Table format:
Empnum |perendate |date/timestamp
-------|----------|----------------
0500 | 10/2/06 |10/22/06 13:25:52
0500 | 10/2/06 |10/22/06 13:20:50
0200 | 10/7/06 |10/22/06 13:25:42

As you can see from the above table, two of the records are identical - obviousy a duplication. The only difference is the timestamp.
I want to be able to read/process the record with the most recent timestamp. which is "0500 10/2/06 10/22/06 13:25:52 and ignore its duplicate(the next record). Also I want to process the other record with different empnum. Please I'd appreciate if someone can show me how to accomplish this objective.
Thank you all.
Brianus.

 

[kjv1611]

One thought/possibility would be to do this:

Once you find the duplicate value, create a SQL statement for the criteria that will pull both records. Then, use the DMAX function to select the one more/most recent.

 

[PWise]

This query will return the last time stamp for each empnum and perendate

Select * from tablename 
inner join[
Select Empnum ,perendate , max(timeStamp) as maxtimeStamp 
from tablename
group by Empnum ,perendate ]. as mts 
on mts.Empnum = tablename.Empnum  
and mts.perendate=tablename.perendate 
and  mts.maxtimeStamp = tablename.timeStamp

 

[Remou]

Here are some sketches.

The lowest timestamp where there are more than one in the group:
[tt]SELECT tblLog.Empnum, tblLog.perendate, tblLog.[date/timestamp]
FROM tblLog
WHERE (((tblLog.[date/timestamp]) In (Select Min([date/timestamp]) From tblLog A Where A.EmpNum=tblLog.EmpNum Having Count(*)>1)))
ORDER BY tblLog.Empnum;[/tt]

One record from each group:
[tt]SELECT tblLog.Empnum, tblLog.perendate, tblLog.[date/timestamp]
FROM tblLog
WHERE (((tblLog.[date/timestamp]) In (Select Max([date/timestamp]) From tblLog A Where A.EmpNum=tblLog.empNum)));[/tt]

However, you are likely to get better answers in the Microsoft: Access Queries and JET SQL Forum
(forum701)

 

[Brianus]

Re: Response from Remou.

Thanks for your valuable input. However my question is: where is "tblog A" coming from? Is that a separate table that I am creating?

My confusion:
WHERE (((tblLog.[date/timestamp]) In (Select Max([date/timestamp]) From tblLog A Where A.EmpNum=tblLog.empNum)));
Thanks.
Brianus

 

[PHV]

You have to understand what is an alias in an SQL instruction.

 

[Brianus]

If I understand it, I wouldn't be asking. Could you please elaborate a little?
Thanks.

 

[PHV]

In fact I gave you keywords for search ...

 

[kjv1611]

Brianus,

What PHV is saying is you can simply search for Alias in the SQL Help file. There will be lots of information there, more than you'll need, probably.