Report Sorting

[Guest]

I have a report which sorts by Month, Location City and then Service Call
Number.

For the Service Call Number there is a date field. I have an ID field for
each record that may be R350 or 450 for example. The R is to denote
different forms to use.

What do I need to do so that when sorting I can get all the "R" numbers
sorted before all the non "R" numbers and also be able to sort accending for
the date field that is shown for each record entry?

With city sorting, R numbers and due date field I can't seem to sort ID
field and then sort Due date field. I can do one but not the other. If I
get R numbers on top the R Numbers are not sorted by date but by the number

Confusing or make sense?

 

[John Vinson]

I have a report which sorts by Month, Location City and then Service Call
Number.

For the Service Call Number there is a date field. I have an ID field for
each record that may be R350 or 450 for example. The R is to denote
different forms to use.

What do I need to do so that when sorting I can get all the "R" numbers
sorted before all the non "R" numbers and also be able to sort accending for
the date field that is shown for each record entry?

With city sorting, R numbers and due date field I can't seem to sort ID
field and then sort Due date field. I can do one but not the other. If I
get R numbers on top the R Numbers are not sorted by date but by the number

Confusing or make sense?

So you want all of the R numbers sorted by date, ignoring the numeric
value of the R number; followed by all the non-R numbers, also sorted
by date?

If so, include a calculated field in the query:

IIF(Left([Service Call Number], 1) = "R", 1, 2)

and make this field the first in the sort order; you may need to join
the Service Call Table to the query in order to get the date field,
but make it next in the sort order.

John W. Vinson[MVP]

 

[Guest]

John, I finally got back to this to work on.

My issue is that I use a Union query to bring to queries together. One
query uses [ID]Field and the other query uses [Service Call number]. I then
join the results into the [Service Call Number].

I have tried what you suggested but I get EXPr and cannot get all records
into the one field with numbers. I get 2 for every service call number and
good R numbers for the ID.

Does this help explain what I need or what I need to do?

I have a report which sorts by Month, Location City and then Service Call
Number.

For the Service Call Number there is a date field. I have an ID field for
each record that may be R350 or 450 for example. The R is to denote
different forms to use.

What do I need to do so that when sorting I can get all the "R" numbers
sorted before all the non "R" numbers and also be able to sort accending for
the date field that is shown for each record entry?

With city sorting, R numbers and due date field I can't seem to sort ID
field and then sort Due date field. I can do one but not the other. If I
get R numbers on top the R Numbers are not sorted by date but by the number

Confusing or make sense?

So you want all of the R numbers sorted by date, ignoring the numeric
value of the R number; followed by all the non-R numbers, also sorted
by date?

If so, include a calculated field in the query:

IIF(Left([Service Call Number], 1) = "R", 1, 2)

and make this field the first in the sort order; you may need to join
the Service Call Table to the query in order to get the date field,
but make it next in the sort order.

John W. Vinson[MVP]

 

[John Vinson]

John, I finally got back to this to work on.

My issue is that I use a Union query to bring to queries together. One
query uses [ID]Field and the other query uses [Service Call number]. I then
join the results into the [Service Call Number].

I have tried what you suggested but I get EXPr and cannot get all records
into the one field with numbers. I get 2 for every service call number and
good R numbers for the ID.

Does this help explain what I need or what I need to do?

No, it doesn't. Please post the SQL of the query.

John W. Vinson[MVP]

 

[Guest]

John, here you go.

Select [SERVICE CALL NUMBER],[ACCOUNT NAME],[LOCATION CITY],[ASSIGNED
CONSULTANT],[COUNTOFASSIGNED CONSULTANT],[LOCATION SERVICING
DIVISION],[POLICY NUMBER],[RESCHEDULED SERVICE MONTH],[LOCATION
ADDRESS],[LOCATION STATE],[ORIGINAL SERVICE CALL DATE],[EXPIRATION
DATE],[FIRSTOFCOMMENTS],[EAP],[LOCATION CONTACT NAME/TITLE],[LOCATION
PHONE],[LOCATION EMAIL],[UNDERWRITER],[Countofscheduled Service
Month],[Scheduled Service Month],[Service Call Type]
From[Total Pending Service calls];

UNION ALL Select "R"& [ID],[ACCOUNT NAME],[LOCATION CITY],[ASSIGNED
CONSULTANT],[COUNTOFASSIGNED CONSULTANT],[LOCATION SERVICING
DIVISION],[Policy/App Number],[NEW DUE DATE],[Location Address],[Location
State],[ORIGINAL DATE],[Expiration Date],[Firstofcomments],[EAP],[Account
Contact],[Account Contact Phone],[Contact Email],[Requested By:],[Countofdue
date:],[Due Date:],[Type of Request]
From[Total Pending Request Calls];

John, I finally got back to this to work on.

My issue is that I use a Union query to bring to queries together. One
query uses [ID]Field and the other query uses [Service Call number]. I then
join the results into the [Service Call Number].

I have tried what you suggested but I get EXPr and cannot get all records
into the one field with numbers. I get 2 for every service call number and
good R numbers for the ID.

Does this help explain what I need or what I need to do?

No, it doesn't. Please post the SQL of the query.

John W. Vinson[MVP]

 

[John Vinson]

John, here you go.

Select [SERVICE CALL NUMBER],[ACCOUNT NAME],[LOCATION CITY],[ASSIGNED
CONSULTANT],[COUNTOFASSIGNED CONSULTANT],[LOCATION SERVICING
DIVISION],[POLICY NUMBER],[RESCHEDULED SERVICE MONTH],[LOCATION
ADDRESS],[LOCATION STATE],[ORIGINAL SERVICE CALL DATE],[EXPIRATION
DATE],[FIRSTOFCOMMENTS],[EAP],[LOCATION CONTACT NAME/TITLE],[LOCATION
PHONE],[LOCATION EMAIL],[UNDERWRITER],[Countofscheduled Service
Month],[Scheduled Service Month],[Service Call Type]
From[Total Pending Service calls];

UNION ALL Select "R"& [ID],[ACCOUNT NAME],[LOCATION CITY],[ASSIGNED
CONSULTANT],[COUNTOFASSIGNED CONSULTANT],[LOCATION SERVICING
DIVISION],[Policy/App Number],[NEW DUE DATE],[Location Address],[Location
State],[ORIGINAL DATE],[Expiration Date],[Firstofcomments],[EAP],[Account
Contact],[Account Contact Phone],[Contact Email],[Requested By:],[Countofdue
date:],[Due Date:],[Type of Request]
From[Total Pending Request Calls];

I'd add one more field: IsR, set to True for the second UNION, and to
false for the first. Then remember that in a UNION query the
fieldnames in the first SELECT clause in the union apply to all
records; use the Report's Sorting and Grouping to sort first by IsR,
and then by [Location Date]. Don't bother sorting the UNION query, the
Report's sorting and grouping will override it anyway.


John W. Vinson[MVP]

 

[Guest]

John this may be dumb, but how do write the field you gave in the query?
[IsR=True]

John, here you go.

Select [SERVICE CALL NUMBER],[ACCOUNT NAME],[LOCATION CITY],[ASSIGNED
CONSULTANT],[COUNTOFASSIGNED CONSULTANT],[LOCATION SERVICING
DIVISION],[POLICY NUMBER],[RESCHEDULED SERVICE MONTH],[LOCATION
ADDRESS],[LOCATION STATE],[ORIGINAL SERVICE CALL DATE],[EXPIRATION
DATE],[FIRSTOFCOMMENTS],[EAP],[LOCATION CONTACT NAME/TITLE],[LOCATION
PHONE],[LOCATION EMAIL],[UNDERWRITER],[Countofscheduled Service
Month],[Scheduled Service Month],[Service Call Type]
From[Total Pending Service calls];

UNION ALL Select "R"& [ID],[ACCOUNT NAME],[LOCATION CITY],[ASSIGNED
CONSULTANT],[COUNTOFASSIGNED CONSULTANT],[LOCATION SERVICING
DIVISION],[Policy/App Number],[NEW DUE DATE],[Location Address],[Location
State],[ORIGINAL DATE],[Expiration Date],[Firstofcomments],[EAP],[Account
Contact],[Account Contact Phone],[Contact Email],[Requested By:],[Countofdue
date:],[Due Date:],[Type of Request]
From[Total Pending Request Calls];

I'd add one more field: IsR, set to True for the second UNION, and to
false for the first. Then remember that in a UNION query the
fieldnames in the first SELECT clause in the union apply to all
records; use the Report's Sorting and Grouping to sort first by IsR,
and then by [Location Date]. Don't bother sorting the UNION query, the
Report's sorting and grouping will override it anyway.


John W. Vinson[MVP]

 

[John Vinson]

John this may be dumb, but how do write the field you gave in the query?
[IsR=True]

True As IsR


John W. Vinson[MVP]