If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
report not following the query
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 |
#3
|
|||
|
|||
report not following the query
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 |
#4
|
|||
|
|||
report not following the query
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 |
Thread Tools | |
Display Modes | |
|
|