IT Employment

Ask something
Discussion Locked

Access 97 Database

0 helpful
1 reply

by irvin spurs

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Need some help; I have created an Access database that I use to calculate numbers, I also have it to group certain fields and give me a montly total and a Grand total for year to date. Access keep adding the numbers wrong. here is a copy of the SQL (SELECT Count([Class Schedule].CourseTitle) AS CountOfCourseTitle, [Class Schedule].[Course ID], [Class Schedule].[Date Completed], Sum([Class Schedule].Hrs) AS SumOfHrs, [Class Schedule].[DIRECTOR NAME], [Class Schedule].[DIRECTOR'S RC], [Class Schedule].[Completion Status]
FROM [Class Schedule]
GROUP BY [Class Schedule].[Course ID], [Class Schedule].[Date Completed], [Class Schedule].[DIRECTOR NAME], [Class Schedule].[DIRECTOR'S RC], [Class Schedule].[Completion Status]
HAVING (((Count([Class Schedule].CourseTitle)) Not Like "(cbt)*") AND (([Class Schedule].[Course ID]) Like "yy*" Or ([Class Schedule].[Course ID]) Like "ns2*" Or ([Class Schedule].[Course ID]) Like "zz*") AND (([Class Schedule].[Date Completed])>#1/1/02#) AND (([ClassSchedule].[Completion Status])="completed" Or ([Class Schedule].[Completion Status])="pass")) OR (((Count([Class Schedule].CourseTitle)) Not Like "(cbt)*") AND (([Class Schedule].[Course ID])="nt2222d" Or ([Class Schedule].[Course ID]) Like "NS0*" Or ([Class Schedule].[Course ID]) Like "di2" Or ([Class Schedule].[Course ID]) Like "na*") AND (([Class Schedule].[Date Completed])>#1/1/02#) AND (([Class Schedule].[Completion Status])="completed" Or ([Class Schedule].[Completion Status])="pass"));

1 reply

by kdogg

SELECT Count([Class Schedule].CourseTitle) AS CountOfCourseTitle, [Class Schedule].[Course ID] AS Expr1, [Class Schedule].[Date Completed] AS Expr2, Sum(Sum([Class Schedule].Hrs)) AS SumOfHrs, [Class Schedule].[DIRECTOR NAME] AS Expr3, [Class Schedule].[DIRECTOR'S RC] AS Expr4, [Class Schedule].[Completion Status] AS Expr5
FROM [Class Schedule]
GROUP BY [Class Schedule].[Course ID], [Class Schedule].[Date Completed], [Class Schedule].[DIRECTOR NAME], [Class Schedule].[DIRECTOR'S RC], [Class Schedule].[Completion Status]
HAVING ((([Class Schedule].[Course ID]) Like "yy*" Or ([Class Schedule].[Course ID]) Like "ns2*" Or ([Class Schedule].[Course ID]) Like "zz*") AND (([Class Schedule].[Date Completed])>#1/1/2002#) AND (([Class Schedule].[Completion Status])="completed" Or ([Class Schedule].[Completion Status])="pass") AND ((Count([Class Schedule].[CourseTitle])) Not Like "(cbt)*")) OR ((([Class Schedule].[Course ID])="nt2222d" Or ([Class Schedule].[Course ID]) Like "NS0*" Or ([Class Schedule].[Course ID]) Like "di2" Or ([Class Schedule].[Course ID]) Like "na*") AND (([Class Schedule].[Date Completed])>#1/1/2002#) AND (([Class Schedule].[Completion Status])="completed" Or ([Class Schedule].[Completion Status])="pass") AND ((Count([Class Schedule].[CourseTitle])) Not Like "(cbt)*"));

which field does your query sum incorrectly?

This discussion is closed to new replies.