More than one claim

[dbl]

This must be easy but I am getting no where.

Ok I want to pull out driver's under policy A123456 who have had more that
one claim in a give period of time.

I have built a query with all the required data and have sorted by the
policy number (the policy number could have a 1000 different vehicles on it)
and the date range that I require.

How do I now get it to give me a list of all the drivers with multiple
claims only? I do not want to see driver's with single claims.

This would have to be done by Reg No, Surname and First Name
so that I get an exact match.

But the driver can have more that 1 vehicle i.e they changed cars during
the policy. So a driver could have a claim on vehicle A and vehicle B

And surnames not being unique i.e. John Wood and Dave Wood

So to sum up I would need to see all the claims for John Wood in vehicle A
and B but only if he has more than 1 claim. And the same for Dave Wood and
all the other drivers with more than one claim.

Any help would be very much appreciated.

Bob

 

[Jeff Boyce]

It all starts with the data!

"How" to do a query depends on how the data is structured.

More specific information may lead to more specific suggestions.

 

[Jerry Whittle]

Is there a primary key field that uniquely identifies the policy holder? If
not, you have a big problem. You need a way to positively identify a person.

How about if a woman gets married and changes her name?

What if you have two John Wood's such as Sr. and Jr.

 

[Bob]

Ok each driver does have a unique ID and each record has a unique ID,
DriverID and CustomerID each policy has a unique ID. So if I sort by
DriverID it gives me them all, the DriverID is a text field i.e. MY7382R
which comes from a external source. So where do I go from here please?

Thanks Bob

 

[Jerry Whittle]

In that case, you are in luck!

Create a query that selects the DriverID and the Policy ID twice. Yes bring
down the Policy ID field two times.

Run it to make sure that it runs right.

If so go up to View and select Totals while in Design view. (Access 2003 or
older)

Change the Total: row under the second Policy ID field from Group By to Count.

In the Criteria for this same field, put > 1

That should give you the drivers who had more than one claim under a policy.

If that works, you'll need to add a Date/Time field to limit the dates.
Change its Totals: to Where and put something like a between statement for
the dates in the criteria.

Sort as you see fit.

 

[dbl]

Jerry it works fine.

Thanks very much for your help.

Regards Bob