If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Joining 2 Left Join Queries
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]; |
#2
|
|||
|
|||
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_CALIFICACIONE S.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" escribió en el mensaje ... 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]; |
#3
|
|||
|
|||
I'm still very confused
"Alejandra Parra" wrote in message ... 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_CALIFICACIONE S.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" escribió en el mensaje ... 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]; |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculated Value will not store in Table | tonyaims | Using Forms | 10 | September 14th, 2004 03:11 AM |
Left Join weird problem | Roberto Fabbrica | Running & Setting Up Queries | 1 | August 16th, 2004 12:08 PM |
Multiple Left Joins and an Inner Join | Corey Burnett | Running & Setting Up Queries | 2 | July 15th, 2004 01:21 PM |
Why doesn't my LEFT JOIN work? | Dave | Running & Setting Up Queries | 5 | June 21st, 2004 10:45 AM |
Left Join Problem | Jonathan Haddad | Running & Setting Up Queries | 4 | June 8th, 2004 11:51 PM |