Crosstab查询公式

Question

我正在遇到问题，以了解如何完成这项工作。我要求公式检查是否更改documentType，然后使用该MemberId使用该MemberShare和sumofmembershare。

当前，即使MemberId不是该documentytype的一部分，它也会显示所有内容。请帮忙。

我尝试了此公式，并且不会计算documentType jpa1值

成员cost：sum（iif（[testingProjectWptable Query]。[documentTyType] = [testProjectCostCostfieldsjppqry]。[documentTyType]。 .[MemberShare]/[Total Share],0),0))

Sample data

ProjecID	WPNumber	WPName	DocumentType	CurrentBudget	TotalShare	WikCredit	MemberID
JP2001	80000	ProjectDev	JPP	$15,000	10.5
JP2001	81000	ProjectMgmnt	JPP	$25,000	10.5
JP2001	82000	ProjectMgmnt	JPA1	$45,000	13.5

MEMBER Table

ProjecID	DocumentType	MemberID	MemberShare
JP2001	JPP	11	5.5
JP2001	JPP	2	4.0
JP2001	JPP	9	1.0
JP2001	JPA1	7	2.0
JP2001	JPA1 JPA1	8	1.0

JPP总成员共享10.5
JPA总成员共享13.5

SQL

PARAMETERS [Forms]![APLTBLFRM]![ProjectID] Long;
TRANSFORM Sum(Nz([CurrentBudget]*[MemberShare]/[Total Share],0)) AS MemberCost
SELECT [TestingProjectWPTable Query].ProjectID, [TestingProjectWPTable Query].WPNumber, [TestingProjectWPTable Query].WPName, [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].CurrentBudget, [TestingProjectWPTable Query].SumOfSumOfMemberShare AS [Total Share], [TestingProjectWPTable Query].WiKCredit, [TestingProjectWPTable Query].MemberID
FROM TestProjectCostFieldsJPPQRY INNER JOIN [TestingProjectWPTable Query] ON TestProjectCostFieldsJPPQRY.ProjectID = [TestingProjectWPTable Query].ProjectID
WHERE (((TestProjectCostFieldsJPPQRY.ProjectID)=[Forms]![APLTBLFRM]![ProjectID]))
GROUP BY [TestingProjectWPTable Query].ProjectID, [TestingProjectWPTable Query].WPNumber, [TestingProjectWPTable Query].WPName, [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].CurrentBudget, [TestingProjectWPTable Query].SumOfSumOfMemberShare, [TestingProjectWPTable Query].WiKCredit, [TestingProjectWPTable Query].MemberID
ORDER BY [TestingProjectWPTable Query].DocumentType DESC 
PIVOT [TestProjectCostFieldsJPPQRY].[MemberName] & ", Share " & [TestProjectCostFieldsJPPQRY].[MemberShare];

输出视图

谢谢，
塔鲁纳