If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab Query - Recalculate totals after each date
I have created a crosstab query that displays values for individuals across
time as follows: Name TransactionDate1 TransactionDate2 Shareholder1 50 -10 Shareholder2 30 -10 Shareholder3 10 -5 I'm hoping that there is some way I can add a Row Heading that calculates totals for each Shareholder following each TransactionDate, like so: Name TD1 TotalAfterTD1 TD2 TotalAfterTD2 Shareholder1 50 50 -10 40 Shareholder2 30 30 -10 20 Shareholder3 10 10 -5 5 The transactions do not occur at regular intervals, which is why I have not been able to adapt any of the solutions I've found on this site for tallying totals, say, by month. I also tried using the query as the basis for a report so that I could create a Running Sum text box for this purpose, but was told that I had too many fields for the report. Again, I can't see how I would standardize the dates to yield fewer fields since the transactions don't occur at regular intervals. I've gotten the sense that I need to use the Dsum function. The following suggestion from John Vinson (adapted with my field names) seemed to be on the right track: =DSum([Date], [PerDay2009-Dec31Totals_Q], [Date] = #" & [Date] & "#) But I can't figure out how to insert the above into my existing query. Here is the SQL for my crosstab query: TRANSFORM Sum([PerDay2009-Dec31Totals_Q].Shares) AS SumOfShares SELECT [PerDay2009-Dec31Totals_Q].[Full Name], Sum([PerDay2009-Dec31Totals_Q].Shares) AS [Total Of Shares] FROM [PerDay2009-Dec31Totals_Q] GROUP BY [PerDay2009-Dec31Totals_Q].[Full Name] ORDER BY [PerDay2009-Dec31Totals_Q].Date PIVOT [PerDay2009-Dec31Totals_Q].Date; Any help you can offer would be most appreciated. |
#2
|
|||
|
|||
Crosstab Query - Recalculate totals after each date
These queries include the running sum --
PerDay2009-Dec31Totals_Q_XC -- SELECT [PerDay2009-Dec31Totals_Q].[Full Name], [PerDay2009-Dec31Totals_Q].Dates, Sum([PerDay2009-Dec31Totals_Q].Shares) AS SumOfShares FROM [PerDay2009-Dec31Totals_Q] GROUP BY [PerDay2009-Dec31Totals_Q].[Full Name], [PerDay2009-Dec31Totals_Q].Dates; PerDay2009-Dec31Totals_Q_CXC -- SELECT [PerDay2009-Dec31Totals_Q].[Full Name], [PerDay2009-Dec31Totals_Q_XC].Dates, [PerDay2009-Dec31Totals_Q_XC].SumOfShares, Sum([PerDay2009-Dec31Totals_Q].Shares) AS Tally FROM [PerDay2009-Dec31Totals_Q] INNER JOIN [PerDay2009-Dec31Totals_Q_XC] ON [PerDay2009-Dec31Totals_Q].[Full Name] = [PerDay2009-Dec31Totals_Q_XC].[Full Name] WHERE ((([PerDay2009-Dec31Totals_Q].Dates)=[PerDay2009-Dec31Totals_Q_XC].[Dates])) GROUP BY [PerDay2009-Dec31Totals_Q].[Full Name], [PerDay2009-Dec31Totals_Q_XC].Dates, [PerDay2009-Dec31Totals_Q_XC].SumOfShares ORDER BY [PerDay2009-Dec31Totals_Q].[Full Name], [PerDay2009-Dec31Totals_Q_XC].Dates; TRANSFORM First([PerDay2009-Dec31Totals_Q_CXC].[SumOfShares]) & " -- " & [Tally] AS FirstOfSumOfShares SELECT [PerDay2009-Dec31Totals_Q_CXC].[Full Name] FROM [PerDay2009-Dec31Totals_Q_CXC] GROUP BY [PerDay2009-Dec31Totals_Q_CXC].[Full Name], [PerDay2009-Dec31Totals_Q_CXC].Tally PIVOT Format([Dates],"Short Date") & " -- with total"; -- Build a little, test a little. "Laura C." wrote: I have created a crosstab query that displays values for individuals across time as follows: Name TransactionDate1 TransactionDate2 Shareholder1 50 -10 Shareholder2 30 -10 Shareholder3 10 -5 I'm hoping that there is some way I can add a Row Heading that calculates totals for each Shareholder following each TransactionDate, like so: Name TD1 TotalAfterTD1 TD2 TotalAfterTD2 Shareholder1 50 50 -10 40 Shareholder2 30 30 -10 20 Shareholder3 10 10 -5 5 The transactions do not occur at regular intervals, which is why I have not been able to adapt any of the solutions I've found on this site for tallying totals, say, by month. I also tried using the query as the basis for a report so that I could create a Running Sum text box for this purpose, but was told that I had too many fields for the report. Again, I can't see how I would standardize the dates to yield fewer fields since the transactions don't occur at regular intervals. I've gotten the sense that I need to use the Dsum function. The following suggestion from John Vinson (adapted with my field names) seemed to be on the right track: =DSum([Date], [PerDay2009-Dec31Totals_Q], [Date] = #" & [Date] & "#) But I can't figure out how to insert the above into my existing query. Here is the SQL for my crosstab query: TRANSFORM Sum([PerDay2009-Dec31Totals_Q].Shares) AS SumOfShares SELECT [PerDay2009-Dec31Totals_Q].[Full Name], Sum([PerDay2009-Dec31Totals_Q].Shares) AS [Total Of Shares] FROM [PerDay2009-Dec31Totals_Q] GROUP BY [PerDay2009-Dec31Totals_Q].[Full Name] ORDER BY [PerDay2009-Dec31Totals_Q].Date PIVOT [PerDay2009-Dec31Totals_Q].Date; Any help you can offer would be most appreciated. |
#3
|
|||
|
|||
Crosstab Query - Recalculate totals after each date
Thank you, Karl, this is awesome!
Just one more tweak I'm hoping you can help with: Right now the "Value" cells in my crosstab query show the net change in shares followed by the running total ("Tally"), like so: "-50 -- 672." This is great! The tally only appears, however, when there IS a change. What I would like is for that tally to appear in every "Value" cell, even if there has been no action on a given transaction date for the shareholder. This means, I guess, that in cells with a null value, one would see something like "0 -- [Tally]." Thanks for lending your genius to an amateur's efforts. "KARL DEWEY" wrote: These queries include the running sum -- PerDay2009-Dec31Totals_Q_XC -- SELECT [PerDay2009-Dec31Totals_Q].[Full Name], [PerDay2009-Dec31Totals_Q].Dates, Sum([PerDay2009-Dec31Totals_Q].Shares) AS SumOfShares FROM [PerDay2009-Dec31Totals_Q] GROUP BY [PerDay2009-Dec31Totals_Q].[Full Name], [PerDay2009-Dec31Totals_Q].Dates; PerDay2009-Dec31Totals_Q_CXC -- SELECT [PerDay2009-Dec31Totals_Q].[Full Name], [PerDay2009-Dec31Totals_Q_XC].Dates, [PerDay2009-Dec31Totals_Q_XC].SumOfShares, Sum([PerDay2009-Dec31Totals_Q].Shares) AS Tally FROM [PerDay2009-Dec31Totals_Q] INNER JOIN [PerDay2009-Dec31Totals_Q_XC] ON [PerDay2009-Dec31Totals_Q].[Full Name] = [PerDay2009-Dec31Totals_Q_XC].[Full Name] WHERE ((([PerDay2009-Dec31Totals_Q].Dates)=[PerDay2009-Dec31Totals_Q_XC].[Dates])) GROUP BY [PerDay2009-Dec31Totals_Q].[Full Name], [PerDay2009-Dec31Totals_Q_XC].Dates, [PerDay2009-Dec31Totals_Q_XC].SumOfShares ORDER BY [PerDay2009-Dec31Totals_Q].[Full Name], [PerDay2009-Dec31Totals_Q_XC].Dates; TRANSFORM First([PerDay2009-Dec31Totals_Q_CXC].[SumOfShares]) & " -- " & [Tally] AS FirstOfSumOfShares SELECT [PerDay2009-Dec31Totals_Q_CXC].[Full Name] FROM [PerDay2009-Dec31Totals_Q_CXC] GROUP BY [PerDay2009-Dec31Totals_Q_CXC].[Full Name], [PerDay2009-Dec31Totals_Q_CXC].Tally PIVOT Format([Dates],"Short Date") & " -- with total"; -- Build a little, test a little. "Laura C." wrote: I have created a crosstab query that displays values for individuals across time as follows: Name TransactionDate1 TransactionDate2 Shareholder1 50 -10 Shareholder2 30 -10 Shareholder3 10 -5 I'm hoping that there is some way I can add a Row Heading that calculates totals for each Shareholder following each TransactionDate, like so: Name TD1 TotalAfterTD1 TD2 TotalAfterTD2 Shareholder1 50 50 -10 40 Shareholder2 30 30 -10 20 Shareholder3 10 10 -5 5 The transactions do not occur at regular intervals, which is why I have not been able to adapt any of the solutions I've found on this site for tallying totals, say, by month. I also tried using the query as the basis for a report so that I could create a Running Sum text box for this purpose, but was told that I had too many fields for the report. Again, I can't see how I would standardize the dates to yield fewer fields since the transactions don't occur at regular intervals. I've gotten the sense that I need to use the Dsum function. The following suggestion from John Vinson (adapted with my field names) seemed to be on the right track: =DSum([Date], [PerDay2009-Dec31Totals_Q], [Date] = #" & [Date] & "#) But I can't figure out how to insert the above into my existing query. Here is the SQL for my crosstab query: TRANSFORM Sum([PerDay2009-Dec31Totals_Q].Shares) AS SumOfShares SELECT [PerDay2009-Dec31Totals_Q].[Full Name], Sum([PerDay2009-Dec31Totals_Q].Shares) AS [Total Of Shares] FROM [PerDay2009-Dec31Totals_Q] GROUP BY [PerDay2009-Dec31Totals_Q].[Full Name] ORDER BY [PerDay2009-Dec31Totals_Q].Date PIVOT [PerDay2009-Dec31Totals_Q].Date; Any help you can offer would be most appreciated. |
Thread Tools | |
Display Modes | |
|
|