Multiple records with same name BUT..

[initiator]

Hi all,



I have a fairly large access database of bank customers. The database is badly designed. It assigns different P. key's to the same customer. Say 'Customer ID' is the primary key and then the customer name. Bcz it's corporate customers so customer names are also unique. Here's the design

ID CustomerName Amount Outstanding
1001 XYZ Corp $10,000
1002 XYZ Corp $20,000
1003 XYZ Corp $15,000

........................

I want to assign the same P. Key to all instances of XYZ Corp. How can it be done?

thanks

[TheShabz]

should there be multiple entries for each customer? If so, you cannot do that as it defeats the purpose of a primary key. If you want just one entry with largest outstanding balance, run a query for SELECT * and GROUP BY max of Amount Outstanding. also, if you have access to the table design, keep field names without spaces "Amount Outstanding" should be "AmountOutstanding"

[Datagopherdan]

Hi all,

I have a fairly large access database of bank customers. The database is badly designed. It assigns different P. key's to the same customer. Say 'Customer ID' is the primary key and then the customer name. Bcz it's corporate customers so customer names are also unique. Here's the design

ID CustomerName Amount Outstanding
1001 XYZ Corp $10,000
1002 XYZ Corp $20,000
1003 XYZ Corp $15,000

........................

I want to assign the same P. Key to all instances of XYZ Corp. How can it be done?

thanks

Badly designed database? I've had my fair share of those. I can tell you from experience that the majority of the time you are better off creating a brand new database that is setup correctly and normalized then trying to band-aid something together that is wrong. Applying temporary fixes to a badly designed database is an ongoing process because theres always something that needs to be corrected to function right whereas if you take the time to plan it out and determine how everything should be setup, it will save you lots of time and headaches in the long run and you can count on your reporting being accurate instead of having to second guess everything.

Dan
Access Development