Incorrect sort results within query

  • Thread starter Thread starter KathyW
  • Start date Start date
K

KathyW

Is there a limit of the number of sort items one can have within a query. We
are using MS Access 2002. I am using the following query but getting
incorrect sort results. The 6th sort item is not sorting correctly. See
query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND ([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation parents].CountryCode)="lt")) OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation parents].RegID)=21404) AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

Thanks.

Kathy
 
It all starts with the data...

Please provide an example of the data that you have in that "6th" field, how
you'd expect it to sort, and how it is sorting...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

Thanks for responding.

Here are a few columns from the query results Reference Date, Reference ID
and Langauage Sort The following rows are displayed...

Reference Date Reference ID LanguageSort
10/9/2007 21401 Lithuanian
10/9/2007 21401 AAAA
10/15/2007 21404 AAAA
10/15/2007 21404 Lithuanian

Rows 1 & 2 are out of order. Rows 3 & 4 are correct.

I copied the data into a little spreadsheet to show you. You probably need
to see more than just the columns displayed above. How do I attach the file?
Thanks.

Jeff Boyce said:
It all starts with the data...

Please provide an example of the data that you have in that "6th" field, how
you'd expect it to sort, and how it is sorting...

Regards

Jeff Boyce
Microsoft Office/Access MVP

KathyW said:
Is there a limit of the number of sort items one can have within a query.
We
are using MS Access 2002. I am using the following query but getting
incorrect sort results. The 6th sort item is not sorting correctly. See
query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND ([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation parents].CountryCode)="lt")) OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation parents].RegID)=21404) AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

Thanks.

Kathy
Click to expand...
Click to expand...
 
Your sample data doesn't seem to display the columns used to sort. Does the
Reference Date field contain a time element?

You can't attach files to messages in these news groups. You could create an
image file or other information on another web page and provide a link. It is
generally advisable to type or copy and paste records into the body of your
reply.
--
Duane Hookom
Microsoft Access MVP


KathyW said:
Jeff,

Thanks for responding.

Here are a few columns from the query results Reference Date, Reference ID
and Langauage Sort The following rows are displayed...

Reference Date Reference ID LanguageSort
10/9/2007 21401 Lithuanian
10/9/2007 21401 AAAA
10/15/2007 21404 AAAA
10/15/2007 21404 Lithuanian

Rows 1 & 2 are out of order. Rows 3 & 4 are correct.

I copied the data into a little spreadsheet to show you. You probably need
to see more than just the columns displayed above. How do I attach the file?
Thanks.

Jeff Boyce said:
It all starts with the data...

Please provide an example of the data that you have in that "6th" field, how
you'd expect it to sort, and how it is sorting...

Regards

Jeff Boyce
Microsoft Office/Access MVP

KathyW said:
Is there a limit of the number of sort items one can have within a query.
We
are using MS Access 2002. I am using the following query but getting
incorrect sort results. The 6th sort item is not sorting correctly. See
query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND ([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation parents].CountryCode)="lt")) OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation parents].RegID)=21404) AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

Thanks.

Kathy
Click to expand...
Click to expand...
Click to expand...
 
To display all the columns is too much. It becomes very hard to read in the
amount of space provided. In the previous email, I was just showing the last
few columns which are the ones having problems.

The date does not have a time reference. Even if it did, it is at the end
of the sort. The 'AAAA' language sort value should still appear before the
'Lithuanian' value.

I am working on getting a html version outside our firewall so you can see
the data more clearly.

Thanks.

Kathy

Duane Hookom said:
Your sample data doesn't seem to display the columns used to sort. Does the
Reference Date field contain a time element?

You can't attach files to messages in these news groups. You could create an
image file or other information on another web page and provide a link. It is
generally advisable to type or copy and paste records into the body of your
reply.
--
Duane Hookom
Microsoft Access MVP


KathyW said:
Jeff,

Thanks for responding.

Here are a few columns from the query results Reference Date, Reference ID
and Langauage Sort The following rows are displayed...

Reference Date Reference ID LanguageSort
10/9/2007 21401 Lithuanian
10/9/2007 21401 AAAA
10/15/2007 21404 AAAA
10/15/2007 21404 Lithuanian

Rows 1 & 2 are out of order. Rows 3 & 4 are correct.

I copied the data into a little spreadsheet to show you. You probably need
to see more than just the columns displayed above. How do I attach the file?
Thanks.

Jeff Boyce said:
It all starts with the data...

Please provide an example of the data that you have in that "6th" field, how
you'd expect it to sort, and how it is sorting...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there a limit of the number of sort items one can have within a query.
We
are using MS Access 2002. I am using the following query but getting
incorrect sort results. The 6th sort item is not sorting correctly. See
query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND ([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation parents].CountryCode)="lt")) OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation parents].RegID)=21404) AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

Thanks.

Kathy
Click to expand...
Click to expand...
Click to expand...
 
The three fields you provided as an example don't seem to match up with the
first five fields your original post mentioned.

If I had to guess (since I don't know what's being stored in those first
five fields), I'd guess that the [RegID] field contains data that forces the
sort order BEFORE you get to [LanguageSort].

Regards

Jeff Boyce
Microsoft Office/Access MVP


KathyW said:
Jeff,

Thanks for responding.

Here are a few columns from the query results Reference Date, Reference ID
and Langauage Sort The following rows are displayed...

Reference Date Reference ID LanguageSort
10/9/2007 21401 Lithuanian
10/9/2007 21401 AAAA
10/15/2007 21404 AAAA
10/15/2007 21404 Lithuanian

Rows 1 & 2 are out of order. Rows 3 & 4 are correct.

I copied the data into a little spreadsheet to show you. You probably
need
to see more than just the columns displayed above. How do I attach the
file?
Thanks.

Jeff Boyce said:
It all starts with the data...

Please provide an example of the data that you have in that "6th" field,
how
you'd expect it to sort, and how it is sorting...

Regards

Jeff Boyce
Microsoft Office/Access MVP

KathyW said:
Is there a limit of the number of sort items one can have within a
query.
We
are using MS Access 2002. I am using the following query but getting
incorrect sort results. The 6th sort item is not sorting correctly.
See
query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND ([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation parents].CountryCode)="lt"))
OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation parents].RegID)=21404)
AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

Thanks.

Kathy
Click to expand...
Click to expand...
Click to expand...
 
Kathy (sorry for any duplication -- my email app just 'hiccupped')

Check the previous 5 fields ... I'm guessing [RegID] has a value that sorts
BEFORE [LanguageSort]

Regards

Jeff Boyce
Microsoft Office/Access MVP


KathyW said:
Jeff,

Thanks for responding.

Here are a few columns from the query results Reference Date, Reference ID
and Langauage Sort The following rows are displayed...

Reference Date Reference ID LanguageSort
10/9/2007 21401 Lithuanian
10/9/2007 21401 AAAA
10/15/2007 21404 AAAA
10/15/2007 21404 Lithuanian

Rows 1 & 2 are out of order. Rows 3 & 4 are correct.

I copied the data into a little spreadsheet to show you. You probably
need
to see more than just the columns displayed above. How do I attach the
file?
Thanks.

Jeff Boyce said:
It all starts with the data...

Please provide an example of the data that you have in that "6th" field,
how
you'd expect it to sort, and how it is sorting...

Regards

Jeff Boyce
Microsoft Office/Access MVP

KathyW said:
Is there a limit of the number of sort items one can have within a
query.
We
are using MS Access 2002. I am using the following query but getting
incorrect sort results. The 6th sort item is not sorting correctly.
See
query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND ([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation parents].CountryCode)="lt"))
OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation parents].RegID)=21404)
AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

Thanks.

Kathy
Click to expand...
Click to expand...
Click to expand...
 
with

ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;


only if HeadingName are equal, for two or more record, will the five other
fields taken into account. And, then, only if more than one has the same
RegulationType the four other fields will be took into account, etc.

HeadingName, RegulationType, RegionName, SortTItle, RegId, LanguageSort,
ReferenceDate
toto 10 11
yo 16 Lithuanian ...
toto 11 9
aa 11 aaaa ...
toto 11 9
aa 11 Lithuanian ...

is thus properly ordered.




Vanderghast, Access MVP



KathyW said:
To display all the columns is too much. It becomes very hard to read in
the
amount of space provided. In the previous email, I was just showing the
last
few columns which are the ones having problems.

The date does not have a time reference. Even if it did, it is at the end
of the sort. The 'AAAA' language sort value should still appear before
the
'Lithuanian' value.

I am working on getting a html version outside our firewall so you can see
the data more clearly.

Thanks.

Kathy

Duane Hookom said:
Your sample data doesn't seem to display the columns used to sort. Does
the
Reference Date field contain a time element?

You can't attach files to messages in these news groups. You could create
an
image file or other information on another web page and provide a link.
It is
generally advisable to type or copy and paste records into the body of
your
reply.
--
Duane Hookom
Microsoft Access MVP


KathyW said:
Jeff,

Thanks for responding.

Here are a few columns from the query results Reference Date, Reference
ID
and Langauage Sort The following rows are displayed...

Reference Date Reference ID LanguageSort
10/9/2007 21401 Lithuanian
10/9/2007 21401 AAAA
10/15/2007 21404 AAAA
10/15/2007 21404 Lithuanian

Rows 1 & 2 are out of order. Rows 3 & 4 are correct.

I copied the data into a little spreadsheet to show you. You probably
need
to see more than just the columns displayed above. How do I attach the
file?
Thanks.

:

It all starts with the data...

Please provide an example of the data that you have in that "6th"
field, how
you'd expect it to sort, and how it is sorting...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there a limit of the number of sort items one can have within a
query.
We
are using MS Access 2002. I am using the following query but
getting
incorrect sort results. The 6th sort item is not sorting
correctly. See
query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND
([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation
parents].CountryCode)="lt")) OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21404) AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation
parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

Thanks.

Kathy
Click to expand...
Click to expand...
Click to expand...
 
Jeff,

[RegID] is the Reference ID (2nd column) in the previous email.

The first four columns have the following data...
[regulation parents].HeadingName = Air Emission Management
[regulation parents].RegulationType = 2
[pseudo countries].[Region name] = ''
[regulation parents].SortTitle = order no. d1-516 of the minister of
environment 9 october 2007 approving the amendment of the order no. 80 of the
me of 27 february 2002 on the approval of the rules for issuance, renewal and
cancellation of the ippc permits (žin., 2007, nr. 106-4358)

So the last three columns look like this...


[RegID] [LanguageSort] [ReferenceDate]
21401 Lithuanian 10/09/2007
21401 AAAA 10/09/2007
21404 AAAA 10/14/2007
21404 Lithuanian 10/14/2007

We were expecting the last three columns of the sort result to look like
this...

[RegID] [LanguageSort] [ReferenceDate]
21401 AAAA 10/09/2007
21401 Lithuanian 10/09/2007
21404 AAAA 10/14/2007
21404 Lithuanian 10/14/2007


As I mentioned before, it would probably make more sense if you could see
the spreadsheet or the actual table. We have set up an area outside our
firewall where you can access the table but it requires logging in and I
would rather not give out the login information on the public forum. Let me
know if there is a way in which I can send you the login information if you
want to take a look.

Thanks.

Kathy


Jeff Boyce said:
Kathy (sorry for any duplication -- my email app just 'hiccupped')

Check the previous 5 fields ... I'm guessing [RegID] has a value that sorts
BEFORE [LanguageSort]

Regards

Jeff Boyce
Microsoft Office/Access MVP


KathyW said:
Jeff,

Thanks for responding.

Here are a few columns from the query results Reference Date, Reference ID
and Langauage Sort The following rows are displayed...

Reference Date Reference ID LanguageSort
10/9/2007 21401 Lithuanian
10/9/2007 21401 AAAA
10/15/2007 21404 AAAA
10/15/2007 21404 Lithuanian

Rows 1 & 2 are out of order. Rows 3 & 4 are correct.

I copied the data into a little spreadsheet to show you. You probably
need
to see more than just the columns displayed above. How do I attach the
file?
Thanks.

Jeff Boyce said:
It all starts with the data...

Please provide an example of the data that you have in that "6th" field,
how
you'd expect it to sort, and how it is sorting...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there a limit of the number of sort items one can have within a
query.
We
are using MS Access 2002. I am using the following query but getting
incorrect sort results. The 6th sort item is not sorting correctly.
See
query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND ([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation parents].CountryCode)="lt"))
OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation parents].RegID)=21404)
AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

Thanks.

Kathy
Click to expand...
Click to expand...
Click to expand...
 
you have to remember the order your sort is in the order it will sort
the columns

sort 1
[regulation parents].HeadingName,
sort 2
[regulation parents].RegulationType,
sort 3
[pseudo countries].[Region name],
sort 4
[regulation parents].SortTitle,
sort 5
[regulation parents].RegID,
sort 6
[regulation parents].LanguageSort,
sort 7
[regulation parents].ReferenceDate DESC;

because reg type is sort 2 10 is less than 11 so your sort is correct

if you chaneg the order of the roder by you can set it up to sort how
you want it to appear


ORDER BY [regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC,
[regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle;

what the list of order by the sort order is now different and it
should give you the results you want

when it comes down to it do you need to sort by all thoes columns or
just the three because the mroe you sort by the slower your query is

ORDER BY [regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

if you only need the three then only sort by the three but it is up to
you

Regards
Kelvan
 
Understood. We have whittled down the database to this simple set of data
that illustrates the problem. In our sample, HeadingName, RegulationType,
RegionName, and SortTitle are identical. The difference doesn't start until
we get to REGId. So the result is not properly ordered for the first two
records with a REGID of 21401. The 'AAAA' value for LanguageSort should come
before the 'Lithuanian' value. The sort is correct for the REGID 21404. The
'AAAA' LanguageSort comes before 'Lithuanian'.

Michel Walsh said:
with

ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;


only if HeadingName are equal, for two or more record, will the five other
fields taken into account. And, then, only if more than one has the same
RegulationType the four other fields will be took into account, etc.

HeadingName, RegulationType, RegionName, SortTItle, RegId, LanguageSort,
ReferenceDate
toto 10 11
yo 16 Lithuanian ...
toto 11 9
aa 11 aaaa ...
toto 11 9
aa 11 Lithuanian ...

is thus properly ordered.




Vanderghast, Access MVP



KathyW said:
To display all the columns is too much. It becomes very hard to read in
the
amount of space provided. In the previous email, I was just showing the
last
few columns which are the ones having problems.

The date does not have a time reference. Even if it did, it is at the end
of the sort. The 'AAAA' language sort value should still appear before
the
'Lithuanian' value.

I am working on getting a html version outside our firewall so you can see
the data more clearly.

Thanks.

Kathy

Duane Hookom said:
Your sample data doesn't seem to display the columns used to sort. Does
the
Reference Date field contain a time element?

You can't attach files to messages in these news groups. You could create
an
image file or other information on another web page and provide a link.
It is
generally advisable to type or copy and paste records into the body of
your
reply.
--
Duane Hookom
Microsoft Access MVP


:

Jeff,

Thanks for responding.

Here are a few columns from the query results Reference Date, Reference
ID
and Langauage Sort The following rows are displayed...

Reference Date Reference ID LanguageSort
10/9/2007 21401 Lithuanian
10/9/2007 21401 AAAA
10/15/2007 21404 AAAA
10/15/2007 21404 Lithuanian

Rows 1 & 2 are out of order. Rows 3 & 4 are correct.

I copied the data into a little spreadsheet to show you. You probably
need
to see more than just the columns displayed above. How do I attach the
file?
Thanks.

:

It all starts with the data...

Please provide an example of the data that you have in that "6th"
field, how
you'd expect it to sort, and how it is sorting...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there a limit of the number of sort items one can have within a
query.
We
are using MS Access 2002. I am using the following query but
getting
incorrect sort results. The 6th sort item is not sorting
correctly. See
query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND
([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation
parents].CountryCode)="lt")) OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21404) AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation
parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

Thanks.

Kathy
Click to expand...
Click to expand...
Click to expand...
 
I think I might be confusing everyone. Let me try again...

I am using the following query but getting incorrect sort results. The 6th
sort item is not sorting correctly. See query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND ([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation parents].CountryCode)="lt")) OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation parents].RegID)=21404) AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;


We have whittled down our database to a simple illustration of the problem.
Therefore, the first four columns have identical data...

[regulation parents].HeadingName = Air Emission Management
[regulation parents].RegulationType = 2
[pseudo countries].[Region name] = ''
[regulation parents].SortTitle = 'order no. d1-516 of the minister of
environment 9 october 2007 approving the amendment of the order no. 80 of the
me of 27 february 2002 on the approval of the rules for issuance, renewal and
cancellation of the ippc permits (žin., 2007, nr. 106-4358)'


So the data differences does not show up until the last three columns which
look like this...


[RegID] [LanguageSort] [ReferenceDate]
21401 Lithuanian 10/09/2007
21401 AAAA 10/09/2007
21404 AAAA 10/14/2007
21404 Lithuanian 10/14/2007

For RegID 21401 the sort order for LanguageSort is incorrect; however the
sort order for RegID 21404 is correct.

We were expecting the last three columns of the sort result to look like
this...

[RegID] [LanguageSort] [ReferenceDate]
21401 AAAA 10/09/2007
21401 Lithuanian 10/09/2007
21404 AAAA 10/14/2007
21404 Lithuanian 10/14/2007


Does that help?

Kathy
Lord Kelvan said:
you have to remember the order your sort is in the order it will sort
the columns

sort 1
[regulation parents].HeadingName,
sort 2
[regulation parents].RegulationType,
sort 3
[pseudo countries].[Region name],
sort 4
[regulation parents].SortTitle,
sort 5
[regulation parents].RegID,
sort 6
[regulation parents].LanguageSort,
sort 7
[regulation parents].ReferenceDate DESC;

because reg type is sort 2 10 is less than 11 so your sort is correct

if you chaneg the order of the roder by you can set it up to sort how
you want it to appear


ORDER BY [regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC,
[regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle;

what the list of order by the sort order is now different and it
should give you the results you want

when it comes down to it do you need to sort by all thoes columns or
just the three because the mroe you sort by the slower your query is

ORDER BY [regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

if you only need the three then only sort by the three but it is up to
you

Regards
Kelvan
Click to expand...
 
I see. It seems your field SortTitle is such that you exceed the limit:

Sort limit : 255 characters in one or more fields



Try to


ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
Left( [regulation parents].SortTitle, 50),
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;



as example.


Vanderghast, Access MVP

KathyW said:
I think I might be confusing everyone. Let me try again...

I am using the following query but getting incorrect sort results. The
6th
sort item is not sorting correctly. See query below...

SELECT [regulation parents].*, [pseudo countries].[Region name]
FROM [pseudo countries] RIGHT JOIN [regulation parents] ON ([pseudo
countries].CountryCode = [regulation parents].CountryCode) AND ([pseudo
countries].RegionCode = [regulation parents].RegionCode)
WHERE ((([regulation parents].RegulationType)>0) AND (([regulation
parents].RegID)=21401) AND (([regulation parents].CountryCode)="lt")) OR
((([regulation parents].RegID)=23426) AND (([regulation
parents].CountryCode)="lt")) OR ((([regulation parents].RegID)=21404) AND
(([regulation parents].CountryCode)="lt")) OR ((([regulation
parents].RegID)=21406) AND (([regulation parents].CountryCode)="lt"))
ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;


We have whittled down our database to a simple illustration of the
problem.
Therefore, the first four columns have identical data...

[regulation parents].HeadingName = Air Emission Management
[regulation parents].RegulationType = 2
[pseudo countries].[Region name] = ''
[regulation parents].SortTitle = 'order no. d1-516 of the minister of
environment 9 october 2007 approving the amendment of the order no. 80 of
the
me of 27 february 2002 on the approval of the rules for issuance, renewal
and
cancellation of the ippc permits (zin., 2007, nr. 106-4358)'


So the data differences does not show up until the last three columns
which
look like this...


[RegID] [LanguageSort] [ReferenceDate]
21401 Lithuanian 10/09/2007
21401 AAAA 10/09/2007
21404 AAAA 10/14/2007
21404 Lithuanian 10/14/2007

For RegID 21401 the sort order for LanguageSort is incorrect; however the
sort order for RegID 21404 is correct.

We were expecting the last three columns of the sort result to look like
this...

[RegID] [LanguageSort] [ReferenceDate]
21401 AAAA 10/09/2007
21401 Lithuanian 10/09/2007
21404 AAAA 10/14/2007
21404 Lithuanian 10/14/2007


Does that help?

Kathy
Lord Kelvan said:
you have to remember the order your sort is in the order it will sort
the columns

sort 1
[regulation parents].HeadingName,
sort 2
[regulation parents].RegulationType,
sort 3
[pseudo countries].[Region name],
sort 4
[regulation parents].SortTitle,
sort 5
[regulation parents].RegID,
sort 6
[regulation parents].LanguageSort,
sort 7
[regulation parents].ReferenceDate DESC;

because reg type is sort 2 10 is less than 11 so your sort is correct

if you chaneg the order of the roder by you can set it up to sort how
you want it to appear


ORDER BY [regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC,
[regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle;

what the list of order by the sort order is now different and it
should give you the results you want

when it comes down to it do you need to sort by all thoes columns or
just the three because the mroe you sort by the slower your query is

ORDER BY [regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

if you only need the three then only sort by the three but it is up to
you

Regards
Kelvan
Click to expand...
Click to expand...
 
look you problem is the sort and the order the sql order by is in will
be the order you sort it on

these are your columns
HeadingName,RegulationType,RegionName,SortTItle,RegId,LanguageSort,ReferenceDate

this is an example of your data

toto 11 9 aa 11 Lithuanian 10/09/2007
toto 10 11 yo 16 aaaa 10/09/2007
toto 10 11 yo 16 Lithuanian 10/14/2007
toto 11 9 aa 11 aaaa 10/14/2007

this is your sort

ORDER BY [regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle,
[regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC;

so it sorts by the heading name first

since all heading names are toto there is no problem

then reg type
you have two values 10 and 11

so is sorts them


toto 10 11 yo 16 aaaa 10/09/2007
toto 11 9 aa 11 Lithuanian 10/09/2007
toto 10 11 yo 16 Lithuanian 10/14/2007
toto 11 9 aa 11 aaaa 10/14/2007

and then the rest of the sorts

remember each sort further down in the list only sorts the next group
of sort values so when it gets to sort by language sort it does
nothing because there is no more groups to sort by the language sort
cannot overwrite the sorts from pervious

you have to change the order of your order by command or it will not
work

I POSTED THIS BEFORE (i am putting it in caps so you see it this time)

ORDER BY [regulation parents].RegID,
[regulation parents].LanguageSort,
[regulation parents].ReferenceDate DESC,
[regulation parents].HeadingName,
[regulation parents].RegulationType,
[pseudo countries].[Region name],
[regulation parents].SortTitle;

REPLACE YOUR ORDER BY WITH THIS ORDER BY AND IT SHOULD SORT HOW YOU
WANT IT

i do hope you have see this this time and try it

regards
Kelvan
 
Back
Top