Question Primary Key too long Help

[chrisguk]

Hi there,

I have a table that stores account numbers and other data. The account numbers are the primary using a number field with long integer.

The problem I have is some of the account numbers are too long for the long integer max field size. Can I use a text field instead of using number(long integer) or is there and alternative?

Chris

 

[ClareLou80]

Hey Chris,

I'm pretty new to all of this too, but here's my opinion.

I'm also assuming you are using access 2007?

The Text field is an alphanumeric field that can hold up to 256 characters, so yes, you can store numbers in there.

You may also be better off letting the Autonumber be your PK and have account number as a text field. In the property sheet, in design view, you can set the text field so it will not allow duplicates.

Hope this helps!

 

[Galaxiom]

Long text keys are not very efficient.

You would be better introducing a synthetic key (such as autonumber). This has an advantage that one day the account numbering system could change and you will have a much simpler task if it does when you have an independant key.

 

[boblarson]

You can use the DECIMAL number type. It will handle up to 28 digits if you set the PRECISION property to 28 and keep the scale set to 0. It is a 96-bit fixed point number.