Google Answers Logo
View Question
 
Q: MS Access Query ( Answered 5 out of 5 stars,   2 Comments )
Question  
Subject: MS Access Query
Category: Computers > Software
Asked by: dominyon-ga
List Price: $10.00
Posted: 27 Jun 2003 07:02 PDT
Expires: 27 Jul 2003 07:02 PDT
Question ID: 222326
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

Clarification of Question by dominyon-ga on 27 Jun 2003 07:39 PDT
Oh my goodness, Hammer!  You fixed my problem!  I am so happy; yet, I
feel so stupid.  I could have sworn I set that to number.  Oh well, it
is fixed and now I can move on to creating my report and get out early
today.

Thanks so much!  How do I pay/tip you?
Answer  
Subject: Re: MS Access Query
Answered By: hammer-ga on 27 Jun 2003 07:53 PDT
Rated:5 out of 5 stars
 
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

Clarification of Answer by hammer-ga on 27 Jun 2003 08:16 PDT
Dominyon,

By the way, to_be_determined-ga's comment is correct. Depending on
what you need the result to be, you may need to add additional numeric
columns to get the sort you want. Alternately, if you are allowed to
change the formatting of the policy number, you can fix your sort by
reformatting so that the policy numbers always have the same number of
characters.

For example, instead of ...

CP10.1
CP10.10
CP10.4
CP4.1
CP4.10
CP4.4

... you can pad with zeroes, like this ...

CP10.01
CP10.10
CP10.04
CP04.01
CP04.10
CP04.04

... which should cause your values to sort correctly, like this ...

CP01.01
CP01.04
CP01.10
CP10.01
CP10.04
CP10.10

- Hammer
dominyon-ga rated this answer:5 out of 5 stars and gave an additional tip of: $2.00
Thanks for your quick response with a great answer.  I also appreciate
your kindness and explanation of the answer.  This will no doubt be
helpful in the future when I set up additional queries and reports.

I have given you a little tip; not much, I know, but at least you can
get a couple of things from the McDonald's Dollar Menu!  ;)

Comments  
Subject: Re: MS Access Query
From: to_be_determined-ga on 27 Jun 2003 07:41 PDT
 
hammer,

I agree the data type could be a problem.  However, even gixing that
won't solve the issue.  Notice that dominyon's query has "ORDER BY
[Policy Tracking].[Policy Group], [suffix sort].[Sort order]"  [Policy
Tracking].[Policy Group] is not a numeric field, which will cause
"CP10.1" appears before, say, "CP4.1"  I suppose dominyon need a
numeric field to store the chapter number.

My $0.02
Subject: Re: MS Access Query
From: hammer-ga on 27 Jun 2003 08:19 PDT
 
Some fries and a chocolate sundae sound like a great tip! Thank you
for the good rating and your kind words.

- Hammer

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy