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.

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

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.
Attachment 1229

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

I have uploaded a updated Access database here: Attachment 1230
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.

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

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.

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.

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.

Quote:

---

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

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;

---

I have watched the video and will also watch some of the other tutorials. I have added the Update query to my database and I will continue to work with my database to get the final application I want on my own.