Posted on 1998-08-06
I have a situation where I take data from my access query and send it to an excel spreadsheet to populate a chart.
I have about 25 or 30 sql statements, I am having problems with calculations on one. Here is the statement:
dim vw as rs
dim wb as workbook
Dim grosslossanr2sql As String
grosslossanr2sql = "SELECT ([gross loss])*12/AVG([AVG O/S])FROM QUERY1 WHERE [CLIENT NAME]='client name' AND [YEAR]='1998'"
Set wb = GetObject("c:\WINDOWS\desktop\performance\DASH.XLS")
Set VW = CurrentDb.OpenRecordset(grosslossanr2sql)
Anyway the formula needs to be: GrossLosses*12/average(avg o/s)
I have query1 set up by month and year. [avg o/s] needs to be a YTD average. basicaly I am not averaging the Gross Loss, I need to leave it what ever number it is for that month multiplied by 12, but the Avg O/S field needs to be a YTD Average. How do I fix this?