Question about sorting multiple columns...

[mint]

Hello i'm new here~

I'm using access 2002. I have a table database with multiple columns (one column is used for street names, and another for street numbers). I would like to organize in a way so that the street names are in alphabetical order, and the street numbers in the other column are organized numerically in respect to the street names.

This is how I would like to have it (example)

st_no | st_name
----------------------
153 | Abram
250 | Abram
12 | Kingston
31 | Kingston
53 | Kingston
659 | Lacy

(the street names in alphabetical order, and the street numbers in numerical order in respect to the street names.)


I don't know how to do it correctly. i tried advanced filter/sort (whatever that is) but no success. please help! thanks! :D

 

[Joe4]

Sort them in a Query. Keep in mind that if you have elected to sort by more than one field, it will by the left most Sort field first. Here is how we get around that.

In a Query, select your table and all your fields. Now add the Street Name and Street Number fields again (make sure the Street Name field is to the left of Street Number). Select Ascending for the Sort order of both of these fields and uncheck the "Show" box.

Now your Query will sort by Street Name before Street Number even though your query will display the Street Number field before the Street Name field.

 

[mint]

I tried to follow by your advice, but I am having trouble since I'm just a beginner. Heres what I did:

Open my mdb file.
Queries->create query using wizard
Added all fields (I have more than 2)
i see several check boxes...
design view..?

i know i'm really dumb, but could you explain more by detail? thanks.

 

[Joe4]

Go into Design View. At the top you should see a square with your table name and available fields. Down below, you will see a grid with the fields you selected for the query.

In the table box up above, find the Street Name field and double-click on it to add it to your grid (or click and drag to the end of the grid). Do the same with the Street Number feld.

Now make sure that the Sort parameter for all of your fields in the grid is blank. Now go to the last two fields you added in the grid. Uncheck the "Show" box for each, and select "Ascending" for the Sort on each field.

Voila! Now if you go back to Datasheet View, it should be sorted like you want.

 

[mint]

I followed directions..but sadly, no success.

here is what i had done so far:

i went to datasheet, but it is not done right.

 

[drjekyll325]

Mint,

Can you go to SQL View and give us the query definition?

 

[mint]

SELECT FtWorth.[Last name], FtWorth.[First name], FtWorth.[Mid name], FtWorth.st_no, FtWorth.st_dir, FtWorth.st_name, FtWorth.st_ty, FtWorth.[apt#], FtWorth.city, FtWorth.state, FtWorth.zip, FtWorth.ph1, FtWorth.ph2, FtWorth.ph3, FtWorth.map1, FtWorth.map2, FtWorth.status
FROM FtWorth
ORDER BY FtWorth.st_name, FtWorth.st_no;

 

[drjekyll325]

This should work, as long as your street number field is a number data type. Do you have it set as text?

 

[drjekyll325]

You can do it in the table design view. However, you will have some problems if there is non-numeric data in a text field that you are changing to a number.

You could also sort like this instead of changing the data type:

ORDER BY FtWorth.st_name, val(FtWorth.st_no);

Be warned that you may get some weird results with non-numeric data!

 

[mint]

I would like to give my thanks to jmiskey for helping me out, and continuing to do so even when i'm really newb at this.

And I also want to thank drjekyll325 for testing out the problem, especially the text/number data type.

Thanks to the forum ! hurray