SubQuery Problem

S

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"
 
K

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/


Scott said:
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"
 
S

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


Ken Snell MVP said:
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/


Scott said:
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"
 
K

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.
 
S

Scott

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

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"
 
K

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 " & _
 

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

Possible to turn off grouping programatically 4
SubQuery 1
Counting Subquery Syntax 2
Counting Subquery Syntax 0
Year to Date subquery 7
Subquery Sum Containing Parameters 1
Subquery Problem 1
Subquery Confusion 2

Top