All; using Access 2010. I have a database that is working fine. The user who uses it is good about maintaining it far as keeping the data clean. But now he has asked for an enhancement that I can’t figure out how to make happen. My main table has basic info; SSN, Name and Date of birth. SSN is primary key. I have a separate table each for address, phone number, proprietary info and notes. There are updates to the tables each month; i.e. new customers and changes to existing customers. The problem is more often sales try to land new customers who takes their time deciding whether they want to become customers; so we do not have their full information at the time to put into the database. However; management wants them in the database for tracking purposes. How can I allow him to input the customer’s info without a SSN. First of all; it doesn’t save and the record is lost in the table with no way to retrieve it. I need a way to track the name and notes until they become customers or not. I can always use another table but how would I append to the main tables without having the user be diligent enough to go back and input the SSN. Sorry to go on and on. Just want you to get a picture. I am open to all suggestions please. Thanks
database structure
- Thread starter slimjen1
- Start date
MarkK
bit cruncher
- Local time
- Today, 04:24
- Joined
- Mar 17, 2004
- Messages
- 8,472
The error in design is that you used the SSN as the primary key. Search topic: "meaningful vs meaningless keys."
What you can do going forward is add a new autonumber key field to the primary table, and then write code for each child table that looks up the new key--using the old key--and write the new key to a new foreign key field. You essentially rebuild all your links so that you use meaningless rather than meaningful keys. And this will break a lot of things and it will be a headache. But it's an awesome lesson about what not to use as a key.
You could also, for pending customers, use a negative SSN. That might be an OK work around, but when you get the SSN you'd still need to make sure child records are updated.
hth
What you can do going forward is add a new autonumber key field to the primary table, and then write code for each child table that looks up the new key--using the old key--and write the new key to a new foreign key field. You essentially rebuild all your links so that you use meaningless rather than meaningful keys. And this will break a lot of things and it will be a headache. But it's an awesome lesson about what not to use as a key.
You could also, for pending customers, use a negative SSN. That might be an OK work around, but when you get the SSN you'd still need to make sure child records are updated.
hth
I get this a little. I do have a autonumber for each of my tables. Ive had trouble using autonumbers as primary keys in the past. Can you give me an example of how this would work in my database please.
thank you
thank you
ButtonMoon
Registered User.
- Local time
- Today, 12:24
- Joined
- Jun 4, 2012
- Messages
- 305
Slimjen,
Autonumber is not the solution. The problem implicit in your question is this: what to use as an identifier for customers without an SSN? That's a business question, not a technical one and it isn't solved by adding a surrogate key. Surrogate keys are usually not exposed to business users at all - in fact it's virtually the definition of a surrogate key that it is not an identifier for things in the business domain.
SSN seems like an unusual choice for identifying customers unless you are a government organization. Wouldn't customer's refuse to divulge personal information like that? Most businesses tend to identify customers by an allocated customer number, login name, email address or similar information.
Autonumber is not the solution. The problem implicit in your question is this: what to use as an identifier for customers without an SSN? That's a business question, not a technical one and it isn't solved by adding a surrogate key. Surrogate keys are usually not exposed to business users at all - in fact it's virtually the definition of a surrogate key that it is not an identifier for things in the business domain.
SSN seems like an unusual choice for identifying customers unless you are a government organization. Wouldn't customer's refuse to divulge personal information like that? Most businesses tend to identify customers by an allocated customer number, login name, email address or similar information.
RainLover
VIP From a land downunder
- Local time
- Today, 21:24
- Joined
- Jan 5, 2009
- Messages
- 5,041
I believe Lagbolt has the solution for this situation.
The same solution could be used in all other Tables where natural keys cannot.
All my tables have autonumbers as primary key. SSN is a unique key. I apologize i didn't make this clear. Again; I always had trouble using autonumbers because of identifying with a ramdom number. I will go try again. Thanks for all your help.
Ok. I am taking this step by step to test my forms to use the autonumber as primary and foreign keys. I designed a form with just my main table which runs off a query because I need to get a certain age criteria. The primary key I keep invisible. This query only contains the main table. I add a record name only. When I close and reopen; all records are there except the record I just added. When I add a SSN; which had been used to link other tables before; and close then reopen; I am able to retrieve the record. I am not trying to link another table at this point. Why is this happening? In my table; the SSN is not primary key. I only have the property; yes; no dups. What do I need to do to stop it from seeking the SSN so I can retrieve the record!!
Cronk
Registered User.
- Local time
- Today, 21:24
- Joined
- Jul 4, 2013
- Messages
- 2,799
Jen
I have had similar situations where a certain field, not the PK, had to be kept unique. In those exceptions where for whatever reason, there was no such data for the field. (In my case, it was to do with military databases where secondments from overseas, did not have a military service number allocated.)
In such cases, I would generate what I called, a psuedo number, which was out of range of what might actually be assigned.
Any search now must be on name because obviously the customer won't know the psuedo SSN allocated to him/her.
I have had similar situations where a certain field, not the PK, had to be kept unique. In those exceptions where for whatever reason, there was no such data for the field. (In my case, it was to do with military databases where secondments from overseas, did not have a military service number allocated.)
In such cases, I would generate what I called, a psuedo number, which was out of range of what might actually be assigned.
Any search now must be on name because obviously the customer won't know the psuedo SSN allocated to him/her.
