Summing Check Boxes

  • Thread starter Thread starter GoBrowns!
  • Start date Start date
G

GoBrowns!

Can someone please check this over?

I am trying to create a query to sum some fields and some check boxes.... I
keep getting an error that I am missing an operator... please help!

Here is my code:

SELECT([EmployeeID] as EmployeeID,
[EmployeeName] as EmployeeName,
Sum([VacationTimeUsed]) as VacationTimeUsed,
Sum([SickTimeUsed]) as SickTimeUsed,
Sum(Abs([Tardy])) as CountTardy,
Sum(Abs([MissedPunch])) as CountMissedPunch,
Sum(abs([Occurrence])) as CountOccurrence,
FROM tblAttendanceRecords
WHERE
GROUP BY([EmployeeID])
ORDER BY([EmployeeName]);

Thanks!
 
You have a beginning parentheses right after the SELECT
keyword with no corresponding closing parentheses. Also,
you have no aggregate function for the EmployeeName field
and a WHERE keyword with no criteria, plus an axtra comma
before the FROM keyword.

Try this;

SELECT [EmployeeID] as EmployeeID,
[EmployeeName] as EmployeeName,
Sum([VacationTimeUsed]) as VacationTimeUsed,
Sum([SickTimeUsed]) as SickTimeUsed,
Sum(Abs([Tardy])) as CountTardy,
Sum(Abs([MissedPunch])) as CountMissedPunch,
Sum(Abs([Occurrence])) as CountOccurrence
FROM tblAttendanceRecords
GROUP BY [EmployeeID], [EmployeeName]
ORDER BY [EmployeeName];
 
When I pasted your code in, I got a few errors:

EmployeeID in the SELECT statement caused a circular reference?
Also, I am missing a punctuation somewhere or using a "forbidden" word?

Any thoughts?!?

Thanks!!!!!!!!!!!

Beetle said:
You have a beginning parentheses right after the SELECT
keyword with no corresponding closing parentheses. Also,
you have no aggregate function for the EmployeeName field
and a WHERE keyword with no criteria, plus an axtra comma
before the FROM keyword.

Try this;

SELECT [EmployeeID] as EmployeeID,
[EmployeeName] as EmployeeName,
Sum([VacationTimeUsed]) as VacationTimeUsed,
Sum([SickTimeUsed]) as SickTimeUsed,
Sum(Abs([Tardy])) as CountTardy,
Sum(Abs([MissedPunch])) as CountMissedPunch,
Sum(Abs([Occurrence])) as CountOccurrence
FROM tblAttendanceRecords
GROUP BY [EmployeeID], [EmployeeName]
ORDER BY [EmployeeName];

--
_________

Sean Bailey


GoBrowns! said:
Can someone please check this over?

I am trying to create a query to sum some fields and some check boxes.... I
keep getting an error that I am missing an operator... please help!

Here is my code:

SELECT([EmployeeID] as EmployeeID,
[EmployeeName] as EmployeeName,
Sum([VacationTimeUsed]) as VacationTimeUsed,
Sum([SickTimeUsed]) as SickTimeUsed,
Sum(Abs([Tardy])) as CountTardy,
Sum(Abs([MissedPunch])) as CountMissedPunch,
Sum(abs([Occurrence])) as CountOccurrence,
FROM tblAttendanceRecords
WHERE
GROUP BY([EmployeeID])
ORDER BY([EmployeeName]);

Thanks!
Click to expand...
Click to expand...
 
Use this:

SELECT [EmployeeID],
[EmployeeName],
Sum([VacationTimeUsed]) as SumVacationTime,
Sum([SickTimeUsed]) as SumSickTimeUsed,
Sum(Abs([Tardy])) as CountTardy,
Sum(Abs([MissedPunch])) as CountMissedPunch,
Sum(Abs([Occurrence])) as CountOccurrence
FROM tblAttendanceRecords
GROUP BY [EmployeeID], [EmployeeName]
ORDER BY [EmployeeName]

OR normally you can also do it this way where you reference the field with the
table and field names and then the alias can be the same as the field name:

SELECT tblAttendanceRecords.[EmployeeID] as EmployeeID,
tblAttendanceRecords.[EmployeeName] as EmployeeName,
Sum(tblAttendanceRecords.[VacationTimeUsed]) as VacationTimeUsed,
Sum(tblAttendanceRecords.[SickTimeUsed]) as SickTimeUsed,
Sum(Abs([Tardy])) as CountTardy,
Sum(Abs([MissedPunch])) as CountMissedPunch,
Sum(Abs([Occurrence])) as CountOccurrence
FROM tblAttendanceRecords
GROUP BY tblAttendanceRecords.[EmployeeID], tblAttendanceRecords.[EmployeeName]
ORDER BY tblAttendanceRecords.[EmployeeName]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top