Report First Instance of a record

[rccline]

Can you report on the first instance of a record?

I have a query that returns multiple fields.

I want to show only the first instance of selected fields following an Id. For example, I am interested in reporting LeaseID and Field1. The data in Field2 varies, so I can't use a summary query.

LeaseID Field1 Field2
1 x y
1 x x
1 x v
2 x y
3 x z
3 x x


I then I want to summarize the data in Field2 in a subreport based on LeaseID.

This would be simplified if the database was normalized, but this seems to present several difficult problems.

Any suggestions?

Thanks.

Robert

 

[mrsbean]

You could try something like this:

SELECT LEASEID, FIELD1, Min(FIELD2) AS MinField2 FROM yourTable
GROUP BY LEASEID, FIELD1

MrsBean

 

[rccline]

Thank you mrsbean. I'm not out of the woods on solving my problem yet, but I sure like that trick.

Robert