Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re-sorting a table

    I am using Access 2007 linked to a SQL 2008 database.

    I am using an application that displays the primary field in a table as a pick list. The table has two fields, and it was sorted like this.

    [Subject], [Department]
    Economics, Economics
    Accounting, Economics
    Banking, Economics
    Finance, Economics
    Medicine, Medicine
    Anatomy, Medicine
    Pathology, Medicine
    Surgery, Medicine
    ... etc.

    Subjects are sorted alphabetically within Departments, but the first record within each Department is an additional heading record for the Department itself as a Subject.

    I understood that it is preferable for tables to have a primary key, so I applied a primary key to the Subject field, but now the table is sorted like this; i.e., entirely alphabetically.

    [Subject], [Department]
    Accounting, Economics
    Anatomy, Medicine
    Banking, Economics
    Economics, Economics
    Finance, Economics
    Medicine, Medicine
    Pathology, Medicine
    Surgery, Medicine
    ... etc.

    How can I get it back into the original sequence (with or without a primary key)?

    Is there a way to move rows manually while editing the table in SQL Server Management Studio? Alternatively, I have a copy of the original table layout in an Excel spreadsheet; is there a way to copy/paste or convert this through Access or SQL Server?
    Last edited by Murgatroyd; 2011-01-30 at 04:50.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you want the contents of a table displayed in a particular order, use a query to sort them into the order required, and use that query as the source of the pick list.
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. I know how to use queries in Access, but in this case, I have no control over the application, which displays the records from the SQL table as is, hence I need to get it back into its original sequence. I could retype all the fields manually but I hoped that there may be a way to rearrange the existing records into the original sequence, or recreate the table from an Excel file.
    Last edited by Murgatroyd; 2011-01-30 at 06:57.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by Murgatroyd View Post
    Thanks for your reply. I know how to use queries in Access, but in this case, I have no control over the application, which displays the records from the SQL table as is, hence I need to get it back into its original sequence. I could retype all the fields manually but I hoped that there may be a way to rearrange the existing records into the original sequence, or recreate the table from an Excel file.
    Table records have no set order. If you have no control over the presentation order, through a query sort, the only way to get the "desired" order back would be to delete all records and insert them in your desired order. Any future need to insert an "out of order" record would ruin the presentation order again, so the preexisting order was just a fortunate "coincidence".

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If you have access to SQL Server so that you can create a view, you could create a view that sorts the records in the desired order. Views in SQL Server are much like queries in Access, but you can link to them from Access, where you see the view as a table.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your replies. I understand that records in a table have no intrinsic sequence. However, I do not need a way to view or retrieve the records from this table in a particular sequence; I need a way to restore them to their original sequence within the table.

    Before I applied/removed a key, they were displayed in the required sequence when I viewed them not only in the application but also in SQL Server Management Studio. After I applied/removed the key, they are now displayed in a single alphabetical sequence not only in the application but also in SQL Server Management Studio.

    The original, required sequence is not a simple sort but is a series of alphabetical groups with each group preceded by a heading record. This sequence was not coincidental but was by design; i.e., it was a custom list. I don't know how the records were originally arranged to achieve this sequence - presumably by manual entry or from an Excel version - but I need to restore it.

    I have started retyping the fields manually in the required sequence, and this is partly achieving the required result; however, something else seems to have become disrupted, because I get an error when I try to modify some rows ("The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows)"), and some fields now contain duplicates or nulls (not sure how this happened), although I get this error when I try to remove them, whether I allow nulls or not. I could restore the original sequence if I could resolve this error and finish retyping the fields manually.

  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Sorry, can you tell exactly what errors are you getting and what you are doing when you get them?

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Delaware, US
    Posts
    1,161
    Thanks
    19
    Thanked 99 Times in 88 Posts
    Quote Originally Posted by Murgatroyd View Post
    How can I get it back into the original sequence (with or without a primary key)?
    The problem is that they were not in any particular order or sequence to start with. My guess is that they were simply displaying is the order the records were originally entered in.

    To sort records, you have to have something to sort on. Without seeing your application and knowing how it is setup, I cannot give you a definitive answer but I can give you a possible answer. Put something into the table you can sort on in the order you want.

    The answer is going to depend on whether or not your database is in third normal form (3NF). If it is, then you will have a separate Departments table which is related to Subjects. Add a number field (call it SortOrder or whatever) to the Departments table and assign a sequence number to each Department with the numbers in the order you want the Departments to sort in. You can then pull that data into the Subjects table and sort on that first and subject second.

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Marin Co, CA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add a 'sortOrder' field!

    To reorder the rows to an arbitrary order of your choice, just do what you already did -- make a field be the primary key.

    If no field exists that provides the order you want -- ADD ONE! Make it an integer field, and put 1 for the first row, 2, for the second, etc. Then alter the table so that's the primary key, and you're done.

    This will probably work. Changing the primary key reordered it for you once, and it's reasonable for us to expect the same thing will happen if you change the primary key again.

    If that doesn't work, you could take the table (with your sortOrder field), and copy it to a new table, inserting in the desired sortOrder. Then drop the old table, and you have two choices: Rename the new table to the old name, or introduce a view with the old name that refers to the new table with a proper ORDERED BY. I would definitely do the latter, unless there's some odd reason the program won't work with a view (say, it looks at the definition).

    But this is absolutely a bug in your program, and I'd suggest reporting it as such! If you want an order to a returned set of rows, you use ORDERED BY. Plain and simple. But unfortunately, some programmers look and see "Oh, it's already sorted in the order I want", and think "Oh, we'll save doing a sort", and it works -- until it doesn't. Like upgrading your database server, or making any change at all in your table structure, or maybe even doing a few more inserts and/or deletes.

    Sometimes they think GROUP BY will imply a sort. It doesn't.

    They're also not saving any sorting time like they think they are. If the data actually IS sorted (say, because of whatever index it is using to traverse the table, or because the rows are stored in the order dictated by the primary key), then the database engine knows not to do an extra sort.

  10. #10
    New Lounger
    Join Date
    Dec 2009
    Location
    Marin Co, CA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A couple other points:

    First, a random query is LIKELY to use the primary key order, simply because the engine is likely to pick the primary key's index as what to traverse to find all of the records. It may even store them based on that -- but that's an implementation detail. As is how it enumerates the records. Adding an index can always potentially change the traversal order, if the engine decides the query would be more efficient using that index instead.

    Second, a primary key can be compound -- that is, you can make your primary key be Department, Subject -- that will give the order given in your example, sorted by department first, then subject. If that's actually what you want, then use that.

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Location
    Marin Co, CA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by gsmith-plm View Post
    The problem is that they were not in any particular order or sequence to start with. My guess is that they were simply displaying is the order the records were originally entered in.

    To sort records, you have to have something to sort on. Without seeing your application and knowing how it is setup, I cannot give you a definitive answer but I can give you a possible answer. Put something into the table you can sort on in the order you want.

    The answer is going to depend on whether or not your database is in third normal form (3NF). If it is, then you will have a separate Departments table which is related to Subjects. Add a number field (call it SortOrder or whatever) to the Departments table and assign a sequence number to each Department with the numbers in the order you want the Departments to sort in. You can then pull that data into the Subjects table and sort on that first and subject second.
    Unfortunately, your answer is a bit too good for this situation. He doesn't have control over the query; he has to use the existing broken query that doesn't have an ORDERED BY clause. And it doesn't sound like the database is normalized. Otherwise, good answer.

    Putting the sortBy field in the picklist table is the more general solution, though not necessarily the best solution. It allows you any arbitrary picklist ordering. But you may want specifically Department/Subject ordering. But if you really wanted that, you wouldn't have this table at all. You'd have your Department table, and your Subject table with a foreign key to the Department table, and this query would be a join on the two, with an ORDER BY clause, ordering by department name or department number, subject name or subject number, according to preference.

    But you could launch into an entire course on database design based on the problems with this one table.

  12. #12
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,651
    Thanks
    38
    Thanked 161 Times in 139 Posts
    I may be missing something obvious here but why not remove the primary key you applied and create a new compound primary key based on Department, Subject (similar to what Bob suggested a couple of posts back.)

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your replies. The application developer has confirmed that the sorting is done in the application and does not depend on the sequence of the records in the table.

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Murgatroyd View Post
    Thanks for your replies. The application developer has confirmed that the sorting is done in the application and does not depend on the sequence of the records in the table.
    So how come you have a problem?
    Regards
    John



  15. #15
    New Lounger
    Join Date
    Jan 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use the Query Builder

    When you applied your Key to your table, SQL Server likely re-sorted the data (which is how Clustered Indexes work, so the data is retrieved faster). For small Dimension tables such as those for drop-down values it generally isn't necessary to have an index (although a key is a good idea to keep duplicate values from being entered). Now that the data is being retrieved by your key, you will need to force a sort on the data you bring back.

    Make sure you're viewing your form in Design view, select your object in Access (either your Combo Box or List View depending on what you're using), and right-click / select Properties (if the Properties box isn't already displayed in Design mode). Click the Data tab, then in the Row Source on the right-hand side of the property box you'll see the Build button (...). Click that to open the Query Builder. If you just selected your table previously, you'll be prompted if you want to invoke the Query Builder on the table. Click Yes. If you were already using a dynamic SELECT statement, or an existing Query, it should be displayed.

    Make sure you see your Department first in the Selected Fields List (at the bottom of the Query Builder), then Subject (you can double-click or drag-drop the field to the Selected Fields List). Select your Sort Order from the drop-down list. Now click above the Subject field and drag it in front of the Department field. In the Design ribbon you should see a View button in the Results section. You can click the Datasheet View to see the results. If you'd like to see the SQL behind the query, click the View/SQL View. When you're satisfied with the results, click the Close button and the query will be saved to your object as the new Row Source and it should look as it did before.

    Hope this helps.
    Last edited by Tootman; 2011-02-07 at 19:53. Reason: Saw the other posts and don't want to duplicate solutions

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •