Crosstab Query - Recalculate totals after each date

L

Laura C.

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.
 
K

KARL DEWEY

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. said:
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.
Click to expand...
 
L

Laura C.

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 said:
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. said:
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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

crosstab query? 12
Data totals by date 1
Duplicate prompts in crosstab query and subsequent report 1
Need to create totals using a Crosstab Query 3
Crosstab Query and Specific Date Ranges 6
Crosstab query error 1
row/column totals in crosstab query 1
string crosstab query 1

Top