Hello All,
I have a Crosstab Query I need to display All Members even if there is no value associated and it should display $0.00 Currently it is showing 2 but if I add them under the Property Column heading I thought it will display, which it did but then I do not get anu cost breakdown in the column. I will be using this query in the Report/Form and currently for empty field values it is giving Name error. Require guidance please.
Thanks
Taruna
SQL
TRANSFORM Sum([SumOfCurrentBudget]/[SumOfMemberShare]*[MemberShare]) AS MemberCost
SELECT [TestingProjectWPTable Query].ProjectID, [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].WPName, [TestingProjectWPTable Query].SumOfCurrentBudget, Avg([TestingProjectWPTable Query].SumOfMemberShare) AS [Total Of SumOfMemberShare]
FROM [TestingProjectWPTable Query] INNER JOIN TestProjectCostFieldsJPPQRY ON [TestingProjectWPTable Query].ProjectID = TestProjectCostFieldsJPPQRY.ProjectID
GROUP BY [TestingProjectWPTable Query].ProjectID, [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].WPName, [TestingProjectWPTable Query].SumOfCurrentBudget
PIVOT [TestProjectCostFieldsJPPQRY].[MemberName] & ", Share " & [TestProjectCostFieldsJPPQRY].[MemberShare];
I have a Crosstab Query I need to display All Members even if there is no value associated and it should display $0.00 Currently it is showing 2 but if I add them under the Property Column heading I thought it will display, which it did but then I do not get anu cost breakdown in the column. I will be using this query in the Report/Form and currently for empty field values it is giving Name error. Require guidance please.
Thanks
Taruna
SQL
TRANSFORM Sum([SumOfCurrentBudget]/[SumOfMemberShare]*[MemberShare]) AS MemberCost
SELECT [TestingProjectWPTable Query].ProjectID, [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].WPName, [TestingProjectWPTable Query].SumOfCurrentBudget, Avg([TestingProjectWPTable Query].SumOfMemberShare) AS [Total Of SumOfMemberShare]
FROM [TestingProjectWPTable Query] INNER JOIN TestProjectCostFieldsJPPQRY ON [TestingProjectWPTable Query].ProjectID = TestProjectCostFieldsJPPQRY.ProjectID
GROUP BY [TestingProjectWPTable Query].ProjectID, [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].WPName, [TestingProjectWPTable Query].SumOfCurrentBudget
PIVOT [TestProjectCostFieldsJPPQRY].[MemberName] & ", Share " & [TestProjectCostFieldsJPPQRY].[MemberShare];