report not following the query

I have a report that is not producing the correct order of the information
that the underlying query indicates. Running just the query has the correct
order, but report does not. Below is the sql from the query that is used in
the report.

SELECT tblChangeRequest.DevTargetEndDate, tblChangeRequest.ChangeRequestID,
tblChangeRequest.ChangeRequest, tblProjectLead.ProjectLead,
tblSPGName.SPGName, tblChangeRequestStatus.StatusReportOrder,
tblChangeRequestStatus.ChangeRequestStatus
FROM (((tblChangeRequest LEFT JOIN tblProjectLead ON
tblChangeRequest.ProjectLeadID = tblProjectLead.ProjectLeadID) LEFT JOIN
tblDevAssignedTo ON tblChangeRequest.DevAssignedToID =
tblDevAssignedTo.DevAssignedToID) LEFT JOIN tblSPGName ON
tblChangeRequest.SPGNameID = tblSPGName.SPGNameID) RIGHT JOIN
tblChangeRequestStatus ON tblChangeRequest.ChangeRequestStatusID =
tblChangeRequestStatus.ChangeRequestStatusID
WHERE (((tblChangeRequestStatus.StatusReportOrder)0))
ORDER BY tblChangeRequestStatus.StatusReportOrder;

I have added group by but that upset the ChangeRequest field. Even took the
query out and did the query in the report's recordsource. The report has a
grouping header done by the Status field. Want to show the items that fall
into the different statuses. I have a column in the status table that
indicates the order the statuses are to appear on the report. That is the
Order By above.

This is puzzling. Can anyone see or know why the report does not match the
query?

Thanks... John

Don't ever count on the order in the query to pass through to the report. Use
the Sorting and Grouping dialog in the report design to set all sorting.

--
Duane Hookom
Microsoft Access MVP


"JohnE" wrote:

I have a report that is not producing the correct order of the information
that the underlying query indicates. Running just the query has the correct
order, but report does not. Below is the sql from the query that is used in
the report.

SELECT tblChangeRequest.DevTargetEndDate, tblChangeRequest.ChangeRequestID,
tblChangeRequest.ChangeRequest, tblProjectLead.ProjectLead,
tblSPGName.SPGName, tblChangeRequestStatus.StatusReportOrder,
tblChangeRequestStatus.ChangeRequestStatus
FROM (((tblChangeRequest LEFT JOIN tblProjectLead ON
tblChangeRequest.ProjectLeadID = tblProjectLead.ProjectLeadID) LEFT JOIN
tblDevAssignedTo ON tblChangeRequest.DevAssignedToID =
tblDevAssignedTo.DevAssignedToID) LEFT JOIN tblSPGName ON
tblChangeRequest.SPGNameID = tblSPGName.SPGNameID) RIGHT JOIN
tblChangeRequestStatus ON tblChangeRequest.ChangeRequestStatusID =
tblChangeRequestStatus.ChangeRequestStatusID
WHERE (((tblChangeRequestStatus.StatusReportOrder)0))
ORDER BY tblChangeRequestStatus.StatusReportOrder;

I have added group by but that upset the ChangeRequest field. Even took the
query out and did the query in the report's recordsource. The report has a
grouping header done by the Status field. Want to show the items that fall
into the different statuses. I have a column in the status table that
indicates the order the statuses are to appear on the report. That is the
Order By above.

This is puzzling. Can anyone see or know why the report does not match the
query?

Thanks... John

A report will generally ignore the sort order of the underlying query, so
should be sorted by means of its internal sorting and grouping mechanism.
Change the group level from the Status field to the StatusReportOrder field.
The report will then be grouped by Status but by the value in the
StatusReportOrder field not the value in the Status field.

Ken Sheridan
Stafford, England

JohnE wrote:
I have a report that is not producing the correct order of the information
that the underlying query indicates. Running just the query has the correct
order, but report does not. Below is the sql from the query that is used in
the report.

SELECT tblChangeRequest.DevTargetEndDate, tblChangeRequest.ChangeRequestID,
tblChangeRequest.ChangeRequest, tblProjectLead.ProjectLead,
tblSPGName.SPGName, tblChangeRequestStatus.StatusReportOrder,
tblChangeRequestStatus.ChangeRequestStatus
FROM (((tblChangeRequest LEFT JOIN tblProjectLead ON
tblChangeRequest.ProjectLeadID = tblProjectLead.ProjectLeadID) LEFT JOIN
tblDevAssignedTo ON tblChangeRequest.DevAssignedToID =
tblDevAssignedTo.DevAssignedToID) LEFT JOIN tblSPGName ON
tblChangeRequest.SPGNameID = tblSPGName.SPGNameID) RIGHT JOIN
tblChangeRequestStatus ON tblChangeRequest.ChangeRequestStatusID =
tblChangeRequestStatus.ChangeRequestStatusID
WHERE (((tblChangeRequestStatus.StatusReportOrder)0))
ORDER BY tblChangeRequestStatus.StatusReportOrder;

I have added group by but that upset the ChangeRequest field. Even took the
query out and did the query in the report's recordsource. The report has a
grouping header done by the Status field. Want to show the items that fall
into the different statuses. I have a column in the status table that
indicates the order the statuses are to appear on the report. That is the
Order By above.

This is puzzling. Can anyone see or know why the report does not match the
query?

Thanks... John

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201004/1

Thanks for the response. I just figured that out and had to do some creative
grouping as well.
.... John


"Duane Hookom" wrote:

Don't ever count on the order in the query to pass through to the report. Use
the Sorting and Grouping dialog in the report design to set all sorting.

--
Duane Hookom
Microsoft Access MVP


"JohnE" wrote:

I have a report that is not producing the correct order of the information
that the underlying query indicates. Running just the query has the correct
order, but report does not. Below is the sql from the query that is used in
the report.

SELECT tblChangeRequest.DevTargetEndDate, tblChangeRequest.ChangeRequestID,
tblChangeRequest.ChangeRequest, tblProjectLead.ProjectLead,
tblSPGName.SPGName, tblChangeRequestStatus.StatusReportOrder,
tblChangeRequestStatus.ChangeRequestStatus
FROM (((tblChangeRequest LEFT JOIN tblProjectLead ON
tblChangeRequest.ProjectLeadID = tblProjectLead.ProjectLeadID) LEFT JOIN
tblDevAssignedTo ON tblChangeRequest.DevAssignedToID =
tblDevAssignedTo.DevAssignedToID) LEFT JOIN tblSPGName ON
tblChangeRequest.SPGNameID = tblSPGName.SPGNameID) RIGHT JOIN
tblChangeRequestStatus ON tblChangeRequest.ChangeRequestStatusID =
tblChangeRequestStatus.ChangeRequestStatusID
WHERE (((tblChangeRequestStatus.StatusReportOrder)0))
ORDER BY tblChangeRequestStatus.StatusReportOrder;

I have added group by but that upset the ChangeRequest field. Even took the
query out and did the query in the report's recordsource. The report has a
grouping header done by the Status field. Want to show the items that fall
into the different statuses. I have a column in the status table that
indicates the order the statuses are to appear on the report. That is the
Order By above.

This is puzzling. Can anyone see or know why the report does not match the
query?

Thanks... John