a running sum (by date) in a query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

grantschneider@gmail.com - 24 Jul 2007 16:54 GMT

Is this possible? I have dates (some of which are the same) and I
would like to use them to create a running sum of another column in my
query. Is this possible?

Reply to this Message

grantschneider@gmail.com - 24 Jul 2007 17:12 GMT

On Jul 24, 4:54 pm, grantschnei...@gmail.com wrote:

> Is this possible? I have dates (some of which are the same) and I
> would like to use them to create a running sum of another column in my
> query. Is this possible?

Sorry, not only do i need to create a running sum by date but it also
has to be PER sales person. So even though all the sales people are in
one table and I am querying the table different ways, I need to make
sure I am calculating the quantity of sales person person as a running
sum.

hope this makes sense.

Reply to this Message

Dale Fye - 24 Jul 2007 17:32 GMT

Grant,

Yes, it is possible to create a running sum, but we really need a little
more info about your table structure. It would help if you would give us the
field names in your table, a small sample of the applicable fields, and what
you would like the output of the query to look like.

Dale

Signature

Email address is not valid.
Please reply to newsgroup only.

> On Jul 24, 4:54 pm, grantschnei...@gmail.com wrote:
> > Is this possible? I have dates (some of which are the same) and I
[quoted text clipped - 8 lines]
>
> hope this makes sense.

Reply to this Message

grantschneider@gmail.com - 24 Jul 2007 17:58 GMT

> Grant,
>
[quoted text clipped - 23 lines]
>
> - Show quoted text -

Here is my SQL code:

PARAMETERS [Forms]![SVP]![SVP] Text ( 255 ), [Forms]![SVP]![Month]
Text ( 255 );
SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, [Master Sales Forecast].[Share Total], [Master
Sales Forecast].SVP, IIf([master sales forecast].account="Renewal +ve",
[Agg]*0.5*Forms!SVP![Renewal Multiple],0) AS Renewal, IIf([master
sales forecast].account="Referral",[Agg]*Forms!SVP![Referral Multiple],
0) AS Referral, IIf([master sales forecast].account="add-on",
[Agg]*Forms!SVP![Add-on Multiple],0) AS [Add-on], IIf([master sales
forecast].account="Graduate",[Agg]*Forms!SVP![Graduate Multiple],0) AS
Graduate, IIf([master sales forecast].account="New Sale",[Agg]*Forms!
SVP![New Sale Multiple],0) AS [New Sale], [Master Sales Forecast].VP,
[Share Total]*Forms!SVP![Agg Multiple]*[Points] AS Agg, [graduate]+
[Renewal]+[Referral]+[new sale]+[add-on] AS Total, [Master Sales
Forecast].Month, MonthConversion.Date
FROM ([Master Sales Forecast] INNER JOIN Points ON [Master Sales
Forecast].Aircraft = Points.Aircraft) LEFT JOIN MonthConversion ON
[Master Sales Forecast].Month = MonthConversion.Month
WHERE ((([Master Sales Forecast].Account)<>"card adjustment" And
([Master Sales Forecast].Account)<>"renewal -ve" And ([Master Sales
Forecast].Account)<>"churn" And ([Master Sales
Forecast].Account)<>"upgrade/downgrade" And ([Master Sales
Forecast].Account)<>"share reduction") AND (([Master Sales
Forecast].SVP) Like ([Forms]![SVP]![SVP]) & "*") AND (([Master Sales
Forecast].Month) Like ([Forms]![SVP]![Month] & "*")));

What I am looking to do is create a running sum of the [Share Total]
for each [SVP] by Date.

Disregard the many commission columns, they will be irrelevant to the
running sum.

Eventually, I am trying to find out when each SVP hits a certain
amount of shares sold so that they then go into an 'accelerated'
commission. For every month thereafter they receive a higher
commission percentage.

Thanks
Grant

Reply to this Message