RS query: duplicates in the detail section
-
Hi,
I am working on a student transportation report and encountered a problem i cannot seem to figure out. I copy the query below into the RS designer.
SELECT S.LastName,
S.FirstName,
S.StudentID,
RD.ArrivalTime,
D.StopDescription,
B.VehicleNumber,
ST.TransportType,
SC.SchoolName,
U.UserUID
FROM DWR_table_BusInfo B
INNER JOIN DWR_table_BusRouteInfo RI ON B.RefId = RI.BusInfoRefId
INNER JOIN DWR_table_BusRouteDetail RD ON RD.BusRouteInfoRefId = RI.RefId INNER JOIN DWR_table_StudentTransportInfo ST ON ST.SchoolBusRouteDetailRefId = RD.RefId
INNER JOIN DWR_table_BusStopInfo D ON RD.BusStopInfoRefId = D.RefId
INNER JOIN DWR_table_StudentPersonal S ON S.RefId = ST.StudentPersonalRefId
INNER JOIN DWR_table_StudentSchoolEnrollment E ON E.StudentPersonalRefId = S.RefId
INNER JOIN DWR_table_SchoolInfo SC ON E.SchoolInfoRefId = SC.RefId
INNER JOIN DWR_table_SchoolUser U ON SC.BuildingNumber = U.SchoolID
WHERE U.UserUID = @user-id ORDER BY S.LastName, RD.ArrivalTime
I choose the following groupings in the report wizard:
Page - SchoolName
Group - Last Name, TransportType (Inbound or outbound)
Detail - StopDescription, ArrivalTime, VehicleNumber
As a result I get some duplicate records in the detail section of the report. However, when I run the query above in the Query Analyser I get no duplicates. What could possibly cause the duplicates? I am lost. Any ideas you may have would be helpful.
Thanks a lot!
-
Are you sure there are no duplicates in Query Analyzer? Change your query to perform the same grouping operation the RS is doing.
--------------------
Colt 45 - the original point and click interface
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply