Solved

SQL Problem

Posted on 1998-08-06
15
210 Views
Last Modified: 2008-02-01
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)

.Sheets("sheet1").Range("Aj6:Aj17").CopyFromRecordset VW



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?





0
Comment
Question by:windows1
  • 8
  • 4
  • 3
15 Comments
 
LVL 5

Expert Comment

by:tuvi
ID: 1958291
I am still confused:

>>> So if it is June, Avg o/s needs to be an average of jan, feb,march,april, may and june.  <<<

>>> but the Avg O/S field needs to be a YTD Average <<<

What do you mean?
I need more info? In your table, is there a month field? Also, the [avg o/s] field in your table, is it YTD for that month or it is just for that month?


0
 
LVL 5

Expert Comment

by:tuvi
ID: 1958292
The formula:

GrossLosses*12/average(avg o/s)

Let's say it is June you want. So, you need [avg o/s] to be YTD up to June?
0
 

Author Comment

by:windows1
ID: 1958293
Edited text of question
0
 

Author Comment

by:windows1
ID: 1958294
I edited the question, I had a typo sorry.

The [avg o/s] needs to be ytd as of the last month in the dataset for that year.  But it needs to know the difference, since I am deleting and re-importing all the data for 97 and 98 each month.
so when It comes up with an average of [avg 0/s] for january it will be for 1 month, for february it will be an average of jan and feb, for march it will be for jan, feb and march.

David
David


0
 

Author Comment

by:windows1
ID: 1958295
Edited text of question
0
 
LVL 17

Expert Comment

by:ramrom
ID: 1958296
One way is to replace [AVG O/S] with:
DAvg("[AVG O/S]", "QUERY1", "WHERE [CLIENT NAME]='client name' AND [YEAR]='1998'")

0
 

Author Comment

by:windows1
ID: 1958297
It is giving syntax errors, I replaced it with this, what is wrong with it?

grosslossanr2sql = "SELECT ([gross loss])*12" / DAvg("[AVG O/S]", "QUERY1", "WHERE [CLIENT NAME]='client name' AND [YEAR]='1998'")
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 17

Expert Comment

by:ramrom
ID: 1958298
<b>
Sorry; I forgot that your SQL was being constructed as a literal. You should replace each " with a pair of ""
</b>
0
 

Author Comment

by:windows1
ID: 1958299
Thanks for your help, I think I am getting there but I still get an error.  when I execute this, I get an error message saying that a reserved word is missing or in error.

grosslossanr1sql = "SELECT ([gross loss])*12 / DAvg(""[AVG O/S]"", ""QUERY1"", ""WHERE [CLIENT NAME]='client name' AND [YEAR]='1998'"")"

any other ideas?
0
 

Author Comment

by:windows1
ID: 1958300
Adjusted points to 100
0
 
LVL 5

Expert Comment

by:tuvi
ID: 1958301
I think your problem is not the syntax but the formula itself. I still don't get it, still vague about what you want. In other words, what the formula means.

Each of your record there is a [gross loss] and [avg 0/s] YTD. Then what does [gross loss]*12 means?
0
 

Author Comment

by:windows1
ID: 1958302
Lets put it this way, it is not the formula. The *12 multiplies it times 12 months.
 If I use this sql:

grosslossanr1sql = "SELECT ([gross loss])*12/[AVG O/S] FROM QUERY1 WHERE [CLIENT NAME]='client name' AND [YEAR]='1998'"

it works fine.

if I type in:grosslossanr1sql = "SELECT AVG([gross loss])*12/AVG[AVG O/S] FROM QUERY1 WHERE [CLIENT NAME]='client name' AND [YEAR]='1998'"

ALSO WORKS FINE (except I only get an overall average instead of a different average for each month)




The occurs when I try to AVERAGE OR SUM ONE FIELD AND I DON'T TRY TO AVERAGE OR SUM THE OTHER. such as this:

"SELECT ([gross loss])*12/AVG[AVG O/S] FROM QUERY1 WHERE [CLIENT NAME]='client name' AND [YEAR]='1998'"

Thanks
0
 
LVL 17

Accepted Solution

by:
ramrom earned 100 total points
ID: 1958303
Sorry :-( for the above failure. I tested the following and it seems to work:

"SELECT ([gross loss])*12/DAvg(""[AVG O/S]"", ""query1"", ""[CLIENT NAME]='client name' AND [YEAR]='1998'"") as expr1 FROM query1 WHERE [CLIENT NAME]='client name' AND [YEAR]='1998'"
0
 
LVL 17

Expert Comment

by:ramrom
ID: 1958304
"The occurs when I try to AVERAGE OR SUM ONE FIELD AND I DON'T TRY TO AVERAGE OR SUM THE OTHER." True - if you use a domain aggregate function (such as AVG()) you are creating a totals query. Each item in the select clause must either be either a domain aggregate function or in the group by clause.
0
 

Author Comment

by:windows1
ID: 1958305
Ramrom Thanks I appreciate your answer, It seems to work much better, for somereason the calculations are slightly different than what they should be, for example, 1.94 instead of 1.99  9.79 instead of 9.91. Only one is exact. they are rounded exactly, ??

Thanks!  If you have any suggestions why the numbers are slightly different I appreciate it, but you answered my question!

David
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms/access ftp / SFTP 3 32
DSum for Access 6 43
data analyst 3 48
Getting Run-Time Error 13 - Type Mismatch 3 25
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now