report not following the query

[JohnE]

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

 

[Duane Hookom]

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.

 

[JohnE]

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