Crosstab Column Heading (1 Viewer)

sunnytaru

Member
Local time
Yesterday, 19:02
Joined
Mar 24, 2022
Messages
35
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];


1648522507056.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 16:02
Joined
Oct 29, 2018
Messages
18,962
You would use the In() clause like so:
Code:
...PIVOT FieldName In("Column1,Column2,Column3,etc.")
 

sunnytaru

Member
Local time
Yesterday, 19:02
Joined
Mar 24, 2022
Messages
35
You would use the In() clause like so:
Code:
...PIVOT FieldName In("Column1,Column2,Column3,etc.")
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 [MemberName] In("AECL Share 1, BP Share 4, CNL Share 1.5");

This results all in one column AECL Share 1, BP Share 4, CNL Share 1.5 and no value reflects, I need these in separate columns.
When I add it this way "PIVOT [TestProjectCostFieldsJPPQRY].[MemberName] & ", Share " & [TestProjectCostFieldsJPPQRY].[MemberShare];" I see the AECL Share 1 in a separate column and value below it. I want heading populated even if no value so when I display in form/Report I do not have to rearrange fields. I am not sure if I explained well...
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 16:02
Joined
Oct 29, 2018
Messages
18,962
Hi. Sorry, I made a typo. Each column should be separated and enclosed with their own delimiters. For example:
Code:
 PIVOT [MemberName] In("AECL Share 1", "BP Share 4", "CNL Share 1.5")
Hope that helps...
 

sunnytaru

Member
Local time
Yesterday, 19:02
Joined
Mar 24, 2022
Messages
35
Hi. Sorry, I made a typo. Each column should be separated and enclosed with their own delimiters. For example:
Code:
 PIVOT [MemberName] In("AECL Share 1", "BP Share 4", "CNL Share 1.5")
Hope that helps...
I tried this Headers appear but values do not appear in query. But when I add this PIVOT [TestProjectCostFieldsJPPQRY].[MemberName] & ", Share " & [TestProjectCostFieldsJPPQRY].[MemberShare] this way values and header appear ; But with this option in the report the fields that are empty show #Name? Is there a way to add if this field does not exists add $0.00 ? In the form that is getting value from the query?
 

ongke0711

New member
Local time
Today, 06:02
Joined
Feb 20, 2020
Messages
2
As on the picture, there's a comma in the column header. So you adjust a little bit the SQL statement of theDBGuy, I think it will be ok.
Code:
PIVOT [MemberName] In("AECL, Share 1", "BP, Share 4", "CNL, Share 1.5")
 

sunnytaru

Member
Local time
Yesterday, 19:02
Joined
Mar 24, 2022
Messages
35
As on the picture, there's a comma in the column header. So you adjust a little bit the SQL statement of theDBGuy, I think it will be ok.
Code:
PIVOT [MemberName] In("AECL, Share 1", "BP, Share 4", "CNL, Share 1.5")
I tried the above and the value does not display

1649299614052.png


PIVOT [TestProjectCostFieldsJPPQRY].[MemberName] & ", Share " & [TestProjectCostFieldsJPPQRY].[MemberShare] ($ value displayed)

1649299688203.png
 

Users who are viewing this thread

Top Bottom