Joining 2 Left Join Queries

[esi]

Trying to incorporate 2 left join queries into 1 combined query. Basically
I'm trying to get the total REG hours and OVT hours for each pay period and
each dept. I need to make sure that if hours do not exist for a given pay
period or dept, a null value is shown. I tried to left join Q1 with
qryTotalREGPayroll and qryTotalOVTPayroll in the same query but, I would up
with results doubling the amount of SumOfTotal_REGHrs. Not sure how to
srtucture this query.

Left Query 1 Sql:

SELECT Q1.Month, Q1.Period, Q1.[Department Number],
Sum(qryTotalREGPayroll.Total_REGHrs) AS SumOfTotal_REGHrs
FROM Q1 LEFT JOIN qryTotalREGPayroll ON (Q1.[Department Number] =
qryTotalREGPayroll.Dept) AND (Q1.Period = qryTotalREGPayroll.Period)
GROUP BY Q1.Month, Q1.Period, Q1.[Department Number];


Left Query 2 sql:

SELECT Q1.Month, Q1.Period, Q1.[Department Number],
Sum(qryTotalOVTPayroll.Total_OVTHrs) AS SumOfTotal_OVTHrs
FROM Q1 LEFT JOIN qryTotalOVTPayroll ON (Q1.[Department Number] =
qryTotalOVTPayroll.Dept) AND (Q1.Period = qryTotalOVTPayroll.Period)
GROUP BY Q1.Month, Q1.Period, Q1.[Department Number];

 

[Alejandra Parra]

Maybe this can help Use an alias for each query and then join them

SELECT TBL1.STR_NUMEROLISTA AS NL, TBL1.STR_NOMBREABREVIADO AS NOMBRE " +
"FROM " +
"[SELECT TBL_ALUMNOS.STR_NOMBREABREVIADO,
TBL_ALUMNOS.STR_NUMEROLISTA, TBL_ALUMNOS.STR_CVEALUMNO,
TBL_CALIFICACIONES.STR_VALOR, TBL_CALIFICACIONES.STR_CVEDATO " +
"FROM TBL_ALUMNOS " +
"LEFT JOIN " +
"TBL_CALIFICACIONES ON TBL_ALUMNOS.STR_CVEALUMNO =
TBL_CALIFICACIONES.STR_CVEALUMNO " +
"WHERE TBL_ALUMNOS.STR_CVEGRADO='" + strGradoElegido + "' " +
"AND TBL_ALUMNOS.STR_CVEGRUPO='" + strGrupoElegido + "']. AS TBL1 "
+
"LEFT JOIN " +
"[SELECT
TBL_CALIFICACIONES.STR_CVEALUMNO,TBL_CALIFICACIONES.STR_CVEDATO,
TBL_DATOSCAPTURA.STR_DESCRIPCION " +
"FROM TBL_CALIFICACIONES
RIGHT JOIN
TBL_DATOSCAPTURA ON TBL_CALIFICACIONES.STR_CVEDATO =
TBL_DATOSCAPTURA.STR_CVEDATO
"WHERE TBL_DATOSCAPTURA.STR_CVEDATO IN " +
"(SELECT TBL_DATOSPERIODO.STR_CVEDATO FROM TBL_DATOSPERIODO,
TBL_DATOSMATERIA " +
"WHERE TBL_DATOSPERIODO.STR_CVEDATO=TBL_DATOSMATERIA.STR_CVEDATO AND
" +
"TBL_DATOSMATERIA.STR_CVEMATERIA='" + strMateriaElegida + "')" +
"]. AS TBL2 " +
"ON (TBL1.STR_CVEDATO) =(TBL2.STR_CVEDATO) "

 

[esi]

I'm still very confused

Maybe this can help Use an alias for each query and then join them

SELECT TBL1.STR_NUMEROLISTA AS NL, TBL1.STR_NOMBREABREVIADO AS NOMBRE " +
"FROM " +
"[SELECT TBL_ALUMNOS.STR_NOMBREABREVIADO,
TBL_ALUMNOS.STR_NUMEROLISTA, TBL_ALUMNOS.STR_CVEALUMNO,
TBL_CALIFICACIONES.STR_VALOR, TBL_CALIFICACIONES.STR_CVEDATO " +
"FROM TBL_ALUMNOS " +
"LEFT JOIN " +
"TBL_CALIFICACIONES ON TBL_ALUMNOS.STR_CVEALUMNO =
TBL_CALIFICACIONES.STR_CVEALUMNO " +
"WHERE TBL_ALUMNOS.STR_CVEGRADO='" + strGradoElegido + "' " +
"AND TBL_ALUMNOS.STR_CVEGRUPO='" + strGrupoElegido + "']. AS TBL1 "
+
"LEFT JOIN " +
"[SELECT
TBL_CALIFICACIONES.STR_CVEALUMNO,TBL_CALIFICACIONES.STR_CVEDATO,
TBL_DATOSCAPTURA.STR_DESCRIPCION " +
"FROM TBL_CALIFICACIONES
RIGHT JOIN
TBL_DATOSCAPTURA ON TBL_CALIFICACIONES.STR_CVEDATO =
TBL_DATOSCAPTURA.STR_CVEDATO
"WHERE TBL_DATOSCAPTURA.STR_CVEDATO IN " +
"(SELECT TBL_DATOSPERIODO.STR_CVEDATO FROM TBL_DATOSPERIODO,
TBL_DATOSMATERIA " +
"WHERE TBL_DATOSPERIODO.STR_CVEDATO=TBL_DATOSMATERIA.STR_CVEDATO AND
" +
"TBL_DATOSMATERIA.STR_CVEMATERIA='" + strMateriaElegida + "')" +
"]. AS TBL2 " +
"ON (TBL1.STR_CVEDATO) =(TBL2.STR_CVEDATO) "

Trying to incorporate 2 left join queries into 1 combined query. Basically
I'm trying to get the total REG hours and OVT hours for each pay period and
each dept. I need to make sure that if hours do not exist for a given pay
period or dept, a null value is shown. I tried to left join Q1 with
qryTotalREGPayroll and qryTotalOVTPayroll in the same query but, I would up
with results doubling the amount of SumOfTotal_REGHrs. Not sure how to
srtucture this query.

Left Query 1 Sql:

SELECT Q1.Month, Q1.Period, Q1.[Department Number],
Sum(qryTotalREGPayroll.Total_REGHrs) AS SumOfTotal_REGHrs
FROM Q1 LEFT JOIN qryTotalREGPayroll ON (Q1.[Department Number] =
qryTotalREGPayroll.Dept) AND (Q1.Period = qryTotalREGPayroll.Period)
GROUP BY Q1.Month, Q1.Period, Q1.[Department Number];


Left Query 2 sql:

SELECT Q1.Month, Q1.Period, Q1.[Department Number],
Sum(qryTotalOVTPayroll.Total_OVTHrs) AS SumOfTotal_OVTHrs
FROM Q1 LEFT JOIN qryTotalOVTPayroll ON (Q1.[Department Number] =
qryTotalOVTPayroll.Dept) AND (Q1.Period = qryTotalOVTPayroll.Period)
GROUP BY Q1.Month, Q1.Period, Q1.[Department Number];