Help with a Query

[TomD]

Consider the following query column set up......

Ref No/Surname/Initial/Policy No/On Risk Date

With no criteria set, the resulting query report shows the
Ref No is not unique i.e it may occur more than once.
When it does, it details is the same surname/initial but
different policy number.

What is required with the query is that the Ref No is
unique i.e.occurs only once; multiple occurances are to be
ignored.

As well as the above, the On Risk Rate is to be
<=01/01/1999

I can do query for On Risk date (produces 566 records but
with a number of Ref No repeated)

Can anyone help to do what I want ?????

 

[Gerald Stanley]

Try something along the lines of
SELECT refNo, surname, initial, policyNo, onRiskDate
FROM YourTable AS T1
WHERE policyNo IN (SELECT Max(policyNo) FROM YourTable
WHERE onRiskDate <= #01/01/1999# AND refNo = T1.refNo)

Hope This Helps
Gerald Stanley MCSD