السلام عليكم اخوة الكرام
لدينا جدول اسمه bill مرتبط ب Company به حقل نصي Trans_ID مصدره 1;"شيك بنكي";2;"فاتورة"
نريد اضافة هذا الحقل الى الاستعلام الموحد بشرط يكون Trans_ID=2
علما ان الجداول الاخرى بالاستعلام الموحد لا يوجد بها هذا الحقل
SELECT Bill.CompanyID, Company.[Nom &Prénom &fr], Company.N_Entrep, Company.[N° CompteRib], Company.[Centre de chéques Postaux], Company.[adress], -1 AS TheType, Sum(Bill.Bill_Value) AS TheValue, Add_Observations([Bill].[CompanyID],"Bill") AS Obsérvation, -1 AS M
FROM Company INNER JOIN Bill ON Company.CompanyID = Bill.CompanyID
GROUP BY Bill.CompanyID, Company.[Nom &Prénom &fr], Company.N_Entrep, Company.[N° CompteRib], Company.[Centre de chéques Postaux], Company.[adress], -1, Add_Observations([Bill].[CompanyID],"Bill"), Month([Bill_Date]), Year([Bill_Date])
HAVING (((Month([Bill_Date]))=Month(CDate([Forms]![FrmTransfer]![txtMonth1]))) AND ((Year([Bill_Date]))=Year(CDate([Forms]![FrmTransfer]![txtMonth1]))))
UNION ALL
SELECT Cridi.EmployeeID, Employee.[Nom &Prénom &fr],Employee.[Nom et Prénom], Employee.[N° CompteRib],Employee.[Centre de chéques Postaux],[adress], +1 AS TheType, Sum(Cridi.Cridi_Value) AS SumOfCridi_Value,Add_Observations([Cridi].[EmployeeID],"Cridi") AS Obsérvation,-1 AS M
FROM Employee INNER JOIN Cridi ON Employee.EmployeeID = Cridi.EmployeeID
GROUP BY Cridi.EmployeeID, Employee.[Nom &Prénom &fr],Employee.[Nom et Prénom], Employee.[N° CompteRib],Employee.[Centre de chéques Postaux],[adress], +1, Add_Observations([Cridi].[EmployeeID],"Cridi"), Month([Cridi_Date]), Year([Cridi_Date])
HAVING (((Month([Cridi_Date]))=Month(CDate([Forms]![FrmTransfer]![txtMonth1]))) AND ((Year([Cridi_Date]))=Year(CDate([Forms]![FrmTransfer]![txtMonth1]))))
UNION ALL
SELECT Mena7.EmployeeID, Employee.[Nom &Prénom &fr],Employee.[Nom et Prénom], Employee.[N° CompteRib],Employee.[Centre de chéques Postaux],[adress], +1 AS TheType, Sum(Mena7.Menha_Value) AS SumOfMenha_Value,Add_Observations([Mena7].[EmployeeID],"Mena7") AS Obsérvation ,-1 AS M
FROM Employee INNER JOIN Mena7 ON Employee.EmployeeID = Mena7.EmployeeID
GROUP BY Mena7.EmployeeID, Employee.[Nom &Prénom &fr],Employee.[Nom et Prénom], Employee.[N° CompteRib],Employee.[Centre de chéques Postaux],[adress], +1, Add_Observations([Mena7].[EmployeeID],"Mena7"), Month([Menha_Date]), Year([Menha_Date])
HAVING (((Month([Menha_Date]))=Month(CDate([Forms]![FrmTransfer]![txtMonth1]))) AND ((Year([Menha_Date]))=Year(CDate([Forms]![FrmTransfer]![txtMonth1]))))
UNION ALL
SELECT Sanitaire.EmployeeID, Employee.[Nom &Prénom &fr],Employee.[Nom et Prénom], Employee.[N° CompteRib],Employee.[Centre de chéques Postaux],[adress], +1 AS TheType, Sanitaire.NesbaValue,Add_Observations([Sanitaire].[EmployeeID],"Sanitaire") AS Obsérvation, -1 AS M
FROM Employee INNER JOIN Sanitaire ON Employee.EmployeeID = Sanitaire.EmployeeID
GROUP BY Sanitaire.EmployeeID, Employee.[Nom &Prénom &fr],Employee.[Nom et Prénom], Employee.[N° CompteRib],Employee.[Centre de chéques Postaux],[adress], +1, Sanitaire.NesbaValue, Add_Observations([Sanitaire].[EmployeeID],"Sanitaire"), Month([Sanitaire_Date]), Year([Sanitaire_Date])
HAVING (((Month([Sanitaire_Date]))=Month(CDate([Forms]![FrmTransfer]![txtMonth1]))) AND ((Year([Sanitaire_Date]))=Year(CDate([Forms]![FrmTransfer]![txtMonth1]))))
UNION ALL
SELECT Mossada.EmployeeID, Employee.[Nom &Prénom &fr],Employee.[Nom et Prénom], Employee.[N° CompteRib],Employee.[Centre de chéques Postaux],[adress], +1 AS TheType, Mossada.Moss_Value, Add_Observations([Mossada].[EmployeeID],"Mossada") AS Obsérvation, Moss_Ver
FROM Employee INNER JOIN Mossada ON Employee.EmployeeID = Mossada.EmployeeID
GROUP BY Mossada.EmployeeID, Employee.[Nom &Prénom &fr],Employee.[Nom et Prénom], Employee.[N° CompteRib],Employee.[Centre de chéques Postaux],[adress], +1, Mossada.Moss_Value, Add_Observations([Mossada].[EmployeeID],"Mossada"), Mossada.Moss_Ver, Month([Moss_Date]), Year([Moss_Date])
HAVING (((Month([Moss_Date]))=Month(CDate([Forms]![FrmTransfer]![txtMonth1]))) AND ((Year([Moss_Date]))=Year(CDate([Forms]![FrmTransfer]![txtMonth1]))))
UNION ALL SELECT Scolaire.EmployeeID, Employee.[Nom &Prénom &fr],Employee.[Nom et Prénom], Employee.[N° CompteRib],Employee.[Centre de chéques Postaux],[adress], +1 AS TheType, Scolaire.Scolaire_Value,Add_Observations([Scolaire].[EmployeeID],"Scolaire") AS Obsérvation, -1 AS M
FROM Employee INNER JOIN Scolaire ON Employee.EmployeeID = Scolaire.EmployeeID
GROUP BY Scolaire.EmployeeID, Employee.[Nom &Prénom &fr],Employee.[Nom et Prénom], Employee.[N° CompteRib],Employee.[Centre de chéques Postaux],[adress], +1, Scolaire.Scolaire_Value,Add_Observations([Scolaire].[EmployeeID],"Scolaire"), Month([Scolaire_Date]), Year([Scolaire_Date])
HAVING (((Month([Scolaire_Date]))=Month(CDate([Forms]![FrmTransfer]![txtMonth1]))) AND ((Year([Scolaire_Date]))=Year(CDate([Forms]![FrmTransfer]![txtMonth1]))));
ارجو التعديل على هذا الاستعلام الموجد من اجل ظهور النتيجة الصحيحة
