 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Multiple field Table Sort
I am using Access 2007 and am unable to find specific instructions on how to sort a table by more than one field, I would like to, for example, sort records by city and then within each city by name. I know I can do it with a query or report but would like to be able to work on the records in a specific order. I have tried advanced filter/sort but it didn t seem to work. Many Thanks. Jay, Using a query would be the way to handle it. It is generally a good idea to regard Tables as being background data storage, and not for human consumption, and use the other tools available, as applicable, whenever you need to see or work with the data.  SignatureSteve Schapel, Microsoft Access MVP > I am using Access 2007 and am unable to find specific instructions on how > to sort a table by more than one field, I would like to, for example, sort [quoted text clipped - 6 lines] > > Many Thanks. Steve Schapel <schapel@mvps.org.ns> wrote in news:#5mqiY8XIHA.5088 @TK2MSFTNGP03.phx.gbl: > Jay, > > Using a query would be the way to handle it. It is generally a good > idea to regard Tables as being background data storage, and not for > human consumption, and use the other tools available, as applicable, > whenever you need to see or work with the data. I find it confusing you call it "not for human consumption" since the table will have the actual records that I wish to modify via the attached form. It is my understanding that a query is mostly used to look at records and not to actually modify them --or is there a way to call up the records using the query or some other method? Thanks Jay, The principle avenue for working with data is via a form. The Record Source for a form can be a table or a query (in the form of a saved query object, or an SQL statement). This gives you a lot more power that working directly in the tables themselves. For example, you can control the appearance (formatting) of the data, you can work with data from more than one related table at once, you can work with a subset of the data, and... you can sort the data on multiple fields. If you have a form based directly on a table, if you look at the Properties of the form you will see that there is a property called 'Order By' which you can use to sort the form's data accordong to more than one field. I prefer to control it in the form's Record Source. SignatureSteve Schapel, Microsoft Access MVP > I find it confusing you call it "not for human consumption" since the table > will have the actual records that I wish to modify via the attached form. > > It is my understanding that a query is mostly used to look at records and > not to actually modify them --or is there a way to call up the records > using the query or some other method? How does one format the listing of the field names in properties of the form? Under 'Order by," I put: city,name . The cities were in order but the names within the cities were random. Thanks Steve Schapel <schapel@mvps.org.ns> wrote in news:eA8icuEYIHA.3556 @TK2MSFTNGP02.phx.gbl: > Jay, > [quoted text clipped - 10 lines] > 'Order By' which you can use to sort the form's data accordong to more > than one field. I prefer to control it in the form's Record Source. I'm not really sure, Jay. I have never done it. As mentioned before, I think there are better ways. Still, I would put the field names in []s, and especially with name, which is a Reserved Word (i.e. has a special meaning) in Access, and as such should not really be used as the name of a field or control. So, try it like this: [city], [name] And then, I think you need to put code in there, using for example the form's Load event, like this: Me.OrderByOn = True Let us know how that goes. SignatureSteve Schapel, Microsoft Access MVP > How does one format the listing of the field names in properties of the > form? > > Under 'Order by," I put: city,name . > > The cities were in order but the names within the cities were random. >How does one format the listing of the field names in properties of the >form? > >Under 'Order by," I put: city,name . > >The cities were in order but the names within the cities were random. Is the City a Lookup Field in your table design? If so, it's sorting by the meaningless, numeric city ID. If it is a Lookup field, you may want to base the form on a Query joining the main table to the city table; include the city name from the Cities lookup table and sort by that. You can sort the records on the form in two ways - one would be to put [City];[Name] in the form's OrderBy property, and - in code, you can't do it manually - set the form's OrderByOn property to True. Probably better, and certainly simpler, create a Query exposing all the fields that you want to see and sort by. Put the sort fields left to right in the query grid and put Ascending (or Descending as appropriate) on the Sort row of the query grid. Base the form on this Query and it will respect the sort order. Sorting data in Reports is a bit different - include all the fields you want to sort by in the query and use the Report's Sorting and Grouping dialog to specify the sort order. John W. Vinson [MVP] >It is my understanding that a query is mostly used to look at records and >not to actually modify them --or is there a way to call up the records >using the query or some other method? Your understanding is incorrect, perhaps derived from experience with other software where this is true. It's perfectly routine to base a Form (an appropriate tool for user interaction) on a sorted Query. As noted upthread, a Table should be considered an unordered data repository; your original premise that "I need to sort the table so the user can see it in order" is simply incorrect. For the user to see the records in order, you would use a Query. John W. Vinson [MVP] I created a simple query, however the test sorts based on more than field that I'm creating are not flying. In the lower pane of the design view of the query, "Customer Name" is a column to the left of "City," so apparently I will have to rearrange the fields in the table so that "Customer Name" appears to the right of "City", unless there is another way of doing it. However, when I attempted to sort it by "City", then "Account Number" (after changing the sort row under their respective columns to "ascending"), the data were still not appearing correctly in query datasheet view, even though it is a field that appears to the right of "City". The data sorted correctly under "City" but was random under "Account Number." Thanks. > Jay, > [quoted text clipped - 13 lines] >> >> Many Thanks. > I created a simple query, however the test sorts based on more > than field that I'm creating are not flying. [quoted text clipped - 3 lines] > rearrange the fields in the table so that "Customer Name" appears > to the right of "City", unless there is another way of doing it. Yes, Virginia, there is a Santa Claus. Remove the Ascending statement from the Sort: row under the customer name field. Add the customer name column a second time to the query grid, to the right of your city column. Put the Ascending in this second copy of the column. Uncheck the box under this copy from the Show: row. > However, when I attempted to sort it by "City", then "Account > Number" (after changing the sort row under their respective [quoted text clipped - 4 lines] > > Thanks. Is account number a text column? Do you have a format applied to this column in the table? was it sorting on City, Customer, the naccount number? SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Bob, If you select the column in the query design grid, you can use the little bar at the top of the column to drag it to another position within the grid. Either that, or delete the existing column, and re-insert in the position you want it. I don't think there is any reason to have the same field represented in two columns. SignatureSteve Schapel, Microsoft Access MVP > Remove the Ascending statement from the Sort: row under the customer > name field. > Add the customer name column a second time to the query grid, to the > right of your city column. Put the Ascending in this second copy of > the column. Uncheck the box under this copy from the Show: row. Steve Schapel <schapel@mvps.org.ns> wrote in news:#rRqNETYIHA.4740 @TK2MSFTNGP02.phx.gbl: > Bob, > [quoted text clipped - 3 lines] > re-insert in the position you want it. I don't think there is any > reason to have the same field represented in two columns. I know how to move a column. One can also drag a column in datasheet view, and save the query so that the column order is retained. The original poster wanted a multi-column sort, with the layout of columns differing from the sort sequence. In order to do this, once can code the SQL as SELECT foo, bar, baz from SomeTable ORDER BY baz,foo, bar In the designer, one adds the columns: foo, bar, baz, foo, bar then sets to ascending on the last three columns, and unchecks the show: checkbox on the two rightmost columns. Result: SQL equal to that created above from the designer. SignatureBob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com >I created a simple query, however the test sorts based on more than field >that I'm creating are not flying. [quoted text clipped - 3 lines] >fields in the table so that "Customer Name" appears to the right of "City", >unless there is another way of doing it. That is indeed the way to do it. Fields sort left to right. That has no effect on how you display the data on a Form or Report though. >However, when I attempted to sort it by "City", then "Account Number" (after >changing the sort row under their respective columns to "ascending"), the >data were still not appearing correctly in query datasheet view, even though >it is a field that appears to the right of "City". The data sorted >correctly under "City" but was random under "Account Number." Please use View... SQL to switch the view of the query into SQL view. Copy and paste the SQL text to a message here. Something is not coming across in our explanations and/or your description; the SQL *is* the real query (the grid is just a tool to make it easier to build the query). John W. Vinson [MVP] SELECT [W208-02JAN].[In Date], [W208-02JAN].[CUSTOMER NAME], [W208-02JAN].ADDRESS, [W208-02JAN].CITY, [W208-02JAN].ATTENTION, [W208-02JAN].[ACCT NO], [W208-02JAN].[JOB LOCATION], [W208-02JAN].PRICE, [W208-02JAN].[CUST NO], [W208-02JAN].QTY1, [W208-02JAN].DES1, [W208-02JAN].EA1, [W208-02JAN].TOT1, [W208-02JAN].QTY2, [W208-02JAN].DES2, [W208-02JAN].EA2, [W208-02JAN].TOT2, [W208-02JAN].QTY3, [W208-02JAN].DES3, [W208-02JAN].EA3, [W208-02JAN].TOT3, [W208-02JAN].QTY4, [W208-02JAN].DES4, [W208-02JAN].EA4, [W208-02JAN].TOT4, [W208-02JAN].QTY5, [W208-02JAN].DES5, [W208-02JAN].EA5, [W208-02JAN].TOT5, [W208-02JAN].QTY6, [W208-02JAN].DES6, [W208-02JAN].TOT15, [W208-02JAN].EA6, [W208-02JAN].QTY7, [W208-02JAN].DES7, [W208-02JAN].EA7, [W208-02JAN].TOT7, [W208-02JAN].QTY8, [W208-02JAN].DES8, [W208-02JAN].EA8, [W208-02JAN].TOT8, [W208-02JAN].QTY9, [W208-02JAN].DES9, [W208-02JAN].EA9, [W208-02JAN].TOT9, [W208-02JAN].QTY10, [W208-02JAN].DES10, [W208-02JAN].EA10, [W208-02JAN].TOT10, [W208-02JAN].QTY11, [W208-02JAN].DES11, [W208-02JAN].EA11, [W208-02JAN].TOT11, [W208-02JAN].QTY12, [W208-02JAN].DES12, [W208-02JAN].EA12, [W208-02JAN].TOT12, [W208-02JAN].QTY13, [W208-02JAN].DES13, [W208-02JAN].EA13, [W208-02JAN].TOT13, [W208-02JAN].QTY14, [W208-02JAN].DES14, [W208-02JAN].EA14, [W208-02JAN].TOT14, [W208-02JAN].QTY15, [W208-02JAN].DES15, [W208-02JAN].EA15, [W208-02JAN].TOT6 FROM [W208-02JAN] ORDER BY [W208-02JAN].CITY; >>I created a simple query, however the test sorts based on more than field >>that I'm creating are not flying. [quoted text clipped - 26 lines] > > John W. Vinson [MVP] I didn t notice that sorting under 'acct no' was turned off. Here is the SQL text with it turned on. The data in datasheet view still is not sorted for 'acct no' within each city. SELECT [W208-02JAN].[In Date], [W208-02JAN].[CUSTOMER NAME], [W208-02JAN].ADDRESS, [W208-02JAN].CITY, [W208-02JAN].ATTENTION, [W208-02JAN].[ACCT NO], [W208-02JAN].[JOB LOCATION], [W208-02JAN].PRICE, [W208-02JAN].[CUST NO], [W208-02JAN].QTY1, [W208-02JAN].DES1, [W208-02JAN].EA1, [W208-02JAN].TOT1, [W208-02JAN].QTY2, [W208-02JAN].DES2, [W208-02JAN].EA2, [W208-02JAN].TOT2, [W208-02JAN].QTY3, [W208-02JAN].DES3, [W208-02JAN].EA3, [W208-02JAN].TOT3, [W208-02JAN].QTY4, [W208-02JAN].DES4, [W208-02JAN].EA4, [W208-02JAN].TOT4, [W208-02JAN].QTY5, [W208-02JAN].DES5, [W208-02JAN].EA5, [W208-02JAN].TOT5, [W208-02JAN].QTY6, [W208-02JAN].DES6, [W208-02JAN].TOT15, [W208-02JAN].EA6, [W208-02JAN].QTY7, [W208-02JAN].DES7, [W208-02JAN].EA7, [W208-02JAN].TOT7, [W208-02JAN].QTY8, [W208-02JAN].DES8, [W208-02JAN].EA8, [W208-02JAN].TOT8, [W208-02JAN].QTY9, [W208-02JAN].DES9, [W208-02JAN].EA9, [W208-02JAN].TOT9, [W208-02JAN].QTY10, [W208-02JAN].DES10, [W208-02JAN].EA10, [W208-02JAN].TOT10, [W208-02JAN].QTY11, [W208-02JAN].DES11, [W208-02JAN].EA11, [W208-02JAN].TOT11, [W208-02JAN].QTY12, [W208-02JAN].DES12, [W208-02JAN].EA12, [W208-02JAN].TOT12, [W208-02JAN].QTY13, [W208-02JAN].DES13, [W208-02JAN].EA13, [W208-02JAN].TOT13, [W208-02JAN].QTY14, [W208-02JAN].DES14, [W208-02JAN].EA14, [W208-02JAN].TOT14, [W208-02JAN].QTY15, [W208-02JAN].DES15, [W208-02JAN].EA15, [W208-02JAN].TOT6 FROM [W208-02JAN] ORDER BY [W208-02JAN].CITY, [W208-02JAN].[ACCT NO]; > SELECT [W208-02JAN].[In Date], [W208-02JAN].[CUSTOMER NAME], > [W208-02JAN].ADDRESS, [W208-02JAN].CITY, [W208-02JAN].ATTENTION, [quoted text clipped - 51 lines] >> >> John W. Vinson [MVP] Yikes, my bad -- the 'Acct No' was sorting correctly after all! Since I am using transferred data, I just had entered the data differently for each city at different times by, for example, placing a period after the state in some instances which made the same city appear higher up. Many thanks. >I didn t notice that sorting under 'acct no' was turned off. Here is the >SQL text with it turned on. [quoted text clipped - 84 lines] >>> >>> John W. Vinson [MVP] >Yikes, my bad -- the 'Acct No' was sorting correctly after all! > >Since I am using transferred data, I just had entered the data differently >for each city at different times by, for example, placing a period after the >state in some instances which made the same city appear higher up. That's a relief... and a good example of the advantages of normalization. If you had a table of States and a table of Cities with referential integrity enforced you would have consistant values for the state and the city. It appears that your table structure has lots of repeating fields (EA1 through EA15, TOT1 through TOT15, etc.). Ideally there should be several one to many relationships here... and if TOT means "total" these fields should probably be calculated on the fly rather than stored. Is this table intentionally denormalized? Or have you considered normalizing it? John W. Vinson [MVP] Jay, > In the lower pane of the design view of the query, "Customer Name" is a > column to the left of "City," so apparently I will have to rearrange the > fields in the table so that "Customer Name" appears to the right of > "City", unless there is another way of doing it. No, you don't need to touch the table. You can place the fields in the query design drid in any7 order you like, regardless of their order in the table. > However, when I attempted to sort it by "City", then "Account Number" > (after changing the sort row under their respective columns to > "ascending"), the data were still not appearing correctly in query > datasheet view, even though it is a field that appears to the right of > "City". The data sorted correctly under "City" but was random under > "Account Number." Could it be that Account Number is a Text data type, and you are expecting it to sort numerically? SignatureSteve Schapel, Microsoft Access MVP Yikes, my bad -- the 'Acct No' was sorting correctly after all! Since I am using transferred data, I just had entered the data differently for each city at different times by, for example, placing a period after the state in some instances which made the same city appear higher up. Many Thanks to all. >I created a simple query, however the test sorts based on more than field >that I'm creating are not flying. [quoted text clipped - 30 lines] >>> >>> Many Thanks. I created a sample form off of a query that was created from the table and entered data into the query form. Checking back at the original table I see that the data had changed also, so that solves the problem. I was under the mistaken impression the query would not handle changing the data. Many thanks > Jay, > [quoted text clipped - 13 lines] >> >> Many Thanks. Probably clumsy, but here's a method that works for me: Base your form on a query. Create an expression in the query: Somename:[City]&[Name} Sort by this expression. By the way, "Name" should not be used as a field name, it is a reserved word. >I am using Access 2007 and am unable to find specific instructions on how > to sort a table by more than one field, I would like to, for example, [quoted text clipped - 8 lines] > > Many Thanks. Just wondering, Tom, do you see an advantage of this compared with just sorting in your query on both the separate fields? SignatureSteve Schapel, Microsoft Access MVP > Base your form on a query. > Create an expression in the query: Somename:[City]&[Name} > Sort by this expression. Now that you say it, no. :) It's just a carry over from my early days when I did not realise that the order of fields in the query will also affect the order in which they are sorted. Some habits just don't die. JayC - please strike the last remark. > Just wondering, Tom, do you see an advantage of this compared with just > sorting in your query on both the separate fields? > >> Base your form on a query. >> Create an expression in the query: Somename:[City]&[Name} >> Sort by this expression. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.