records beyond some limit

[Mark]

Hi,

I have a situation where each client is allowed so many visits, but will
sometimes exceed this maximum number of visits. Each visit can have multiple
services. I would like to display in a report the days and services for each
client that exceed a specified limit. I already have a saved Totals query
("SavedQry") which groups on Client_ID and DateOfService.

There's a one-to-many relationship between ClientTbl and ServicesProvidedTbl

ClientTbl
- Client_ID as primary key (autonumber)

ServicesProvidedTbl
- Srv_ID as primary key (autonumber
- Client_ID (long integer
- DateOfService (date)
- TypeOfService (text)

For each client, I want to start with the first date of service and after
reaching the 26th day of service, I want to begin creating a record set (or
whatever) to be printed on a report of the 27th day and each of its
services, the 28th day and each of its services, etc., etc. I just need to
capture the days/services *beyond* the 26th day for each client.

How do I code for something like this? Temp tables would be fine with me.

Thank you,
Mark

 

[Allen Browne]

Create a query that returns the TOP 26 dates per client. Save. Then use the
Unmatched Query Wizard to create a query that gives you the other records.

A subquery might help for the first part. See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

You might need a temporary table (for acceptable performance) if you have
hundreds of thousands of records, but that may not be needed.

 

[Mark]

Thank you, Allen

Create a query that returns the TOP 26 dates per client. Save. Then use the
Unmatched Query Wizard to create a query that gives you the other records.

A subquery might help for the first part. See:
Subquery basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

You might need a temporary table (for acceptable performance) if you have
hundreds of thousands of records, but that may not be needed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

me.