Crosstab Query - Access Developers - Computer Learning Zone


	Back to Access Developers Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Crosstab Query

Taruna Matharu

4 years ago

Hello All,

Can someone help me understand in the crosstab query, how I can make the formula to check if DocumentType is changes Calculate for that MemberID using that MemberShare Only

TRANSFORM Sum(Nz([TestingProjectWPTable Query].[CurrentBudget]*[TestProjectCostFieldsJPPQRY].[MemberShare]/[Total Share],0)) AS MemberCost
SELECT [TestingProjectWPTable Query].ProjectID, [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].WPNumber, [TestingProjectWPTable Query].WPName, [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
GROUP BY [TestingProjectWPTable Query].ProjectID, [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].WPNumber, [TestingProjectWPTable Query].WPName, [TestingProjectWPTable Query].CurrentBudget, [TestingProjectWPTable Query].SumOfSumOfMemberShare, [TestingProjectWPTable Query].WiKCredit, [TestingProjectWPTable Query].MemberID
ORDER BY [TestingProjectWPTable Query].ProjectID DESC , [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].WPNumber DESC
PIVOT [TestProjectCostFieldsJPPQRY].[MemberName] & ", Share " & [TestProjectCostFieldsJPPQRY].[MemberShare];

	Taruna Matharu OP @Reply 4 years ago

	Adam Schwanz @Reply 4 years ago
You need to use an IIF statement to change how it behaves when different conditions are true, in this case what DocumentType equals IIF Function

	Taruna Matharu OP @Reply 4 years ago
Adam, Appreciate response, I cannot understand IIF DocumentType equals to ? what I should be comparing too.

	Adam Schwanz @Reply 4 years ago
You said you want to change how it calculates based on what the Document Type is? In whatever field is doing the calculation you need to tell it how to do which calculation and when, you can use IIF for that.

Taruna Matharu OP @Reply

4 years ago

Hi Adam
This formula does not consider the DoumentType as I did not specify MemberCost: Sum(Nz([CurrentBudget]*[MemberShare]/[Total Share],0))

When I check for the DocumentType with this
MemberCost: Sum(IIf([TestingProjectWPTable Query].[DocumentType]=[TestProjectCostFieldsJPPQRY].[DocumentType],Nz([TestingProjectWPTable Query].[CurrentBudget]*[TestProjectCostFieldsJPPQRY].[MemberShare]/[Total Share],0),0))
It does not calculate the new DocumentType . I am so lost :-(

	Taruna Matharu OP @Reply 4 years ago

	Adam Schwanz @Reply 4 years ago
OK, I'm kind of lost too. What are you trying to make happen? What do you want to be able to do, and what will that cause to change in your report?

	Taruna Matharu OP @Reply 4 years ago

	Taruna Matharu OP @Reply 4 years ago
Adam, I just posted the output I am trying to achieve. When the DocumentType for that Member is entered it should only calculate for that Member

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developers.

Next Unseen

New Feature: Comment Live View