SubQuery Problem

[Scott]

Below is part of a subquery that I'm coding in vba for a report. I left the
"10" hard coded for the example in CODE 1. The "10" represents the results
of a calculation that needs the e.empID column also shown. If I just replace
the 10 with e.empID, within the vbe, the e.empID is not recognized as being
part of the sql SELECT statement. I have no way to write a SELECT statement
to get the e.empID column. CODE 2 shows the complete SQL.

I know there is always a way to do things, but I can't see it. Any ideas?


CODE 1
*********************

(Select Sum(t_data.dataOT_Hrs)
FROM t_data
WHERE t_data.empID = e.empID) / 10 ) AS OT_Hrs_YTD_Avg


CODE 2
*********************

sSQL = "SELECT d.dataID, e.empLast, e.empFirst, dt.dtDateBegin,
dt.dtDateEnd, dt.dtWeek, " & _
"d.empID, d.jobID, j.jobName, d.deptID,
dp.deptName, s.shiftName, d.shiftID, d.dataOT_Base, " & _
"d.dataRegHrs, d.dataOT_Hrs, d.dataRegHrs_RT,
d.millID, " & _
"(SELECT Sum(da.dataOT_Hrs) FROM t_date dt INNER
JOIN t_data da ON dt.dtID = da.dtID " & _
"WHERE da.empID = e.empID AND ((dt.dtDateBegin

= #" & txtYearBegin & "#) And " & _

"dt.dtDateBegin <= #" &
Forms!f_rpt_overtime.Form.txtDateBegin & "#)) AS OT_Hrs_YTD, " & _
"((Select Sum(t_data.dataOT_Hrs) FROM t_data " &
_
"WHERE t_data.empID = e.empID) / " &
GetEmpOT_Weeks([empID], Forms!f_rpt_overtime.Form.cboWeek.Column(1)) & " AS
OT_Hrs_YTD_Avg, " & _
"dt.dtID " & _
"FROM t_shifts AS s INNER JOIN (t_jobs AS j INNER JOIN
(t_dept AS dp INNER JOIN (t_date AS dt INNER JOIN " & _
"(t_data AS d INNER JOIN t_employee AS e ON
d.empID = e.empID) ON dt.dtID = d.dtID) ON dp.deptID = d.deptID) " &_
"ON j.jobID = d.jobID) ON s.shiftID = d.shiftID
" & _
"WHERE dt.dtID = " & Forms!f_rpt_overtime.Form.cboWeek &
" " & _
"ORDER BY e.empLast, e.empFirst, d.shiftID"

 

[Ken Snell MVP]

It's difficult to follow your coded stuff; let's start with just the SQL
statement on its own, outside the VBA code.

Post that and let us know specifically which part of the SQL statement is
giving you the problem. Then, after we resolve that issue, the VBA coding
will be much easier to do.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Below is part of a subquery that I'm coding in vba for a report. I left
the "10" hard coded for the example in CODE 1. The "10" represents the
results of a calculation that needs the e.empID column also shown. If I
just replace the 10 with e.empID, within the vbe, the e.empID is not
recognized as being part of the sql SELECT statement. I have no way to
write a SELECT statement to get the e.empID column. CODE 2 shows the
complete SQL.

I know there is always a way to do things, but I can't see it. Any ideas?


CODE 1
*********************

(Select Sum(t_data.dataOT_Hrs)
FROM t_data
WHERE t_data.empID = e.empID) / 10 ) AS OT_Hrs_YTD_Avg


CODE 2
*********************

sSQL = "SELECT d.dataID, e.empLast, e.empFirst, dt.dtDateBegin,
dt.dtDateEnd, dt.dtWeek, " & _
"d.empID, d.jobID, j.jobName, d.deptID,
dp.deptName, s.shiftName, d.shiftID, d.dataOT_Base, " & _
"d.dataRegHrs, d.dataOT_Hrs, d.dataRegHrs_RT,
d.millID, " & _
"(SELECT Sum(da.dataOT_Hrs) FROM t_date dt
INNER JOIN t_data da ON dt.dtID = da.dtID " & _
"WHERE da.empID = e.empID AND ((dt.dtDateBegin

= #" & txtYearBegin & "#) And " & _

"dt.dtDateBegin <= #" &
Forms!f_rpt_overtime.Form.txtDateBegin & "#)) AS OT_Hrs_YTD, " & _
"((Select Sum(t_data.dataOT_Hrs) FROM t_data "
& _
"WHERE t_data.empID = e.empID) / " &
GetEmpOT_Weeks([empID], Forms!f_rpt_overtime.Form.cboWeek.Column(1)) & "
AS OT_Hrs_YTD_Avg, " & _
"dt.dtID " & _
"FROM t_shifts AS s INNER JOIN (t_jobs AS j INNER JOIN
(t_dept AS dp INNER JOIN (t_date AS dt INNER JOIN " & _
"(t_data AS d INNER JOIN t_employee AS e ON
d.empID = e.empID) ON dt.dtID = d.dtID) ON dp.deptID = d.deptID) " &_
"ON j.jobID = d.jobID) ON s.shiftID = d.shiftID
" & _
"WHERE dt.dtID = " & Forms!f_rpt_overtime.Form.cboWeek
& " " & _
"ORDER BY e.empLast, e.empFirst, d.shiftID"

 

[Scott]

I did a debug.print of the full sql. You'll notice thesame 10 below. In the
vbe, I need the e.empID where the 10 resides. Actually, the final code will
have an access function that depends on the e.empID. That's why I got into
this mess. So the 10, will eventually be myFunction(e.empID).

' HARD-CODED:

SELECT d.dataID, e.empLast, e.empFirst, dt.dtDateBegin, dt.dtDateEnd,
dt.dtWeek, d.empID, d.jobID, j.jobName, d.deptID, dp.deptName, s.shiftName,
d.shiftID, d.dataOT_Base, d.dataRegHrs, d.dataOT_Hrs, d.dataRegHrs_RT,
d.millID,

(SELECT Sum(da.dataOT_Hrs)
FROM t_date dt INNER JOIN t_data da ON dt.dtID = da.dtID
WHERE da.empID = e.empID AND ((dt.dtDateBegin >= #1/4/2009#) And
dt.dtDateBegin <= #3/8/2009#)) AS OT_Hrs_YTD,

((Select Sum(t_data.dataOT_Hrs)
FROM t_data
WHERE t_data.empID = e.empID) / 10 ) AS OT_Hrs_YTD_Avg,
dt.dtID

FROM t_shifts AS s INNER JOIN (t_jobs AS j INNER JOIN (t_dept AS dp INNER
JOIN (t_date AS dt INNER JOIN (t_data AS d INNER JOIN t_employee AS e ON
d.empID = e.empID) ON dt.dtID = d.dtID) ON dp.deptID = d.deptID)
ON j.jobID = d.jobID) ON s.shiftID = d.shiftID
WHERE dt.dtID = 80
ORDER BY e.empLast, e.empFirst, d.shiftID

It's difficult to follow your coded stuff; let's start with just the SQL
statement on its own, outside the VBA code.

Post that and let us know specifically which part of the SQL statement is
giving you the problem. Then, after we resolve that issue, the VBA coding
will be much easier to do.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Below is part of a subquery that I'm coding in vba for a report. I left
the "10" hard coded for the example in CODE 1. The "10" represents the
results of a calculation that needs the e.empID column also shown. If I
just replace the 10 with e.empID, within the vbe, the e.empID is not
recognized as being part of the sql SELECT statement. I have no way to
write a SELECT statement to get the e.empID column. CODE 2 shows the
complete SQL.

I know there is always a way to do things, but I can't see it. Any ideas?


CODE 1
*********************

(Select Sum(t_data.dataOT_Hrs)
FROM t_data
WHERE t_data.empID = e.empID) / 10 ) AS OT_Hrs_YTD_Avg


CODE 2
*********************

sSQL = "SELECT d.dataID, e.empLast, e.empFirst,
dt.dtDateBegin, dt.dtDateEnd, dt.dtWeek, " & _
"d.empID, d.jobID, j.jobName, d.deptID,
dp.deptName, s.shiftName, d.shiftID, d.dataOT_Base, " & _
"d.dataRegHrs, d.dataOT_Hrs, d.dataRegHrs_RT,
d.millID, " & _
"(SELECT Sum(da.dataOT_Hrs) FROM t_date dt
INNER JOIN t_data da ON dt.dtID = da.dtID " & _
"WHERE da.empID = e.empID AND ((dt.dtDateBegin

= #" & txtYearBegin & "#) And " & _

"dt.dtDateBegin <= #" &
Forms!f_rpt_overtime.Form.txtDateBegin & "#)) AS OT_Hrs_YTD, " & _
"((Select Sum(t_data.dataOT_Hrs) FROM t_data "
& _
"WHERE t_data.empID = e.empID) / " &
GetEmpOT_Weeks([empID], Forms!f_rpt_overtime.Form.cboWeek.Column(1)) & "
AS OT_Hrs_YTD_Avg, " & _
"dt.dtID " & _
"FROM t_shifts AS s INNER JOIN (t_jobs AS j INNER JOIN
(t_dept AS dp INNER JOIN (t_date AS dt INNER JOIN " & _
"(t_data AS d INNER JOIN t_employee AS e ON
d.empID = e.empID) ON dt.dtID = d.dtID) ON dp.deptID = d.deptID) " &_
"ON j.jobID = d.jobID) ON s.shiftID =
d.shiftID " & _
"WHERE dt.dtID = " & Forms!f_rpt_overtime.Form.cboWeek
& " " & _
"ORDER BY e.empLast, e.empFirst, d.shiftID"

 

[Ken Snell MVP]

Will you be calling the function within the SQL statement? Or are you
wanting to concatenate the results of the function call into the SQL
statement?

If the former, then replacing 10 by MyFunction(e.empID) should work fine. If
the latter, then you'll need a way to get the value of e.empID outside the
query so that you can use it in your function's argument.

 

[Scott]

it's the latter, that's what is puzzling me. i've never come across this
situation. what would you do?

 

[Ken Snell MVP]

Any reason why you cannot call the function from within the query? If it's a
public function in a regular module, it will work just fine.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Scott]

if you notice my function GetEmpOT_Weeks. I've tried everything, but the vbe
doesn't recognize the e.empID

sSQL = "INSERT INTO t_rpt_overtime (dataID, empLast, empFirst,
dtDateBegin, dtDateEnd, " & _
"dtWeek, empID, jobID, jobName, deptID,
deptName, shiftName, shiftID, dataOT_Base, dataRegHrs, " & _
"dataOT_Hrs, dataRegHrs_RT, millID, OT_Hrs_YTD,
OT_Hrs_YTD_Avg, dtID) " & _
"SELECT d.dataID, e.empLast, e.empFirst, dt.dtDateBegin,
dt.dtDateEnd, dt.dtWeek, " & _
"d.empID, d.jobID, j.jobName, d.deptID,
dp.deptName, s.shiftName, d.shiftID, d.dataOT_Base, " & _
"d.dataRegHrs, d.dataOT_Hrs, d.dataRegHrs_RT,
d.millID, " & _
"(SELECT Sum(da.dataOT_Hrs) FROM t_date dt INNER
JOIN t_data da ON dt.dtID = da.dtID " & _
"WHERE da.empID = e.empID AND ((dt.dtDateBegin

= #" & txtYearBegin & "#) And " & _

"dt.dtDateBegin <= #" &
Forms!f_rpt_overtime.Form.txtDateBegin & "#)) AS OT_Hrs_YTD, " & _
"((Select Sum(t_data.dataOT_Hrs) FROM t_data " &
_
"WHERE t_data.empID = e.empID AND
(t_data.dtDateBegin >= #" & txtYearBegin & "#) And " & _
"(t_data.dtDateBegin <= #" &
Forms!f_rpt_overtime.Form.txtDateBegin & "#)) " & _
"/ " & GetEmpOT_Weeks(e.empID,
Forms!f_rpt_overtime.Form.cboWeek.Column(1)) & " ) AS OT_Hrs_YTD_Avg, " & _
"dt.dtID " & _
"FROM t_shifts AS s INNER JOIN (t_jobs AS j INNER JOIN
(t_dept AS dp INNER JOIN (t_date AS dt INNER JOIN " & _
"(t_data AS d INNER JOIN t_employee AS e " & _
"ON d.empID = e.empID) ON dt.dtID = d.dtID) ON
dp.deptID = d.deptID) " & _
"ON j.jobID = d.jobID) ON s.shiftID = d.shiftID
" & _
"WHERE dt.dtID = " & Forms!f_rpt_overtime.Form.cboWeek &
" " & _
"ORDER BY e.empLast, e.empFirst, d.shiftID"

 

[Ken Snell MVP]

As I noted, if you make the GetEmpOT_Weeks function a public function in a
regular module, then you can just use the call directly in the query:

"WHERE t_data.empID = e.empID AND
(t_data.dtDateBegin >= #" & txtYearBegin & "#) And " & _
"(t_data.dtDateBegin <= #" &
Forms!f_rpt_overtime.Form.txtDateBegin & "#)) " & _
"/ GetEmpOT_Weeks(e.empID, " & _
Forms!f_rpt_overtime.Form.cboWeek.Column(1) & ")) AS OT_Hrs_YTD_Avg, " & _
"dt.dtID " & _