Access 2003-Sort by last letters in a column

laurence997

Hi guys,
I'm having a small problem with a table that I have in access, I hope ye can help me:).
I have column in a table in access which contains a list of numbers which identify animals. My problem is that halfway through the number there is one random number, and when I try to order the number it is put in order of the random number in the middle and not the last four numbers, as I would like it to.
I have coloured the random number red below, and the number that I would like to organise them by is in blue.

Eg. 100050100
100060101
100070102

Thanks.

Snowbat

If the random number is always a single digit at position 5 and the table is not greater than 65536 rows, you could:

1. Export it to Excel
2. Copy the numbers column into a new column
3. Split the copy at the sixth digit using Convert Text to Columns (Split cell content based on a column break)
4. Sort by the new column that contains the latter part of the number
5. Delete the columns you just created
6. If necessary, import back into Access

toe_knee

Try this:-

1. Click Insert, Query from the main menu. Choose Design View.
2. Add the table(s) you want by clicking the Show Table button (it should be the one in between the ! and the Σ buttons.
3. Once you've selected the tables you're ready to add the fields to your query. Drag and drop the fields onto the query grid.
4. Now, in the first blank column to the right of your screen type this:- SortField:Right$([TableName].[FieldName],4) YOU MUST replace TableName and FieldName to match your data.
5. Click in your newly created field and choose Ascending from the Sort drop-down box.
6. Finally, uncheck the Show checkbox.
7. Run your query.

laurence997

Snowbat wrote: »

If the random number is always a single digit at position 5 and the table is not greater than 65536 rows, you could:

1. Export it to Excel
2. Copy the numbers column into a new column
3. Split the copy at the sixth digit using Convert Text to Columns (Split cell content based on a column break)
4. Sort by the new column that contains the latter part of the number
5. Delete the columns you just created
6. If necessary, import back into Access

I suppose I could do that, but really I'd rather to keep it in Access, as I'll regularly be adding data to the table and dont want to continually have to keep doing this(I'm just a bit lazy:p). Thanks for the info anyway:)

toe_knee wrote: »

Try this:-

1. Click Insert, Query from the main menu. Choose Design View.
2. Add the table(s) you want by clicking the Show Table button (it should be the one in between the ! and the Σ buttons.
3. Once you've selected the tables you're ready to add the fields to your query. Drag and drop the fields onto the query grid.
4. Now, in the first blank column to the right of your screen type this:- SortField:Right$([TableName].[FieldName],4) YOU MUST replace TableName and FieldName to match your data.
5. Click in your newly created field and choose Ascending from the Sort drop-down box.
6. Finally, uncheck the Show checkbox.
7. Run your query.

Yes, that does seem like a good method. I'll be back in a few minutes after I try this.:):)

laurence997

Yes, Tony, that method did work. Can I add one of these fields to the original table or would that work? I'm guessing it won't work, but the only experience I have with access is a basic ECDL cert.:o

Also, one more small question. There is a form which has a combo box in it, and the combo box uses this list of numbers as its data source. Is there any way that I could set it up so that as I use the form and go through the animal identification numbers, filling in the required info for each animal that the identification numbers of the animals I have already entered the info for disappear from the combo box?
I have currently got it set that it doesn't allow me to enter the same number twice, but when there are hundreds of numbers I would far rather that it would show the numbers only that I haven't used, rather than me have to use a query to find the animals that I have to enter the data for, and the find the identification numbers in the combo box.
I was thinking of using something like the following in the "row source" of the combo box, but it doesn't work. I am not surprised as I never learned how to set the source of a combo box manually and it was really more of a shot in the dark.

SELECT [All Animals].[Eartag Number] FROM [All Animals] NOT [Survey].[Eartag Number] FROM [Survey];

This is where the table with the identification numbers is "All Animals", the identification numbers are in the column "Eartag Number". The table that the form inputs the data to is "Survey" and the combo box uses lookup to find the values for the column "Eartag Number" in the All Animals table and I can then use them in the Survey form.
Perhaps I should just set up a query and set it to take the values from that instead, but does a query update automatically as I go through the animals?

Sorry if all thats a bit confusing, but I'm just starting to use Access now, and if ye can't understand what I mean I'll try to explain it a bit better.

FSL

Any field in a database can be accessed using the whole field or part of it.

If it is a character field then you can use the left or right or substring functions to access the portion of the string you want.

If the field is numeric then you just perform whatever arithmetic operations are needed to extract the required portion.