a running sum (by date) in a query?

  • Thread starter Thread starter grantschneider
  • Start date Start date
G

grantschneider

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

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.
 
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
 
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
--
Email address is not valid.
Please reply to newsgroup only.







- Show quoted text -
Click to expand...



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
 
Back
Top