You are here:

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

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.