Limiting records in a subform

[karatelung]

I guess the best way to explain this is to give an example, so:

The main form and subform both have a field called [HomeId] which is the master and child link. The other important fields in the subform are [ClientID] and [Date].

Now, of course, the subform shows only the records where [HomeId] is equal to the [HomeID] in the main form. Here's my problem.

Say a [HomeID] is "1" and three records in the subform are showing with [HomeID] = "1", [ClientID] = "A" and there are three different values for [Date]. Also, two more records are showing with [HomeID] = "1", [ClientID] = "B", and two different values for [Date]. Five records total in the subform.

What I want is for there to be only one record showing per [ClientID] with each record having the most recent [Date]. So, one [ClientID] = "A" record with the most recent [Date] of the three and one [ClientID] = "B" record with the most recent [Date] of the two.

Can someone please, please help. I would be so grateful.

TIA,

Richie

 

[Alexandre]

This can be handled from the report, using the group level immediatly above the details section, or you could pre-select the Max date from the query underlying your report (using an embedded query). Choice will essentially depend on whether your are interested in details related to dates others than the maxDate for ClientX. If not, you would use embedded queries.

Alex
I ll try to put up a simple demo for you.

[This message has been edited by Alexandre (edited 02-05-2002).]

 

[karatelung]

Thanks, Alex, a simple example would be great if you can spare the time. In the meantime I'll play around with maxDate and the report to see if I can get what I need.

Richie

 

[karatelung]

Okay, I got the subform to work and show as many details as I need. I used the Max() aggregate function on the date like you suggested and I grouped by ClientID.

Since Access queries won't let you show other details unless they are part of an aggregate function, I just used the Last() function for any other details. It was already showing only one record per ClientID, so Last() doesn't really have any effect except for allowing me to view other details - a workaround.

I was able to do a report using a query based on the above one. I used the HomeID as the first level of grouping, and ClientID as the second level group. This is kind of sloppy, but it works. You can't concatenate fields in an aggregate function.

Is there another way to do this type of report without using the aforementioned type of query?

Thanks again,

Richie

 

[Alexandre]

Karatelung,

Preferably than to set up a "theorical" example, if your DB is not too big, zip it and send it to my email.

Alex