السلام عليكم اخوة الكرام
لدينا جدول اسمه 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]))));
ارجو التعديل على هذا الاستعلام الموجد من اجل ظهور النتيجة الصحيحة
تمت العملية بنجاح شكرا لك استاذ Eng.Qassim ربي يجعل تعبك معي في ميزان حسناتك ببركة الايام العشرة الاخيرة من الشهر الفضيل
