What are the ideal fields for table relationships

[tbjmobile@gmail.com]

I was recently helped by various people on this forum for which I am very grateful, my thanks to all of them. During this process I was advised that using short text fields as linking fields, primary and foreign, was not good. I said I would look into this but I'm finding difficulty finding the answer so perhaps someone could point me to a source of information on this subject.


The problem is that historically, my firm has used a customer code system and those codes are 001, 002 up to about 075. I can't easily change these to number fields because they obviously won't translate the leading zero/s to which everyone has become accustomed over the years. If it is going to lead to problems later on though, is it best to always use an Autonumber ID field in all of my tables in order to create the relationships, and still incorporate my customer field as it is now? Almost all of the tables are based on the customer codes as it is common throughout all the work that we do.
Thank you.

[ranman256]

for customers, internally I use autonum. You CAN also assign your in-house Client# as string 001, etc..
but internally, the system uses Client (autoNum) as a generated # and all related tables use this number.
Works great. For the users, they can keep their human Client# if they need, and they wont know the difference.

[tbjmobile@gmail.com]

Thanks ranman256 (again).
Do you mean use an autonumber field in (say) the tblCustomers table but not use it except for linking purposes? Then in (say) the tblWork table, use an autonumber field also, just for the link and use the "001" to identify the record linked to. The reason I have to ask is that almost all of my tables use the "001" to get the data from each other and so while in the tblCustomers, each record is unique, the tblWork will have loads of "001" s and the tblBookings will have lots of customer codes too. So should I use an autonumber field in all the tables just to link them together and then carry on as now and use the "external" customer codes to get the actual data used, that the staff and customers will see and that I use for grouping?
Thanks.

[ranman256]

if youve already used the string field, that is fine. It still works.
continue using them for customer links.

it just means you have to create your own keys manually.

[tbjmobile@gmail.com]

Thank you.

[ssanfu]

If you have been using a text field as a PK field, you can still use it, but it is not optimal. A PK field should not have any real world meaning.

Here are reasons for using an autonumber as the PK field.
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

[tbjmobile@gmail.com]

Understood, thank you both.