Solved I am new to Access - don't shoot. Looking for sorting help on a Table (1 Viewer)

[Bosquetia]

I work at an airport. I am trying to help them with their data by using Access. We have about 150 hangars. Some have numbers only others have letters and numbers (ex: T-24). The hangars are unique so I'd like to make the hangar the ID Field but how can I sort so all the J's, all the T's are together and the numbers go from 1-100 in order. Is it even possible?

 

[plog]

2 fields per "hangar number". Letters in their own field, numbers in their own field.

If you jam both letters and numbers together the "numbers" won't sort as numbers, they will sort as text:

Numbers As Numbers Sort Like This: 1, 2,3,...10,11,12...20,21,22...
Numbers As Strings Sort Like This: 1, 10, 11, 12, ...2, 20, 21, 22...

Store your "hangar number" in 2 fields.

 

[plog]

Then you can make both fields a composite key:

Add or change a table’s primary key in Access - Microsoft Support

An Access primary key in an Access database table is a field with unique values for each record in the table. You can use the key to connect data between tables.

support.microsoft.com

 

[Steve R.]

The hangars are unique so I'd like to make the hangar the ID Field ...

Do not use the hangers ID as the primary key. The name of a hanger may change in the future. Just create a regular text field called "hangername" to hold the hangers ID.

 

[Gasman]

Do not use the hangers ID as the primary key. The name of a hanger may change in the future. Just create a regular text field called "hangername" to hold the hangers ID.

Yes, I always preferred autonumber as ID fields.

 

[LarryE]

Save yourself a lot grief. Use an AutoNumber as your Primary Key and use a text field to store your hanger identifiers.

 

[Pat Hartman]

Save yourself a lot grief. Use an AutoNumber as your Primary Key and use a text field to store your hanger identifiers.

Correct. But, you still need a composite unique index on the combination of the two fields. Use the indexes dialog to create it. You cannot create a two-column index using the table interface itself.

 

[Bosquetia]

Fantastic. Thank you for the quick replies.