Help with Sql statement

D

Duane

I would like to add a field from a third table and sum the field, but I am
having trouble with the syntax. I was wondering if someone could help me
with it?

The third table is called tblOvertime. It is joined to the Employee table
on the HRMN fields. I would like to SUM the Hours field in tblOvertime.

The first RecordSource (my starting point) works fine for my report.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & " _
& "[MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, NES,
& "Employee.TotHours, Employee.TotHours, " _
& "Employee.Group " _
& "FROM Employee " _
& "INNER JOIN Rot " _
& "ON Employee.Group = ROT.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"

This is the one that is messed up.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS
EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "_
& " AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"


I know this is probably written totally wrong, but I am trying to learn. I
am receiving an error for a reserved word, argument name misspelled, or the
punctuation is incorrect. I put more line continuations in here then what
is actually in my code, for readability purposes.

Any help would be greatly appreciated.

I previously posted this in the forum for MS Access Reports, but there
doesn't appear to be as much activity there as there is here.
 
P

pietlinden

I would like to add a field from a third table and sum the field, but I am
having trouble with the syntax.  I was wondering if someone could help me
with it?

The third table is called tblOvertime.  It is joined to the Employee table
on the HRMN fields.  I would like to SUM the Hours field in tblOvertime.

The first RecordSource (my starting point) works fine for my report.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
        & "[LastName] & ', ' & [FirstName] & ' ' & " _
        & "[MiddleInitial] AS EmpName, " _
        & "Employee.Telephone, Employee.Shift, NES,
        & "Employee.TotHours, Employee.TotHours, " _
        & "Employee.Group " _
        & "FROM Employee " _
        & "INNER JOIN Rot " _
        & "ON Employee.Group = ROT.Group " _
        & "WHERE ROT.[MOD" & intMod & "] = 1 " _
        & "AND  Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
        & "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"

This is the one that is messed up.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
       & "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS
EmpName, " _
       & "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
       & "Sum(tblOvertime.Hours) AS SumOfHours, " _
       & "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
       & "ON Employee.HRMN = tblOvertime.HRMN) ON " _
       & "Rot.Group = Employee.Group " _
       & "GROUP BY Employee.HRMN, Employee.Group, " _
       & "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial]," _
       & "Employee.Telephone, Employee.Shift, Employee.NES " _
       & "WHERE ROT.[MOD" & intMod & "] = 1 "_
       & " AND Employee.[" & Forms!frm_Switchboard!MyList & "] =-1 " _
       & "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"

I know this is probably written totally wrong, but I am trying to learn.  I
am receiving an error for a reserved word, argument name misspelled, or the
punctuation is incorrect.   I put more line continuations in here then what
is actually in my code, for readability purposes.

Any help would be greatly appreciated.

I previously posted this in the forum for MS Access Reports, but there
doesn't appear to be as much activity there as there is here.
Click to expand...

build the SQL statement in a string variable and then print the
result to the debug window.
strSQL="SELECT..."
strsql = strsql & " FROM..."

then...
Debug.print strSQL
copy that SQL statement to a new query (click the SQL button in the
QBE grid) and try executing the SQL statement. if it doesn't work
there, you have a syntax error.
 
K

Ken Sheridan

You are just missing a space before the continuation character in:

& "WHERE ROT.[MOD" & intMod & "] = 1 "_

and the WHERE clause should precede the GROUP BY clause.

Try:

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0 " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES"

Ken Sheridan
Stafford, England

Duane said:
I would like to add a field from a third table and sum the field, but I am
having trouble with the syntax. I was wondering if someone could help me
with it?

The third table is called tblOvertime. It is joined to the Employee table
on the HRMN fields. I would like to SUM the Hours field in tblOvertime.

The first RecordSource (my starting point) works fine for my report.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & " _
& "[MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, NES,
& "Employee.TotHours, Employee.TotHours, " _
& "Employee.Group " _
& "FROM Employee " _
& "INNER JOIN Rot " _
& "ON Employee.Group = ROT.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"

This is the one that is messed up.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS
EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "_
& " AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"


I know this is probably written totally wrong, but I am trying to learn. I
am receiving an error for a reserved word, argument name misspelled, or the
punctuation is incorrect. I put more line continuations in here then what
is actually in my code, for readability purposes.

Any help would be greatly appreciated.

I previously posted this in the forum for MS Access Reports, but there
doesn't appear to be as much activity there as there is here.
Click to expand...
 
D

Duane

Thanks for responding Ken. I am still receiving and error stating "The
SELECT Statement includes a reserved word or an argument name that is
misspelled or the punctuation is incorrect" Run-Time error '3141'.

I know I am horrible at trying to explain what it is that I am trying to do.
I appreciate any and all help I can get.

In my previous post the first piece of code uses only two tables and is
joined on the Group field of the (Employee and Rot) tables. I am using a
variable
(intMOD) in the query.

My = Forms!frm_Switchboard!dteDate

If Not IsNull(MyDate) Then
intMod = CLng(MyDate - 1) Mod 196
Else
MyDate = Format(Now, "##/##/####")
End If

This tells me which field to query in my Rot table. Probably not the best
way to go about it, but it works.

The problem I am having is when I try to add the third table to the query.
I want to print a report of the employees and the total number of OT hours
worked. This brings in my tblOvertime table. The tblOvertime table has a
HRMN field which can be joined to the HRMN field in the Employee table. I
would like to use the SUM Aggregate to total the hours worked for each
employee.

The report is unbound and the code is in the On Open Event .

Thanks in advance!

Ken Sheridan said:
You are just missing a space before the continuation character in:

& "WHERE ROT.[MOD" & intMod & "] = 1 "_

and the WHERE clause should precede the GROUP BY clause.

Try:

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0 " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES"

Ken Sheridan
Stafford, England

Duane said:
I would like to add a field from a third table and sum the field, but I
am
having trouble with the syntax. I was wondering if someone could help me
with it?

The third table is called tblOvertime. It is joined to the Employee
table
on the HRMN fields. I would like to SUM the Hours field in tblOvertime.

The first RecordSource (my starting point) works fine for my report.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & " _
& "[MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, NES,
& "Employee.TotHours, Employee.TotHours, " _
& "Employee.Group " _
& "FROM Employee " _
& "INNER JOIN Rot " _
& "ON Employee.Group = ROT.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"

This is the one that is messed up.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS
EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "_
& " AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"


I know this is probably written totally wrong, but I am trying to learn.
I
am receiving an error for a reserved word, argument name misspelled, or
the
punctuation is incorrect. I put more line continuations in here then
what
is actually in my code, for readability purposes.

Any help would be greatly appreciated.

I previously posted this in the forum for MS Access Reports, but there
doesn't appear to be as much activity there as there is here.
Click to expand...
Click to expand...
 
D

Duane

Thanks for responding Ken. I am still receiving and error stating "The
SELECT Statement includes a reserved word or an argument name that is
misspelled or the punctuation is incorrect" Run-Time error '3141'.

I know I am horrible at trying to explain what it is that I am trying to do.
I appreciate any and all help I can get.

In my previous post the first piece of code uses only two tables and is
joined on the Group field of the (Employee and Rot) tables. I am using a
variable (intMOD) in the query.

MyDate = Forms!frm_Switchboard!dteDate

If Not IsNull(MyDate) Then
intMod = CLng(MyDate - 1) Mod 196
Else
MyDate = Format(Now, "##/##/####")
End If

This tells me which field to query in my Rot table. Probably not the best
way to go about it, but it works.

The problem I am having is when I try to add the third table to the query.
I want to print a report of the employees and the total number of OT hours
worked. This brings in my tblOvertime table. The tblOvertime table has a
HRMN field which can be joined to the HRMN field in the Employee table. I
would like to use the SUM Aggregate to total the hours worked for each
employee.

The report is unbound and the code is in the On Open Event .

Thanks in advance!

Ken Sheridan said:
You are just missing a space before the continuation character in:

& "WHERE ROT.[MOD" & intMod & "] = 1 "_

and the WHERE clause should precede the GROUP BY clause.

Try:

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0 " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES"

Ken Sheridan
Stafford, England

Duane said:
I would like to add a field from a third table and sum the field, but I
am
having trouble with the syntax. I was wondering if someone could help me
with it?

The third table is called tblOvertime. It is joined to the Employee
table
on the HRMN fields. I would like to SUM the Hours field in tblOvertime.

The first RecordSource (my starting point) works fine for my report.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & " _
& "[MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, NES,
& "Employee.TotHours, Employee.TotHours, " _
& "Employee.Group " _
& "FROM Employee " _
& "INNER JOIN Rot " _
& "ON Employee.Group = ROT.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"

This is the one that is messed up.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS
EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "_
& " AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"


I know this is probably written totally wrong, but I am trying to learn.
I
am receiving an error for a reserved word, argument name misspelled, or
the
punctuation is incorrect. I put more line continuations in here then
what
is actually in my code, for readability purposes.

Any help would be greatly appreciated.

I previously posted this in the forum for MS Access Reports, but there
doesn't appear to be as much activity there as there is here.
Click to expand...
Click to expand...
 
K

Ken Sheridan

There is a surplus comma at the end of the following line:

& "Sum(tblOvertime.Hours) AS SumOfHours, "

which should be removed.

Otherwise the only things I can see which might be the cause of a problem
are that you have column's named Group and Mod. The first is an SQL keyword,
the second a VBA function name (modulus). Try wrapping them in brackets:
[Group] and [Mod]. In fact wrapping all the table and column names in
brackets as the Access query designer does will do no harm. Nevertheless its
best to avoid 'reserved' words, and use more explicit terms such as
EmployeeGroup or WorkGroup etc.

Ken Sheridan
Stafford, England

Duane said:
Thanks for responding Ken. I am still receiving and error stating "The
SELECT Statement includes a reserved word or an argument name that is
misspelled or the punctuation is incorrect" Run-Time error '3141'.

I know I am horrible at trying to explain what it is that I am trying to do.
I appreciate any and all help I can get.

In my previous post the first piece of code uses only two tables and is
joined on the Group field of the (Employee and Rot) tables. I am using a
variable (intMOD) in the query.

MyDate = Forms!frm_Switchboard!dteDate

If Not IsNull(MyDate) Then
intMod = CLng(MyDate - 1) Mod 196
Else
MyDate = Format(Now, "##/##/####")
End If

This tells me which field to query in my Rot table. Probably not the best
way to go about it, but it works.

The problem I am having is when I try to add the third table to the query.
I want to print a report of the employees and the total number of OT hours
worked. This brings in my tblOvertime table. The tblOvertime table has a
HRMN field which can be joined to the HRMN field in the Employee table. I
would like to use the SUM Aggregate to total the hours worked for each
employee.

The report is unbound and the code is in the On Open Event .

Thanks in advance!

Ken Sheridan said:
You are just missing a space before the continuation character in:

& "WHERE ROT.[MOD" & intMod & "] = 1 "_

and the WHERE clause should precede the GROUP BY clause.

Try:

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0 " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES"

Ken Sheridan
Stafford, England

Duane said:
I would like to add a field from a third table and sum the field, but I
am
having trouble with the syntax. I was wondering if someone could help me
with it?

The third table is called tblOvertime. It is joined to the Employee
table
on the HRMN fields. I would like to SUM the Hours field in tblOvertime.

The first RecordSource (my starting point) works fine for my report.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & " _
& "[MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, NES,
& "Employee.TotHours, Employee.TotHours, " _
& "Employee.Group " _
& "FROM Employee " _
& "INNER JOIN Rot " _
& "ON Employee.Group = ROT.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"

This is the one that is messed up.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS
EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "_
& " AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"


I know this is probably written totally wrong, but I am trying to learn.
I
am receiving an error for a reserved word, argument name misspelled, or
the
punctuation is incorrect. I put more line continuations in here then
what
is actually in my code, for readability purposes.

Any help would be greatly appreciated.

I previously posted this in the forum for MS Access Reports, but there
doesn't appear to be as much activity there as there is here.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane

Thanks Ken! The surplus comma was the problem. I will keep in mind what
you said about the naming convention on the Group and Mod fields

I have one other question if I may? I changed the second join from an INNER
JOIN to a LEFT JOIN so the results would be all eligible employees, not just
those who have worked overtime. That works fine except, if some of the
employees don't have any matching record (haven't worked any overtime) the
SumOfHours field is blank.

Is there a way to use the IIf function or Nz Function to show a zero (0) in
the field if the employee hasn't worked any overtime, whether it be in the
actual sql or at the field level on the report?

Thanks in Advance

Ken Sheridan said:
There is a surplus comma at the end of the following line:

& "Sum(tblOvertime.Hours) AS SumOfHours, "

which should be removed.

Otherwise the only things I can see which might be the cause of a problem
are that you have column's named Group and Mod. The first is an SQL
keyword,
the second a VBA function name (modulus). Try wrapping them in brackets:
[Group] and [Mod]. In fact wrapping all the table and column names in
brackets as the Access query designer does will do no harm. Nevertheless
its
best to avoid 'reserved' words, and use more explicit terms such as
EmployeeGroup or WorkGroup etc.

Ken Sheridan
Stafford, England

Duane said:
Thanks for responding Ken. I am still receiving and error stating "The
SELECT Statement includes a reserved word or an argument name that is
misspelled or the punctuation is incorrect" Run-Time error '3141'.

I know I am horrible at trying to explain what it is that I am trying to
do.
I appreciate any and all help I can get.

In my previous post the first piece of code uses only two tables and is
joined on the Group field of the (Employee and Rot) tables. I am using a
variable (intMOD) in the query.

MyDate = Forms!frm_Switchboard!dteDate

If Not IsNull(MyDate) Then
intMod = CLng(MyDate - 1) Mod 196
Else
MyDate = Format(Now, "##/##/####")
End If

This tells me which field to query in my Rot table. Probably not the
best
way to go about it, but it works.

The problem I am having is when I try to add the third table to the
query.
I want to print a report of the employees and the total number of OT
hours
worked. This brings in my tblOvertime table. The tblOvertime table has
a
HRMN field which can be joined to the HRMN field in the Employee table.
I
would like to use the SUM Aggregate to total the hours worked for each
employee.

The report is unbound and the code is in the On Open Event .

Thanks in advance!

Ken Sheridan said:
You are just missing a space before the continuation character in:

& "WHERE ROT.[MOD" & intMod & "] = 1 "_

and the WHERE clause should precede the GROUP BY clause.

Try:

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS EmpName,
" _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0 " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES"

Ken Sheridan
Stafford, England

:

I would like to add a field from a third table and sum the field, but
I
am
having trouble with the syntax. I was wondering if someone could help
me
with it?

The third table is called tblOvertime. It is joined to the Employee
table
on the HRMN fields. I would like to SUM the Hours field in
tblOvertime.

The first RecordSource (my starting point) works fine for my report.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & " _
& "[MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, NES,
& "Employee.TotHours, Employee.TotHours, " _
& "Employee.Group " _
& "FROM Employee " _
& "INNER JOIN Rot " _
& "ON Employee.Group = ROT.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 "
_
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"

This is the one that is messed up.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS
EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6,
" _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "_
& " AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 "
_
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"


I know this is probably written totally wrong, but I am trying to
learn.
I
am receiving an error for a reserved word, argument name misspelled,
or
the
punctuation is incorrect. I put more line continuations in here then
what
is actually in my code, for readability purposes.

Any help would be greatly appreciated.

I previously posted this in the forum for MS Access Reports, but there
doesn't appear to be as much activity there as there is here.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

The Nz function would be the best one to use. You should be able to do it in
the query with:

& "Nz(Sum(tblOvertime.Hours),0) AS SumOfHours, " _

or in the report by making the relevant control's ControlSource property:

=Nz([SumOfHours],0)

The other option would be to base the report on an ungrouped query, group
the report by employee and put the controls in the group footer, leaving the
detail section empty and zero height, with =Sum(Nz([Hours],0)) as the
ControlSource of a text box.

Ken Sheridan
Stafford, England

Duane said:
Thanks Ken! The surplus comma was the problem. I will keep in mind what
you said about the naming convention on the Group and Mod fields

I have one other question if I may? I changed the second join from an INNER
JOIN to a LEFT JOIN so the results would be all eligible employees, not just
those who have worked overtime. That works fine except, if some of the
employees don't have any matching record (haven't worked any overtime) the
SumOfHours field is blank.

Is there a way to use the IIf function or Nz Function to show a zero (0) in
the field if the employee hasn't worked any overtime, whether it be in the
actual sql or at the field level on the report?

Thanks in Advance

Ken Sheridan said:
There is a surplus comma at the end of the following line:

& "Sum(tblOvertime.Hours) AS SumOfHours, "

which should be removed.

Otherwise the only things I can see which might be the cause of a problem
are that you have column's named Group and Mod. The first is an SQL
keyword,
the second a VBA function name (modulus). Try wrapping them in brackets:
[Group] and [Mod]. In fact wrapping all the table and column names in
brackets as the Access query designer does will do no harm. Nevertheless
its
best to avoid 'reserved' words, and use more explicit terms such as
EmployeeGroup or WorkGroup etc.

Ken Sheridan
Stafford, England

Duane said:
Thanks for responding Ken. I am still receiving and error stating "The
SELECT Statement includes a reserved word or an argument name that is
misspelled or the punctuation is incorrect" Run-Time error '3141'.

I know I am horrible at trying to explain what it is that I am trying to
do.
I appreciate any and all help I can get.

In my previous post the first piece of code uses only two tables and is
joined on the Group field of the (Employee and Rot) tables. I am using a
variable (intMOD) in the query.

MyDate = Forms!frm_Switchboard!dteDate

If Not IsNull(MyDate) Then
intMod = CLng(MyDate - 1) Mod 196
Else
MyDate = Format(Now, "##/##/####")
End If

This tells me which field to query in my Rot table. Probably not the
best
way to go about it, but it works.

The problem I am having is when I try to add the third table to the
query.
I want to print a report of the employees and the total number of OT
hours
worked. This brings in my tblOvertime table. The tblOvertime table has
a
HRMN field which can be joined to the HRMN field in the Employee table.
I
would like to use the SUM Aggregate to total the hours worked for each
employee.

The report is unbound and the code is in the On Open Event .

Thanks in advance!

You are just missing a space before the continuation character in:

& "WHERE ROT.[MOD" & intMod & "] = 1 "_

and the WHERE clause should precede the GROUP BY clause.

Try:

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS EmpName,
" _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0 " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES"

Ken Sheridan
Stafford, England

:

I would like to add a field from a third table and sum the field, but
I
am
having trouble with the syntax. I was wondering if someone could help
me
with it?

The third table is called tblOvertime. It is joined to the Employee
table
on the HRMN fields. I would like to SUM the Hours field in
tblOvertime.

The first RecordSource (my starting point) works fine for my report.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & " _
& "[MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, NES,
& "Employee.TotHours, Employee.TotHours, " _
& "Employee.Group " _
& "FROM Employee " _
& "INNER JOIN Rot " _
& "ON Employee.Group = ROT.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 "
_
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"

This is the one that is messed up.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS
EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6,
" _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "_
& " AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 "
_
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"


I know this is probably written totally wrong, but I am trying to
learn.
I
am receiving an error for a reserved word, argument name misspelled,
or
the
punctuation is incorrect. I put more line continuations in here then
what
is actually in my code, for readability purposes.

Any help would be greatly appreciated.

I previously posted this in the forum for MS Access Reports, but there
doesn't appear to be as much activity there as there is here.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane

Thanks Ken. I originally tried what you suggested before I wrote me
previous post and I was receiving that dreaded syntax error message.

& "Nz(Sum(tblOvertime.Hours),0) AS SumOfHours, " _

Then when I saw what you wrote and I knew I was one the right track. I
tinkered with it and removed that darn extra comma after SumOfHours and it
work perfect.

Thanks again, and I will also keep in mind the other options you mentioned.

Ken Sheridan said:
The Nz function would be the best one to use. You should be able to do it
in
the query with:

& "Nz(Sum(tblOvertime.Hours),0) AS SumOfHours, " _

or in the report by making the relevant control's ControlSource property:

=Nz([SumOfHours],0)

The other option would be to base the report on an ungrouped query, group
the report by employee and put the controls in the group footer, leaving
the
detail section empty and zero height, with =Sum(Nz([Hours],0)) as the
ControlSource of a text box.

Ken Sheridan
Stafford, England

Duane said:
Thanks Ken! The surplus comma was the problem. I will keep in mind what
you said about the naming convention on the Group and Mod fields

I have one other question if I may? I changed the second join from an
INNER
JOIN to a LEFT JOIN so the results would be all eligible employees, not
just
those who have worked overtime. That works fine except, if some of the
employees don't have any matching record (haven't worked any overtime)
the
SumOfHours field is blank.

Is there a way to use the IIf function or Nz Function to show a zero (0)
in
the field if the employee hasn't worked any overtime, whether it be in
the
actual sql or at the field level on the report?

Thanks in Advance

Ken Sheridan said:
There is a surplus comma at the end of the following line:

& "Sum(tblOvertime.Hours) AS SumOfHours, "

which should be removed.

Otherwise the only things I can see which might be the cause of a
problem
are that you have column's named Group and Mod. The first is an SQL
keyword,
the second a VBA function name (modulus). Try wrapping them in
brackets:
[Group] and [Mod]. In fact wrapping all the table and column names in
brackets as the Access query designer does will do no harm.
Nevertheless
its
best to avoid 'reserved' words, and use more explicit terms such as
EmployeeGroup or WorkGroup etc.

Ken Sheridan
Stafford, England

:

Thanks for responding Ken. I am still receiving and error stating
"The
SELECT Statement includes a reserved word or an argument name that is
misspelled or the punctuation is incorrect" Run-Time error '3141'.

I know I am horrible at trying to explain what it is that I am trying
to
do.
I appreciate any and all help I can get.

In my previous post the first piece of code uses only two tables and
is
joined on the Group field of the (Employee and Rot) tables. I am
using a
variable (intMOD) in the query.

MyDate = Forms!frm_Switchboard!dteDate

If Not IsNull(MyDate) Then
intMod = CLng(MyDate - 1) Mod 196
Else
MyDate = Format(Now, "##/##/####")
End If

This tells me which field to query in my Rot table. Probably not the
best
way to go about it, but it works.

The problem I am having is when I try to add the third table to the
query.
I want to print a report of the employees and the total number of OT
hours
worked. This brings in my tblOvertime table. The tblOvertime table
has
a
HRMN field which can be joined to the HRMN field in the Employee
table.
I
would like to use the SUM Aggregate to total the hours worked for each
employee.

The report is unbound and the code is in the On Open Event .

Thanks in advance!

message
You are just missing a space before the continuation character in:

& "WHERE ROT.[MOD" & intMod & "] = 1 "_

and the WHERE clause should precede the GROUP BY clause.

Try:

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial] AS
EmpName,
" _
& "Employee.Telephone, Employee.Shift, Employee.NES, Rot.MOD6, " _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0 " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial], " _
& "Employee.Telephone, Employee.Shift, Employee.NES"

Ken Sheridan
Stafford, England

:

I would like to add a field from a third table and sum the field,
but
I
am
having trouble with the syntax. I was wondering if someone could
help
me
with it?

The third table is called tblOvertime. It is joined to the
Employee
table
on the HRMN fields. I would like to SUM the Hours field in
tblOvertime.

The first RecordSource (my starting point) works fine for my
report.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & " _
& "[MiddleInitial] AS EmpName, " _
& "Employee.Telephone, Employee.Shift, NES,
& "Employee.TotHours, Employee.TotHours, " _
& "Employee.Group " _
& "FROM Employee " _
& "INNER JOIN Rot " _
& "ON Employee.Group = ROT.Group " _
& "WHERE ROT.[MOD" & intMod & "] = 1 " _
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "]
= -1 "
_
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] >
0"

This is the one that is messed up.

Me.RecordSource = "SELECT Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial]
AS
EmpName, " _
& "Employee.Telephone, Employee.Shift, Employee.NES,
Rot.MOD6,
" _
& "Sum(tblOvertime.Hours) AS SumOfHours, " _
& "FROM Rot INNER JOIN (Employee INNER JOIN tblOvertime " _
& "ON Employee.HRMN = tblOvertime.HRMN) ON " _
& "Rot.Group = Employee.Group " _
& "GROUP BY Employee.HRMN, Employee.Group, " _
& "[LastName] & ', ' & [FirstName] & ' ' & [MiddleInitial],
" _
& "Employee.Telephone, Employee.Shift, Employee.NES " _
& "WHERE ROT.[MOD" & intMod & "] = 1 "_
& " AND Employee.[" & Forms!frm_Switchboard!MyList & "] = -1
"
_
& "AND Employee.[" & Forms!frm_Switchboard!MyList & "H] > 0"


I know this is probably written totally wrong, but I am trying to
learn.
I
am receiving an error for a reserved word, argument name
misspelled,
or
the
punctuation is incorrect. I put more line continuations in here
then
what
is actually in my code, for readability purposes.

Any help would be greatly appreciated.

I previously posted this in the forum for MS Access Reports, but
there
doesn't appear to be as much activity there as there is here.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with report recordsource 2
Help with sql query 2
sql help 1
Help with query from FoxPro 8

Top