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! |
Request for Question Clarification by
hammer-ga
on
27 Jun 2003 07:25 PDT
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.
Try changing that and let me know what happens. If that solves your
problem, I'll post this as an offical answer. If not, post how it is
sorting once the data type change is made, and we'll look for
additional problems.
If you need instructions on how to change the data type, please post
which version of Access you are using.
- Hammer
|
