Query wont sort ?

  • Thread starter Thread starter Dorian
  • Start date Start date
D

Dorian

Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge<30,'X','') AS [Age <30 Days], IIf(P.PayAge>=30 And
P.PayAge<=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge>90,'X','') AS [Age >90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStreet & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))>0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Do you get an error message? Does it sort in the wrong order?

Is this the source for a report and the records in the report are not sorted?

If that is the case, a query sort has no direct relationship to how the
records are sorted for a report. Open the report in design view and select
View: Sorting and grouping and set up the sort for the report using the dialog.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Any idea why this wont sort by Lastname ?
Click to expand...

What's it doing instead? Are you looking at the sort order on a Report? If so,
you need to specify the sort in the Report's Sorting and Grouping dialog - it
ignores the sort order of the Recordsource.

If you open the query as a query datasheet, what does it give you?
 
No report involved, am just opening query in database window and see results
in datasheet. Order appears random.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


John Spencer said:
Do you get an error message? Does it sort in the wrong order?

Is this the source for a report and the records in the report are not sorted?

If that is the case, a query sort has no direct relationship to how the
records are sorted for a report. Open the report in design view and select
View: Sorting and grouping and set up the sort for the report using the dialog.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge<30,'X','') AS [Age <30 Days], IIf(P.PayAge>=30 And
P.PayAge<=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge>90,'X','') AS [Age >90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStreet & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))>0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
Click to expand...
.
Click to expand...
 
No report involved, am just opening query in database window and see results
in datasheet. Order appears random.
This is Access 2003.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Technically, it is ok to ORDER on a field we don't SELECT, but what happens
if you add that field:

SELECT C.Lastname, C.Restitution-NZ...
....
ORDER BY C.Lastname


Also note that ' and - near the begining of a string are consider invisible
character as sorting is involved (from a Win32 based convention)

Xfile
X-files
Xfilet


is correctly ordered ascendingly.



Vanderghast, Access MVP


Dorian said:
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname
&
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge said:
90
Click to expand...
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStreet & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's
Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))>0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and
they
eat for a lifetime".
Click to expand...
 
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge<30,'X','') AS [Age <30 Days], IIf(P.PayAge>=30 And
P.PayAge<=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge>90,'X','') AS [Age >90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStreet & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))>0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
Click to expand...

It may be a corrupt Index on LastName. Try opening the table [Beth AFirms] in
design view and select the Lastname field. Does it have an index?

Or... <shudder>... did you make it a Lookup field?? That would sort by the ID,
not by the text name.
 
My best guess at this point -
[Beth AFirms].LastName is a LOOKUP field in table design. If it is then you
are sorting by a number that indicates a record in another related table that
contains the actual data.

If you have used the Lookup field, the way to fix the query is to include the
table that contains the actual data in your query and sort by the field in the
table with the actual data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
No report involved, am just opening query in database window and see results
in datasheet. Order appears random.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


John Spencer said:
Do you get an error message? Does it sort in the wrong order?

Is this the source for a report and the records in the report are not sorted?

If that is the case, a query sort has no direct relationship to how the
records are sorted for a report. Open the report in design view and select
View: Sorting and grouping and set up the sort for the report using the dialog.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge<30,'X','') AS [Age <30 Days], IIf(P.PayAge>=30 And
P.PayAge<=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge>90,'X','') AS [Age >90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStreet & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))>0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
Click to expand...
.
Click to expand...
Click to expand...
 
No lookup field involved. Lastname is Text(50).
Tried all suggestions. None worked.
However, created a new query and copied in SQL text and ran it.
It worked.
So same SQL text in two different queries gives different results !
Something messed up the original query.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


John Spencer said:
My best guess at this point -
[Beth AFirms].LastName is a LOOKUP field in table design. If it is then you
are sorting by a number that indicates a record in another related table that
contains the actual data.

If you have used the Lookup field, the way to fix the query is to include the
table that contains the actual data in your query and sort by the field in the
table with the actual data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
No report involved, am just opening query in database window and see results
in datasheet. Order appears random.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


John Spencer said:
Do you get an error message? Does it sort in the wrong order?

Is this the source for a report and the records in the report are not sorted?

If that is the case, a query sort has no direct relationship to how the
records are sorted for a report. Open the report in design view and select
View: Sorting and grouping and set up the sort for the report using the dialog.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Dorian wrote:
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge<30,'X','') AS [Age <30 Days], IIf(P.PayAge>=30 And
P.PayAge<=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge>90,'X','') AS [Age >90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStreet & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))>0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
.
Click to expand...
Click to expand...
.
Click to expand...
 
No lookup field involved. Lastname is Text(50), no index on it.
Tried all suggestions. Nothing worked.
However, created a new query and copied in SQL text and ran it.
It worked.
So same SQL text in two different queries gives different results !
Something messed up the original query.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


John W. Vinson said:
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge<30,'X','') AS [Age <30 Days], IIf(P.PayAge>=30 And
P.PayAge<=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge>90,'X','') AS [Age >90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStreet & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))>0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
Click to expand...

It may be a corrupt Index on LastName. Try opening the table [Beth AFirms] in
design view and select the Lastname field. Does it have an index?

Or... <shudder>... did you make it a Lookup field?? That would sort by the ID,
not by the text name.
Click to expand...
 
Tried your suggestion. Made no difference.
However, created a new query and copied in SQL text and ran it.
It worked.
So same SQL text in two different queries gives different results !
Something messed up the original query.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


vanderghast said:
Technically, it is ok to ORDER on a field we don't SELECT, but what happens
if you add that field:

SELECT C.Lastname, C.Restitution-NZ...
...
ORDER BY C.Lastname


Also note that ' and - near the begining of a string are consider invisible
character as sorting is involved (from a Win32 based convention)

Xfile
X-files
Xfilet


is correctly ordered ascendingly.



Vanderghast, Access MVP


Dorian said:
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname
&
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge said:
90
Click to expand...
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStreet & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's
Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))>0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and
they
eat for a lifetime".
Click to expand...
Click to expand...
 
Back
Top