Access Help

[Guest]

I have a large amount of data in an access data base and I was wondering if
there was a way to create a query to sort out second occurances from the
database?

 

[Guest]

What do you mean by "second occurances"? Are they exact duplicates in the
same table; very similar records in the same table; or possibly records in
different tables?

Post some sample data using the real table and field names. Possibly not all
the fields; however, those that matter such a primary keys and date fields.
Then show what you want this data to look like.

 

[Guest]

In access I have:

Client id's Date of service
12345 1/25/02
12345 2/25/02
12345 3/25/02
98765 1/1/02
98765 1/3/02

is there a way to sort out the second occurance for all client's. In this
case for Client id 12345 it would be 2/25/02 and client id 98765 it would be
1/3/02?

I am correlating between 1st 2nd and 3rd occurances. The first occurance I
queried out using the "min" function.

thanks for the help!

 

[Guest]

What do you mean by "sort out"? Remove it from the returned records or only
display the second record based on the date?

 

[Guest]

I only want the second occurance to display in the table based on the date.

 

[Guest]

Something like this may get the results with a little work and modifications:

SELECT T1.[Client id], T1.[Date of service]
FROM YourTable AS T1
WHERE T1.[Date of service] In
(SELECT TOP 1 T2.[Date of service]
FROM YourTable AS T2
WHERE T2.[Client id]=T1.[Client id]
AND T1.[Date of service] In
(SELECT TOP 1 T3.[Date of service]
FROM YourTable AS T3
WHERE T3.[Client id]=T2.[Client id]
ORDER BY T3.[Date of service] DESC)
ORDER BY T2.[Date of service] DESC)
ORDER BY T1.[Client id], T1.[Date of service];