Right records on a query but doubling up when summing them

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cranberryconsult@earthlink.net - 20 Dec 2006 00:29 GMT

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;

Reply to this Message

Allen Browne - 20 Dec 2006 01:27 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
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
[quoted text clipped - 52 lines]
> Like [forms]![frmReportCreation]![Student])))<>False))
> ORDER BY [qryStudentHour Details].StudName;

Reply to this Message

John Vinson - 20 Dec 2006 01:57 GMT

>Hi,
>
[quoted text clipped - 5 lines]
>to just sum the hours once. Please let me know if there's something
>you can point me to.

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]

Reply to this Message