How to convert my SQL query to MS Access query?

MS Access Query

• I am creating a query in order to create a form to make a table of contents for a policy and procedure guideline book. The order of the policies is exact and I can't figure out how to sort them correctly. Here's an example of what order this should go in: CP1.1 CP1.1p CP1.1g In Access, I have set up a table that contains the policy ID, the name of the policy and the effective date (this is what needs to be shown on the TOC page). Anyhow, what I have done is created a table that looks like this: (Named Policy Tracking) Policy Group Policy Nbr Suffix Sort CP1.1 CP1.1 Policy CP1.1 CP1.1g Guiudeline CP1.1 CP1.1g Appendix A Guideline Appendix CP1.1 CP1.1p Procedure CP1.1 CP1.1p Appendix A Procedure Then, I set up another table that looks like this: (Named suffix sort) Suffix Sort Sort Order Guidelines 10 Guidelines Appendix 11 PM 12 PM Appendix 13 Policy 1 Policy Appendix 2 PolicyTable 3 Procedure 4 Procedure Appendix 5 Procedure Appendix A Part 1 6 Procedure Appendix A Part 2 7 Procedure Appendix B 8 Procedure Appendix C 9 To get the date for my query, I have it based off of another query, and there are no problems with that (named qry: Latest Versions). I linked the two tables that I wrote out earlier via the suffix sort. This is what my query looks like: SELECT [Policy Tracking].[Policy Nbr], [Policy Tracking].[Policy Name], Max([qry: Latest Versions].[Revised Date]) AS [MaxOfRevised Date] FROM ([Policy Tracking] INNER JOIN [qry: Latest Versions] ON [Policy Tracking].[Policy Nbr] = [qry: Latest Versions].[Policy Nbr]) INNER JOIN [suffix sort] ON [Policy Tracking].[Suffix Sort] = [suffix sort].[Suffix Sort] GROUP BY [suffix sort].[Sort order], [Policy Tracking].[Policy Nbr], [Policy Tracking].[Policy Name], [Policy Tracking].[Policy Group], [Policy Tracking].[Suffix Sort], [Policy Tracking].Status HAVING ((([Policy Tracking].Status)="ACTIVE")) ORDER BY [Policy Tracking].[Policy Group], [suffix sort].[Sort order]; Now, this seemed like it would have worked for me, but when I sort like this, the sort order is not in ascending order, like I checked in the design view. Instead, it looks like this: Policy Nbr CP1.0 CP1.0g CP1.0g Appendix A CP1.0g Appendix B CP1.0g Appendix C CP1.10 CP1.10g CP1.10p As you can see from my sort order table, I numbered the suffices so that the p comes before the g. In ascending order, when I show the sort order, it comes out like this: Sort order 1 10 11 11 11 1 10 4 To see the two together: Sort order Policy Nbr 1 CP1.0 10 CP1.0g 11 CP1.0g Appendix A 11 CP1.0g Appendix B 11 CP1.0g Appendix C 1 CP1.10 10 CP1.10g 4 CP1.10p I need some help here, please. I am not a programmer, by any means. I am doing an internship for my masters degree and need to get this done by Monday and have been banging my head against the wall for the past two days now and cannot figure it out! Please try to get back to me by the end of today. If you can get something to work by noon today (Florida time; it's now 10 here), I will give you a tip. Thanks!

• Answer:

  Dominyon, Don't feel stupid. Everybody gets caught by that one sooner or later. :> To summarize: One of the effects of setting the data type correctly is that it gives Access clues about how you want the information in the field handled. By choosing Text, you tell Access that you want the values treated as Text, therefore, sorts are done alphabetically. By choosing Number, you let Access know that you want the values treated as numbers and sorted numerically. Here is the relevant part of my RFC, for reference: Judging from your example of how your Sort Order values are sorting, at least part of your problem is that the Sort Order field in the Suffix Sort table has a data type of Text, rather than Number. A Text field will sort its values alphabetically, therefore, using your sample data, 10 comes before 4 because, alphabetically, the character 1 is before the character 4. If you change the data type of the column to Number, Access will know to sort the values numerically, rather than alphabetically. As for your question about payment, by posting this answer, I have been paid for your question. If you wish to tip, you can do so when you rate my answer. I am glad that I was able to help you. Good luck with your project! - Hammer