numbering?

[ginger]

hello

i have a table called other_services. it consists of clients and the dates of services after a certain date. client A may have 2 records, client B may have 12.

my goal is to pull into a report all the clients and the first 5 or less services.

thanks for the help

 

[plog]

Can you post some sample data as well as what the result should be based on that sample data? Be sure to include your table and field names. Use this format:

TableNameHere
Field1NameHere, Field2NameHere, Field3NameHere...
18, Sally, 2/13/2012
77, Dave, 5/14/2008
101, Nathan, 12/11/2012

 

[ginger]

other_services
ClientKey,PN_ID,ServiceDate,StartTime
231355,910948,8/26/2013,10:15:00 AM
231355,904073,8/21/2013,1:30:00 AM
231355,910962,8/28/2013,11:00:00 AM
231355,909763,8/23/2013,4:10:00 PM
231355,904741,8/21/2013,9:50:00 AM
212074,910916,8/28/2013,11:45:00 AM
230354,899798,8/15/2013,11:00:00 AM
230354,905658,8/22/2013,7:00:00 AM
230354,900600,8/16/2013,10:15:00 AM
230354,901491,8/17/2013,9:00:00 AM
230354,897568,8/9/2013,10:40:00 AM
230354,892226,8/6/2013,8:00:00 AM
230354,906657,8/23/2013,10:40:00 AM
230354,906671,8/23/2013,8:00:00 AM


so on the report i would like to have the clientid in a header and up to the first 5 service dates/times in the detail. PN_ID is the unique key for each service.

thanks

 

[plog]

First, you will need to merge your ServiceDate and StartTime fields---its called a Date/Time field for a reason--it can and should hold both. For your data, I did this in a field called [ServiceTime] (i.e. for the first record of your sample data [ServiceTime] was "8/26/2013 10:15:00 AM").

Once that's done, create a sub-query using this SQL:

SELECT other_services.ClientKey, Max(other_services.ServiceTime) AS FifthServiceTime
FROM other_services
WHERE (((DCount("[ClientKey]","other_services","[ClientKey]=" & [ClientKey] & " AND [ServiceTime]<=#" & [ServiceTime] & "#"))<=5))
GROUP BY other_services.ClientKey
ORDER BY other_services.ClientKey, Max(other_services.ServiceTime);

Save that with the name 'sub_FiveServices'. It determines the [ServiceTime] of the 5th service; if there isn't 5 its the [ServiceTime] of the last one.

Then to get the data you want, you use that query against other_services to find all the records with a [ServiceTime] less than or equal to the 5th [ServiceTime] that was determined in 'sub_FiveServices'. This is that SQL:

SELECT other_services.* FROM other_services INNER JOIN sub_FiveServices ON other_services.ClientKey=sub_FiveServices.ClientKey
WHERE (((IIf([ServiceTime]<=[FifthServiceTime],1,0))=1));