Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Sorting Data In An MS-Access Table Using SQL

  1. #1
    Junior Member
    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0

    Sorting Data In An MS-Access Table Using SQL

    Hi everyone.

    I managed to get this working in Excel with the help of some people. The thread posted is here.
    Solved: Conditional Sorting
    I want to do exactly the same thing in my Access table. I have very little knowledge in both Access and in VBA.

    I have some fields in my table that needs to be sorted in a specific way. The first field has numbers (including decimals) or is blank, and is the most important. I need code that will grab just the records that have a value (more than 0) in the first field and then sort them by field 2, 3, and then the fourth field. Then place all of these records at the top of table.

    Hopefully someone can help me.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Can you post a sample access data file? You can upload it to a file share site, and post the link here, if the file size is beyond forum limit
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0
    Ignoring the ID field as the first field, the duration field has blank cells and numbers. As explained in the OP, the records with a duration more than zero must be seperate from the other records when sorting, even if it needs to moved to a seperate table.
    Sample.zip

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-30-2023 at 02:48 PM.

  4. #4
    Junior Member
    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0
    I have uploaded a updated Access database here: Sample2.zip
    This file shows that in table2 is how I need the table to look after the Sorting. Also, something I didn't post in the OP, the record that has a value must have today's date in the Date1 field, that doesn't change when I open the file on a different day. What will happen is, the next day, I will clear the duration field, and later that day, add durations to different records and begin the sorting again.

    Hopefully everything makes sense, and this is easy to do.

  5. #5
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    I have not looked at your db, but as a note -- conditional formatting is only available in Access Forms and Reports. Tables are meant for storing raw data and should not be viewed as spreadsheets.

    Additionally, if you are looking to do sortings, then you should be doing that in either a report or a query. Again, tables are only used for storage and not data presentation. That is a spreadsheet mindset and not a relational database mindset. They are very different and if you plan on working in a RDBMS you need to adopt a database mindset.

    Alan
    Last edited by alansidman; 09-21-2013 at 01:59 AM.

  6. #6
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    I have looked at your db and you will have issues sorting in a query as you have demo'd in Table 2. You have text for your clients, ie. client 1, client 2, etc. This data will sort in this manner, client 1 , client 10, client 11, client 2, client 20, etc. You will need to change your fields to remove the descriptor from the field data. This is true for all fields that you have set up this way. Once you set your data in the appropriate manner, you can then query the table.

    Your query would resemble this.
    Code:
    SELECT Today.ID, Today.Duration, Today.Client, Today.Project, Today.Task, Today.Narration, Today.Date1
    FROM Today
    WHERE (((Today.Duration) Is Not Null))
    ORDER BY Today.Client, Today.Project, Today.Task;
    BTW, the word today is a reserved term in Access and should be avoided as it may cause issues.

  7. #7
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    I have adapted your db as shown in Table1 and built three queries, 2,3 and 4. Query 4 appears to be what you are looking for. It is Union All query that joins Query 2 with Query 3.
    Attached Files Attached Files

  8. #8
    Junior Member
    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0
    Thanks Alan and sorry for the late reply.

    I originally had this "mini-program" built in Excel using VBA etc. working perfectly, as seen in my OP. I wanted to re-build it in Access because of the advanced features and more importantly, learn Access and SQL/VBA by building an application.

    With regards to the Client names, none of them actually have any numbers in them, its all text, I just used the numbering to differentiate the different "clients". I don't mind using queries to acheive the sorting I need. The table will grow by about 5-10 records a day and I will set up a form that will add data to the table so I don't enter the info directly. I will also need a macro/button that will clear the entire Duration field before adding anything new at the start of the day.

    Thanks again for the help.

  9. #9
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    I will also need a macro/button that will clear the entire Duration field before adding anything new at the start of the day.
    I will work on this later today as I have time. This will require an update query to be run against the table. If you want to give it a shot yourself, then look at this

    Microsoft Access Update Query - YouTube

    You will want to update the value in the duration to NULL

  10. #10
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    Here is the SQL statement for the update query that will reset the durations in the table to null. In a form, add a command button and set the command button to run this query.

    Code:
    UPDATE [Copy Of Today] SET [Copy Of Today].Duration = Null;

Similar Threads

  1. Export data from Excel to Access Table (ADO) using VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 4
    Last Post: 02-24-2015, 07:53 PM
  2. Make Table SQL Query In Access That Adds A Primary Key
    By Portucale in forum Access Help
    Replies: 5
    Last Post: 08-08-2013, 10:02 PM
  3. Replies: 8
    Last Post: 08-08-2013, 03:52 AM
  4. Replies: 2
    Last Post: 02-23-2013, 09:18 PM
  5. Upload Excel Data to SQL Table
    By littleiitin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 08-22-2012, 11:02 AM

Posting Permissions

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