Using MS Access/how to select records where a field have a certain number of duplicate records
I have an access database that houses info about pawn tickets and the individuals pawning the property. I created a field that concatenates the pawner's first and last name and DOB to serve as a unique identifer. What I am trying to do is write a query that selects those individuals who have previously pawned over a certain number (such as 4 or 5) of items.
First, I do not recommend using such a key. In fact it violates normalization rules.
Access includes an Autonumber datatype which is perfect for a unique identifier and performs better than a text field.
Also you should have at least 2 tables here. One if a clients table. This should list demographic information about each client. Names, DOB, Address, Phone, etc. Then you need a second table to record all pawn transactions. This would have its own Autonumber Primary key and a foreign key to link to the autonumber ClientID. In addition, it would have fields for the date, item description, amount tendered, due date, etc.
Once that is set up properly, its easy to create a Group By query that groups by ClientID, and counts the number of pawn transactions.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA