Finding Duplicates with many fields
in [General]
|
Prev: Update of Existing Table Numeric Value Using DCount from Anoth
Next: Where to add a Toolbar to start it up with Access, not the databas
From: LG on 12 Apr 2010 17:15 I have a table that consists of many Clients with many plans (fields) that may be the same and maybe different. There are 3791 clients and I was going to try to do a duplicate query but, it only allows to look up to 10 fields. I need find all the clients that have the same exact plans for 20+ fields any suggestions? Thank you for all your assistance
From: John W. Vinson on 12 Apr 2010 18:27
On Mon, 12 Apr 2010 14:15:01 -0700, LG <LG(a)discussions.microsoft.com> wrote: >I have a table that consists of many Clients with many plans (fields) that >may be the same and maybe different. There are 3791 clients and I was going >to try to do a duplicate query but, it only allows to look up to 10 fields. >I need find all the clients that have the same exact plans for 20+ fields any >suggestions? >Thank you for all your assistance STOP!!!! You're "committing spreadsheet". Having one field per plan is *incorrect table design*. If each Client can have zero, one, or many Plans, and each Plan can be chosen by zero, one or many Clients, you have a perfectly classic many to many relationship, which should include three tables: Clients ClientID <primary key> <information about the client as an individual, nothing to do with plans> Plans PlanID <primary key> PlanTitle <other information about the plan as a thing in itself> ClientPlans ClientID <link to Clients> PlanID <link to Plans> <any information about this plan as it pertains to this client> With your design, you face not only the difficulty of this query (trivial with a simple join in the normalized design), but what will you do when a new plan is added? Redesign your table, redesign all your queries, rebuild all your forms, restructure all your reports? OUCH! If you do decide to properly normalize your tables as I'm suggesting, you can migrate the existing data into the new tables with a "Normalizing Union Query" - post back if you need help. -- John W. Vinson [MVP] |