 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008More than one claim
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 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.  SignatureRegards Jeff Boyce Microsoft Office/Access MVP > This must be easy but I am getting no where. > [quoted text clipped - 23 lines] > > Bob 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. SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > This must be easy but I am getting no where. > [quoted text clipped - 23 lines] > > 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 > Is there a primary key field that uniquely identifies the policy holder? > If [quoted text clipped - 36 lines] >> >> Bob 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. SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > 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 [quoted text clipped - 43 lines] > >> > >> Bob Jerry it works fine. Thanks very much for your help. Regards Bob > In that case, you are in luck! > [quoted text clipped - 74 lines] >> >> >> >> Bob |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.