Same Query, new Problem - to two of each person (NOT whole query)

  • Thread starter Thread starter Karissa
  • Start date Start date
K

Karissa

Ok, I have no duplicates, woo-hoo!

Now, I have 556 records for about 30 reps - and I want the most recent two
of each. I know how to sort by rep, ticket date, and ticket time - but, how
do I make it give me just the top two of each rep?
 
you cannot directally do you you need a loop or to use a sub query for
each rep and to get a loop you need to use vba
 
you cannot directally do you you need a loop or to use a sub query for
each rep and to get a loop you need to use vba
Click to expand...

If you want the top two of anything, would not you start by
identifying the top one and add the next to top one.

SELECT A.*
from table1 as A
where [ticket date] = ( select max( [ticket date] )
from table1
where Rep = A.Rep )
union
SELECT A.*
from table1 as A
where [ticket date] = ( select max( [ticket date] )
from table1
where Rep = A.Rep
and [ticket date] < ( SELECT max( [ticket
date] )
FROM table1
where Rep =
A.Rep ) )

I don't know if you can use this logic because of the complications
arising from the split of [ticket date] from [ticket time].
 
You can do this using a coorelated subquery. Life would be simpler if the
date and time were in one field.

SELECT Rep, TicketDate, TicketTime
FROM SomeTable
WHERE TicketDate + TicketTime in
(SELECT TOP 2 TicketDate + TicketTime
FROM SomeTable as tmp
WHERE Tmp.Rep = SomeTable.Rep
And Tmp.TicketDate is Not Null and Tmp.TicketTime is not null
ORDER BY Tmp.TicketDate + TicketTime DESC)

If your table name and field names contain spaces you will need to surround
them with square brackets [].

IF you don't understand how to create a query in the SQL view, you will need
to post back. IF you do so, post the SQL of a working query that returns all
the data (HINT: View: SQL from the menu)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top