Right records on a query but doubling up when summing them

C

cranberryconsult

Hi,

I have a query that brings back data from two tables joined together on
StudentID. The data is absolutely beautiful as long as I'm bringing
back all the columns as "group by". However, when I change the hours
column to "sum", the SumOfHours is double what it is when the data is
just brought back line by line. I can't figure out how to get the sum
to just sum the hours once. Please let me know if there's something
you can point me to.

Thanks,


SELECT [qryStudentHour Details].ProgramName, [qryStudentHour
Details].StudentID, [qryStudentHour Details].StudName, [qryStudentHour
Details].LSID, [qryStudentHour Details].SSN, [qryStudentHour
Details].Gender, [qryStudentHour Details].Birthdate, [qryStudentHour
Details].EthnicityId, [qryStudentHour Details].Address, [qryStudentHour
Details].City, [qryStudentHour Details].State, [qryStudentHour
Details].Zip, [qryStudentHour Details].HomePhone, [qryStudentHour
Details].WorkPhone, [qryStudentHour Details].Email, Sum([qryStudentHour
Details].Hours) AS SumOfHours, [qryStudentHour Details].EventType,
[qryStudentHour Details].EventDate, [qryStudentHour
Details].EmploymentStatus, [qryStudentHour Details].Immigration,
[qryStudentHour Details].LearningDisability, [qryStudentHour
Details].PhysicalDisability, [qryStudentHour Details].WorkFirstTANF,
[qryStudentHour Details].FoodStamps, qryStudentTestsAll.TestDate,
qryStudentTestsAll.TestType
FROM qryStudentTestsAll INNER JOIN [qryStudentHour Details] ON
qryStudentTestsAll.StudentID = [qryStudentHour Details].StudentID
WHERE
(((IIf(IsNull([forms]![frmReportCreation]![Program]),(([tblPrograms-Student].[ProgramName])
Like "*"),(([tblPrograms-Student].[ProgramName]) Like
[forms]![frmReportCreation]![Program])))<>False) AND (([qryStudentHour
Details].FromDate) Is Not Null And ([qryStudentHour
Details].FromDate)>=[forms]![frmReportCreation]![BeginningDate]) AND
(([qryStudentHour Details].ToDate) Is Not Null And ([qryStudentHour
Details].ToDate)<=[forms]![frmReportCreation]![EndingDate]))
GROUP BY [qryStudentHour Details].ProgramName, [qryStudentHour
Details].StudentID, [qryStudentHour Details].StudName, [qryStudentHour
Details].LSID, [qryStudentHour Details].SSN, [qryStudentHour
Details].Gender, [qryStudentHour Details].Birthdate, [qryStudentHour
Details].EthnicityId, [qryStudentHour Details].Address, [qryStudentHour
Details].City, [qryStudentHour Details].State, [qryStudentHour
Details].Zip, [qryStudentHour Details].HomePhone, [qryStudentHour
Details].WorkPhone, [qryStudentHour Details].Email, [qryStudentHour
Details].EventType, [qryStudentHour Details].EventDate, [qryStudentHour
Details].EmploymentStatus, [qryStudentHour Details].Immigration,
[qryStudentHour Details].LearningDisability, [qryStudentHour
Details].PhysicalDisability, [qryStudentHour Details].WorkFirstTANF,
[qryStudentHour Details].FoodStamps, qryStudentTestsAll.TestDate,
qryStudentTestsAll.TestType
HAVING
(((qryStudentTestsAll.TestDate)>=[forms]![frmReportCreation]![BeginningDate])
AND ((qryStudentTestsAll.TestType) Like "*Post*") AND
((IIf(IsNull([forms]![frmReportCreation]![Student]),(([qryStudentHour
Details].[StudentID]) Like "*"),(([qryStudentHour Details].[StudentID])
Like [forms]![frmReportCreation]![Student])))<>False))
ORDER BY [qryStudentHour Details].StudName;
 
A

Allen Browne

You will find that each record is present twice for some reason.

For example, there may be a related table that has 2 records for the record
in the main table, so that each main table record appears in 2 rows of the
query.

If you are convinced that the problem only arises when you change the Total
row under your Hours from Group By to Sum, leave it as Group By, and add the
field again using Sum on the second instance.

On a side note, if you don't actually need all those fields in the GROUP BY
clause, you can choose the ones you do need to group by, and change the
others to First.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that brings back data from two tables joined together on
StudentID. The data is absolutely beautiful as long as I'm bringing
back all the columns as "group by". However, when I change the hours
column to "sum", the SumOfHours is double what it is when the data is
just brought back line by line. I can't figure out how to get the sum
to just sum the hours once. Please let me know if there's something
you can point me to.

Thanks,


SELECT [qryStudentHour Details].ProgramName, [qryStudentHour
Details].StudentID, [qryStudentHour Details].StudName, [qryStudentHour
Details].LSID, [qryStudentHour Details].SSN, [qryStudentHour
Details].Gender, [qryStudentHour Details].Birthdate, [qryStudentHour
Details].EthnicityId, [qryStudentHour Details].Address, [qryStudentHour
Details].City, [qryStudentHour Details].State, [qryStudentHour
Details].Zip, [qryStudentHour Details].HomePhone, [qryStudentHour
Details].WorkPhone, [qryStudentHour Details].Email, Sum([qryStudentHour
Details].Hours) AS SumOfHours, [qryStudentHour Details].EventType,
[qryStudentHour Details].EventDate, [qryStudentHour
Details].EmploymentStatus, [qryStudentHour Details].Immigration,
[qryStudentHour Details].LearningDisability, [qryStudentHour
Details].PhysicalDisability, [qryStudentHour Details].WorkFirstTANF,
[qryStudentHour Details].FoodStamps, qryStudentTestsAll.TestDate,
qryStudentTestsAll.TestType
FROM qryStudentTestsAll INNER JOIN [qryStudentHour Details] ON
qryStudentTestsAll.StudentID = [qryStudentHour Details].StudentID
WHERE
(((IIf(IsNull([forms]![frmReportCreation]![Program]),(([tblPrograms-Student].[ProgramName])
Like "*"),(([tblPrograms-Student].[ProgramName]) Like
[forms]![frmReportCreation]![Program])))<>False) AND (([qryStudentHour
Details].FromDate) Is Not Null And ([qryStudentHour
Details].FromDate)>=[forms]![frmReportCreation]![BeginningDate]) AND
(([qryStudentHour Details].ToDate) Is Not Null And ([qryStudentHour
Details].ToDate)<=[forms]![frmReportCreation]![EndingDate]))
GROUP BY [qryStudentHour Details].ProgramName, [qryStudentHour
Details].StudentID, [qryStudentHour Details].StudName, [qryStudentHour
Details].LSID, [qryStudentHour Details].SSN, [qryStudentHour
Details].Gender, [qryStudentHour Details].Birthdate, [qryStudentHour
Details].EthnicityId, [qryStudentHour Details].Address, [qryStudentHour
Details].City, [qryStudentHour Details].State, [qryStudentHour
Details].Zip, [qryStudentHour Details].HomePhone, [qryStudentHour
Details].WorkPhone, [qryStudentHour Details].Email, [qryStudentHour
Details].EventType, [qryStudentHour Details].EventDate, [qryStudentHour
Details].EmploymentStatus, [qryStudentHour Details].Immigration,
[qryStudentHour Details].LearningDisability, [qryStudentHour
Details].PhysicalDisability, [qryStudentHour Details].WorkFirstTANF,
[qryStudentHour Details].FoodStamps, qryStudentTestsAll.TestDate,
qryStudentTestsAll.TestType
HAVING
(((qryStudentTestsAll.TestDate)>=[forms]![frmReportCreation]![BeginningDate])
AND ((qryStudentTestsAll.TestType) Like "*Post*") AND
((IIf(IsNull([forms]![frmReportCreation]![Student]),(([qryStudentHour
Details].[StudentID]) Like "*"),(([qryStudentHour Details].[StudentID])
Like [forms]![frmReportCreation]![Student])))<>False))
ORDER BY [qryStudentHour Details].StudName;
Click to expand...
 
J

John Vinson

Hi,

I have a query that brings back data from two tables joined together on
StudentID. The data is absolutely beautiful as long as I'm bringing
back all the columns as "group by". However, when I change the hours
column to "sum", the SumOfHours is double what it is when the data is
just brought back line by line. I can't figure out how to get the sum
to just sum the hours once. Please let me know if there's something
you can point me to.
Click to expand...

My guess is that the Join

FROM qryStudentTestsAll INNER JOIN [qryStudentHour Details] ON

is at fault. If either query has two records for a given studentID,
that student's data will be incorporated twice. You may need to change
your GroupBy or use a DISTINCT clause, depending on the nature of your
data.

John W. Vinson[MVP]
 
Top