SQL Statement Doubt

[super_nOOb]

Hey guys,

I was trying to set up a SQL statement, but im still running into a few doubts.
Basically i have this select case:

Select Case True
                Case rbFirstQuarter.Checked
                    mindate = "01/01/" & cbYear.Text & ""
                    maxdate = "03/31/" & cbYear.Text & ""
                Case rdSecondQuarter.Checked
                    mindate = "04/01/" & cbYear.Text & ""
                    maxdate = "06/30/" & cbYear.Text & ""
                Case rbThridQuarter.Checked
                    mindate = "07/01/" & cbYear.Text & ""
                    maxdate = "09/30/" & cbYear.Text & ""
                Case rbFourthQuarter.Checked
                    mindate = "10/01/" & cbYear.Text & ""
                    maxdate = "12/31/" & cbYear.Text & ""
            End Select

I set up a SQL statement to get me the monthly averages of the database which looks like this:

SELECT [Year], [Month], AVG(prod_data.val) AS AverageAmount
FROM [SELECT YEAR(prod_data.valdate) AS [Year],
             MONTH(prod_data.valdate) AS [Month],
            prod_data.val
      FROM prod_data]
GROUP BY [Year], [Month];

But this statement is returning me the average of every year/month in the database. And i wanted it to return me only of the 3 months inside of the select case.

For example, in Case rbFirstQuarter.Checked
i wanted the average of Jan, Feb and March for the year selected in cbYear.Text

Then i wanted to make a muliplication of each of the 3 month with the equivalent from this SQL statement

SELECT DISTINCT [reference], SUM([Shares]) AS [POSITION]
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #" & maxdate & "# And pos_data.reference='client1'
GROUP BY [reference];

But this SQL statement still need to be fixed too. I wanted it to return me the sum from #12/31/2001# to jan, the sum to feb and the sum to march.

Then after making the multiplication of both queries i would have 3 values: 1 for jan, 1 for feb and 1 for march.

Then i would need to get the AVG from those 3.

Does anyone know how can i do that

thx a lot in advance

[FunkyDexter]

I haven't looked at your second statement yet but the problem with the first one is that you have no where clause so it'll return every year. Just add something like this before the group by:-

 Where [Year] = '2007'

I'm not sure how you'll get the value '2007' into the sql statement, that depends on how your building the statement.

Actually, thinking about it, you should probably add the where clause to the inner select, just after the From prod_data line, that will minimise the data the inner select returns and give a better performance.

[super_nOOb]

@ FunkyDexter - many thanks for the reply. Yes you are right about the missing WHERE clause. I actually had tested a few things with it before without a good result. The thing is that i needed something like 6 between clauses to get me the monthly Average of the 3 month pairs (which are the quarters). This is what has been my major dificulty in the 1st SQL statement. Because i wanted only A monthly average for the selected quarter.

I trid to put the where after the prod_data and also before the group by, but my SQL didnt run. Aparently im getting an error.........

[super_nOOb]

I could try something like this:

SELECT [Year], [Month], AVG(prod_data.val) AS AverageAmount
FROM [SELECT YEAR(prod_data.valdate) AS [Year],
             MONTH(prod_data.valdate) AS [Month],
            prod_data.val
      FROM prod_data 
     WHERE  [Year] = '2006' And  [Month] between  1 and 3 ]
GROUP BY [Year], [Month];

Couldnt I?

[FunkyDexter]

I'm not sure I'm understanding your question properly.

Are you asking for the quarterly average for each quarter in a year? If so then the query you suggested is correct but remove the between 1 & 3 bit. The inner query will return all the rows for the current year and the Avg and Group By in the outer query will sort them into querters and give you the average for the quarter.

Or are you asking for the monthly averages for each quarter in a year, i.e. Jan = 1, Feb = 2, Mar = 3 so the monthly average for quarter 1 is 2 - and you'd want to return 4 rows, one for each quarter. If so that's going to be a bit more complicated. You could do it like this:-
First get the monthly average for each quarter

SELECT AVG(prod_data.val) 
FROM prod_data 
WHERE  [Year] = '2006' And  [Month] between  1 and 3

write one of the above queries for each quarter.

Then you can select the results directly by writing each of the four queries as values in a main select statement:-

SELECT (SELECT AVG(prod_data.val)...between  1 and 3),
               (SELECT AVG(prod_data.val)...between  4 and 6),
               (SELECT AVG(prod_data.val)...between  7 and 9),
               (SELECT AVG(prod_data.val)...between  10 and 12))

There may be a better way to do this but I can't think of one off the top of my head.

[super_nOOb]

thx again for the reply. Actually what i need is not the warterly average. What i need is the monthly average for the quarter. For example, in Q1 I need the monthly average of JAN, FEB and MAR. For Q2 I need the average of APR, MAY, JUN....and so forth.

EDIT. So for each quarter i need to return 3 values, one for each month of the quarter

[FunkyDexter]

Got you. what you need is this:-

SELECT 
    (SELECT AVG(prod_data.val) FROM prod_data WHERE  [Year] = '2006' And  [Month] between  1 and 3) as quart1avg,
    (SELECT AVG(prod_data.val) FROM prod_data WHERE  [Year] = '2006' And  [Month] between  4 and 6) as quart2avg,
    (SELECT AVG(prod_data.val) FROM prod_data WHERE  [Year] = '2006' And  [Month] between  7 and 9) as quart3avg,
    (SELECT AVG(prod_data.val) FROM prod_data WHERE  [Year] = '2006' And  [Month] between  10 and 12) as quart4avg

the way it works is that each of the bracketted selects returns a single value representing the monthly average for the quarter. The main select then arranges them on a single row for you.

[edit]Oops, I put some brackets in the wrong places but have fixed them now.

[super_nOOb]

Thx for the tips.

I'm trying to run the code and im getting a reserved error (-3025)

Do you know what might be wrong. Also about the SQL i thing its still not exactally what i want. I think what i need is:
If its Q1: AVG JAN, AVG, FEB and AVG MAR
If its Q2: AVG APR, AVG MAY, AVG JUN
if its Q3: AVG JUL, AVG AGO, AVG SEP
if its Q4: AVG OCT, AVG NOV, AVG, DEC

And the quarter are selected by a radio button, while the year is selected by a combobox.

[FunkyDexter]

Oh, I see.

First of all, your reserved error is probably something to do with using field names like Year, Month and Val. I'm not sure but I think you'll find all of these are reserved words. To use them you must wrap them in square brackets (you're doing this already with Year and Month but not Val), or, even better, DON'T use them. Rename your fields to something else, like prd_val for example.

I think the SQL you want is similar to what I'd duggested but we need to change it slightly. The inner queries will become

SELECT AVG(val) FROM prod_data
WHERE [year] = '2006'
AND [month] = 1

you'll need to get the correct values for year and month into the statement. If you're calling theis from VB I'd suggest you build the sql string in VB and substitute the correct values in as you build it. If you need to select the values in SQL itself (e.g. if you're writing a sproc or creatin an query directly in access) then you're going to need to investigate the wonders of IIF or CASE statements (I think you're using access in which case it's IIF you're after).

Build a select statment like the above for each month in the quarter and then use the same trick as before to select each value onto the same line:-

SELECT
   (SELECT AVG(val) FROM prod_data WHERE [year] = '2006'AND [month] = 1) as JanAvg,
   (SELECT AVG(val) FROM prod_data WHERE [year] = '2006'AND [month] = 2) as FebAvg,
   (SELECT AVG(val) FROM prod_data WHERE [year] = '2006'AND [month] = 3) as marAvg

[super_nOOb]

thx again for the help. Actually i dont have field names of Year and Month. My Date field name is called prod_data.valdate. I named the new field names that the 1st query qould return me as Year and Month using the reserved words.

Actually the idea is indeed to replace the months and years by string variables in VB.NET. But i was trying to find out how the SQL statement would look like 1st.

About the Month, year i think i should "extract" them from the original date field.

[super_nOOb]

maybe i should do something like this

SELECT
   (SELECT AVG(prod_data.val) FROM prod_data WHERE YEAR(prod_data.valdate) = '2006' AND MONTH(prod_data.valdate) = 1) as JanAvg,
   (SELECT AVG(prod_data.val) FROM prod_data WHERE YEAR(prod_data.valdate) = '2006' AND MONTH(prod_data.valdate) = 2) as FebAvg,
   (SELECT AVG(prod_data.val) FROM prod_data WHERE YEAR(prod_data.valdate) = '2006' AND MONTH(prod_data.valdate) = 3) as marAvg

EDIT:
With this code im getting the AVG of each month in one column

SELECT AVG(prod_data.val)  FROM prod_data WHERE YEAR(prod_data.valdate) = '2006' AND MONTH(prod_data.valdate) = 1 
UNION ALL
SELECT AVG(prod_data.val)  FROM prod_data WHERE YEAR(prod_data.valdate) = '2006' AND MONTH(prod_data.valdate) = 2 
UNION ALL
SELECT AVG(prod_data.val)  FROM prod_data WHERE YEAR(prod_data.valdate) = '2006' AND MONTH(prod_data.valdate) = 3 
;

Now i just wanted to add a new field with month,year

HOw can i do that

[FunkyDexter]

Yep, that's it That should be fine.

Your reserved problem is probably to do with your use of Val, stick that in square brackets and see if the problem dissapears.

Also, now that I understand the problem a bit better, there is a more efficient way of doing this:-

SELECT AVG(val)
FROM prod_data
WHERE MONTH(valdate) BETWEEN 1 AND 3
AND YEAR(valdate) = 2006
GROUP BY month
ORDER BY month

The only thing is that that will return you three different rows rather than having them all on the same row and you won't be able to give the individual values aliases. Though you could also select the month and year to help you identify which row is which.

[super_nOOb]

@FunkyDexter - Thanks a lot. Im trying the last code you poseted:

SELECT AVG(prod_data.val)
FROM prod_data
WHERE MONTH(prod_data.valdate) BETWEEN 1 AND 3
AND YEAR(prod_data.valdate) = 2006
GROUP BY month
ORDER BY month;

And its asking me for the parameters for month
BTW i edited the last post with a code that is working now

[super_nOOb]

The last code works fine with this modifications :

SELECT AVG(prod_data.val) AS MonthlyAVG
FROM prod_data
WHERE YEAR(prod_data.valdate) = '2006' AND MONTH(prod_data.valdate) BETWEEN 1 AND 3
GROUP BY MONTH(prod_data.valdate);

EDIT: Now Basically i have a output that looks like this:

MonthlyAVG
103.665806451613
103.76375
105.364782608696

Now i needed to multply each one of this results with the ones from the second query i posted in the 1st post. As soon as i find out something ill post here

Many many thx again

EDIT 2: My 2nd Statement is still a bit bugged. Im only being able to manage the sum from a 1st date and a last one. I'll try to find out how to mange the same monthly concept i did for the 1st SQL statement

[FunkyDexter]

OK, I think I can see what you're doing. It's a bit 'orrible so give me a minute.

[super_nOOb]

The second SQL should be something like this:

SELECT DISTINCT [reference], SUM([Shares]) AS [POSITION]
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #01/31/2006# And pos_data.reference='apollo'
GROUP BY [reference]
UNION ALL
SELECT DISTINCT [reference], SUM([Shares]) AS [POSITION]
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #02/28/2006# And pos_data.reference='apollo'
GROUP BY [reference]
UNION ALL
SELECT DISTINCT [reference], SUM([Shares]) AS [POSITION]
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #03/31/2006# And pos_data.reference='apollo'
GROUP BY [reference];

But i think that is a easier way out

I think this one is even better than the 1st

SELECT SUM([Shares]) AS [POSITION]
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #01/31/2006# And pos_data.reference='apollo'
GROUP BY [reference]
UNION ALL
SELECT SUM([Shares]) AS [POSITION]
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #02/28/2006# And pos_data.reference='apollo'
GROUP BY [reference]
UNION ALL
SELECT  SUM([Shares]) AS [POSITION]
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #03/31/2006# And pos_data.reference='apollo'
GROUP BY [reference];

Now i just needed to multiply the results of the 1st and 2nd query. But i wanted them all in a sigle SQL statement

[FunkyDexter]

I think something like this would do it:-

SELECT reference, month(valdate), 
   (SELECT AVG(prod_data.val) * SUM(shares) AS MonthlyAVG
   FROM prod_data
   INNER JOIN pos_data
      ON MONTH(valdate) = MONTH(period)
      AND YEAR(valdate) = YEAR(period)
   WHERE YEAR(valdate) = '2006' AND MONTH(valdate)BETWEEN 1 AND 3
   GROUP BY MONTH(prod_data.valdate))
GROUP BY reference, month(valdate)

Note the Group By on the inner select is a logically redundant because it's also covered by the Group By on the outer select. It should improve performance by limiting the number of rows returned by the inner select. that said, it might actually reduce performance by intrducing an extra operation so try it with and without to see which performs better.

[FunkyDexter]

Actually, I lied, it's not redundant because it's controlling the values returned by the aggregate functions. Also, the query would look better written like this:-

SELECT reference, month(valdate), 
   (SELECT AVG(prod_data.val) * SUM(shares)
   FROM prod_data
   INNER JOIN pos_data
      ON MONTH(valdate) = MONTH(period)
      AND YEAR(valdate) = YEAR(period)
   WHERE YEAR(valdate) = '2006' AND MONTH(valdate)BETWEEN 1 AND 3
   GROUP BY MONTH(valdate)) as monthly_value
GROUP BY reference, month(valdate)

[super_nOOb]

Ok. Im trying to run the code but i think i have a few concerns. 1st is something i just realised from the 1st query:
I think i need to INNER JOIN prod_data ON cd_prod.id = prod_data.cd_prodID so i query the right product. Im still working on this one.

About the 2nd query the sum must be from #12/31/2001# until the last date of each month of the quarter. And in this one i also need to tell which client it will be making the query of. Please notice the

pos_data.reference='apollo'

[FunkyDexter]

SELECT month(valdate), 
   (SELECT AVG(prod_data.val) * SUM(shares)
   FROM prod_data
   INNER JOIN pos_data
      ON MONTH(valdate) = MONTH(period)
      AND YEAR(valdate) = YEAR(period)
   INNER JOIN cd_prod
     ON cd_prod_id = id
   WHERE YEAR(valdate) = '2006'
   AND MONTH(valdate)BETWEEN 1 AND 3
   AND pos_data.reference = 'Apllo'
   GROUP BY MONTH(valdate)) as monthly_value
FROM prod_data
GROUP BY month(valdate)

Sounds like we're getting close . I led you on a bit of a wild goose chase earlier before I really nderstod what you were after. Sorry about that.

Please clarify what you mean about the dates. If your running from the last dat of the previous month to the last date of the current month INCLUSIVE, then and rows for the last day of a month will be counted twice, which doesn't sound right to me. Is that what you're after?

edit>>Something I got wrong in my previous post was that I forgot teh FROM in the outer select. FYI, you don't need to include all the tables in the outer select because you've already filtered outany values you don't want in the inner select and you're not selecting any values from those tables.

[super_nOOb]

No worries man. Im grateful for all the help so far.........about the dates:
The 1st query is just the average from the current month. In our example for jan would be from 01/01/2006 to 01/31/2006.
The 2nd query that is different. In that one i need the sum from the 1st date i have in my database until the last day of the month i want to look, for example: My database starts in #12/31/2001# . So i needed the sum of all the values in the database for the Distinct CLients until, in our case, Jan 06, Feb 06, Mar 06. So would be the sum from #12/31/2001# until #01/31/2006# for Jan 06, and so forth

[FunkyDexter]

Ouch , that's gonna start getting REALLY tricky. Hang on and I'll see if I can work it out.

[super_nOOb]

yup..........lol

Ok.........I'll try a few things here too.......if i manage to get anywhere ill post it here

[FunkyDexter]

OK, I think we need an inner inner select and it's going to be something like this but I'm sure I've got a few things wrong:-

SELECT month(valdate), 
   (SELECT AVG(prod_data.val) * 
      (SELECT SUM(shares)
      FROM pos_data
      WHERE period < #'MONTH(valdate)'/31/'YEAR(valdate)'#
      AND pos_data.reference = 'Apollo')
   FROM prod_data
   INNER JOIN cd_prod
     ON cd_prod_id = id
   WHERE YEAR(valdate) = '2006'
   AND MONTH(valdate)BETWEEN 1 AND 3
   GROUP BY MONTH(valdate)) as monthly_value
FROM prod_data
GROUP BY month(valdate)

Firstly, I'm 99% sure that this : WHERE period < #'MONTH(valdate)'/31/'YEAR(valdate)'# isn't valid but I don't really use access. A regular access user could probably tell you how to build up the date string

Secondly, I think you might need to do more to join pos_data to prod data in that inner inner select but I'm unclear how that relationship would work.



Sorry, I'm off home now but I'll look in tomorrow to see how you've got on.

[super_nOOb]

@FunkyDexter - Thx for all the help. Well, i've been trying to mess around each query individualy.
For query one:

 SELECT cd_prod.name, AVG(prod_data.val) AS MonthlyAVG
FROM cd_prod INNER JOIN prod_data ON cd_prod.id = prod_data.cd_prodID
WHERE YEAR(prod_data.valdate) = '2006' AND MONTH(prod_data.valdate) BETWEEN 1 AND 3
GROUP BY MONTH(prod_data.valdate), cd_prod.name;

I get this output:

 name	MonthlyAVG
product_EUR	108.6
product_USD	103.665806451613
product_EUR	109.76
product_USD	103.76375
product_EUR	109.44
product_USD	105.364782608696

And for the 2nd query:

SELECT [product], SUM([Shares]) AS [POSITION]
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #01/31/2006# And pos_data.reference='apollo'
GROUP BY [reference], [product]
UNION ALL
SELECT [product], SUM([Shares]) AS [POSITION]
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #02/28/2006# And pos_data.reference='apollo'
GROUP BY [reference], [product]
UNION ALL SELECT [product], SUM([Shares]) AS [POSITION]
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #03/31/2006#  And pos_data.reference='apollo'
GROUP BY [reference], [product];

I'm getting this output:

product	POSITION
product_USD	375.791
product_USD	375.791
product_USD	576.748

My concerns so far:
In query one, i get no date as output. It now has one result for each product i have for each month. In that case it has 2 results for jan/06 (one for product_USD and one for product_EUR)

What i wanted to get here is the average of the following:

Jan/06    product_USD     103.665806451613*375.791
Feb/06    product_USD     103.76375*375.791
MAR/06    product_USD     105.364782608696*576.748

EDIT:
This would return me:

Jan/06    product_USD     38956.67707
Feb/06    product_USD     38993.48338
MAR/06    product_USD     60768.92764

So the only output i want to see is the AVG(38956.67707,38993.48338,60768.92764) which would be: 46239.69603

So i would want a output like this:

Q1/2006   prod_USD    apollo      46239.69603

THe thing is i have to do all this process, cuz the average of a multiplication is diferent of a multiplication of a average

[FunkyDexter]

Morning SN

OK, I thought about this overnight and I think this is what you want:-

SELECT month(valdate), 
   (SELECT AVG(prod_data.val) * SUM(shares)
   FROM prod_data
   INNER JOIN cd_prod
     ON cd_prod_id = id
   INNER JOIN pos_data
     ON period < # & MONTH(valdate) & '/31/' & YEAR(valdate) & #
   WHERE YEAR(valdate) = '2006'
   AND MONTH(valdate)BETWEEN 1 AND 3
   AND pos_data.reference = 'Apollo')
   GROUP BY MONTH(valdate)) as monthly_value
FROM prod_data
GROUP BY month(valdate)

You also need to add an AND to the WHERE clause of the inner query to check that your're using the right product from the cd_prod table as per your previouspost but I'm not sure how you're doing that.

The period < # & MONTH(valdate) & '/31/' & YEAR(valdate) & # syntax is probably still wrong - an acess user should be able to correct that bit.

Finally, you might need to tighten up the join to the pos_data table. At the moment it'll count all records with a reference of 'Apollo' (or whatever value you use) that were before the end of the quarter month. I suspect there's some linking field between pos_data and prod_data that you need to include in the query (some kind of id field probably)

[super_nOOb]

Morning FunkyDexter

Ok. I'll give it a try. After i manage this SQL code its still gonna be more tricky, cuz i wanted to include it on this one:

SELECT cd_clients.name, avg(client_data.val) AS Average,cd_clients.Currency, cd_clients.VAT, cd_clients.mngmt_fee, cd_clients.Retrocession 
FROM cd_clients 
INNER JOIN client_data ON cd_clients.id = client_data.cd_clientID 
where client_data.valDate 
Between #" & mindate & "# And #" & maxdate & "# 
GROUP BY cd_clients.name,cd_clients.Currency,cd_clients.VAT,cd_clients.mngmt_fee, cd_clients.Retrocession

In a way that it would create a column for each product and return me the values of the clients for the respective product. >.<
Its not going to be very easy i think lol

[FunkyDexter]

Oh God, you're joking aren't you.

I'd get the product query working first and then try to bring the customer stuff into it once you're happy with it. Also, although doing all that in a single query might be the right thing to do from a performance point of view it might not be a good idea from a maintainability perspective. I pity the poor programmer who has to pick this up after you've gone.

[super_nOOb]

lol...........im not joking. Some crazy SQL stuff going on in this program isnt it?
szlamany from the forum suggested this:
EDIT:

SELECT G1.ProdName, G1.MonthPart, G1.MonthlyAvg, G2.ProdName, G2.MonthPart, G2.Position
FROM [SELECT cd_prod.name as ProdName
      , AVG(prod_data.val) AS MonthlyAVG
      ,MONTH(prod_data.valdate) as MonthPart
FROM cd_prod INNER JOIN prod_data ON cd_prod.id = prod_data.cd_prodID
WHERE YEAR(prod_data.valdate) = '2006' AND MONTH(prod_data.valdate) BETWEEN 1 AND 3
GROUP BY MONTH(prod_data.valdate), cd_prod.name]. AS G1 LEFT JOIN [SELECT [product] as ProdName, SUM([Shares]) AS [POSITION], MONTH(#01/31/2006#) as MonthPart
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #01/31/2006# And pos_data.reference='apollo'
GROUP BY [reference], [product]
UNION ALL
SELECT [product] as ProdName, SUM([Shares]) AS [POSITION], MONTH(#02/28/2006#) as MonthPart
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #02/28/2006# And pos_data.reference='apollo'
GROUP BY [reference], [product]
UNION ALL
SELECT [product] as ProdName, SUM([Shares]) AS [POSITION], MONTH(#03/31/2006#) as MonthPart
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #03/31/2006# And pos_data.reference='apollo'
GROUP BY [reference], [product]]. AS G2 ON (G2.MonthPart=G1.MonthPart) AND (G2.ProdName=G1.ProdName);

the output is:

G1.ProdName	G1.MonthPart	MonthlyAvg	G2.ProdName	G2.MonthPart	Position
product_EUR	1	103.665806451613			
product_USD	1	103.665806451613	product_USD	1	375.791
product_EUR	2	103.76375			
product_USD	2	103.76375	         product_USD	2	375.791
product_EUR	3	105.364782608696			
product_USD	3	105.364782608696	product_USD	3	576.748

This is working great. Now i have to try to find out how to multiply them and then make the average of the final output. Then try to find on a good solution on how to put this all on that las query.........uffffffffffffffff

[super_nOOb]

OK
Problem solved with help of szlamany

Select G1.ProdName, AVG(G1.MonthlyAvg*G2.Position) AS Multiplication
   From (SELECT cd_prod.name as ProdName
      , AVG(prod_data.val) AS MonthlyAVG
      ,MONTH(prod_data.valdate) as MonthPart
FROM cd_prod INNER JOIN prod_data ON cd_prod.id = prod_data.cd_prodID
WHERE YEAR(prod_data.valdate) = '2006' AND MONTH(prod_data.valdate) BETWEEN 10 AND 12
GROUP BY MONTH(prod_data.valdate), cd_prod.name) as G1
   Left Join (SELECT [product] as ProdName, SUM([Shares]) AS [POSITION], MONTH(#10/31/2006#) as MonthPart
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #10/31/2006# And pos_data.reference='apollo'
GROUP BY [reference], [product]
UNION ALL
SELECT [product] as ProdName, SUM([Shares]) AS [POSITION], MONTH(#11/30/2006#) as MonthPart
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #11/30/2006# And pos_data.reference='apollo'
GROUP BY [reference], [product]
UNION ALL
SELECT [product] as ProdName, SUM([Shares]) AS [POSITION], MONTH(#12/31/2006#) as MonthPart
FROM pos_data
WHERE pos_data.period Between #12/31/2001# And #12/31/2006# And pos_data.reference='apollo'
GROUP BY [reference], [product]) as G2 on G2.ProdName=G1.ProdName AND G2.MonthPart=G1.MonthPart Group by  G1.ProdName

Output:

ProdName	Multiplication
product_EUR	
product_USD	160875.641732987

NOw I need to put it together with this query:

SELECT cd_clients.name, avg(client_data.val) AS Average,cd_clients.Currency, cd_clients.VAT, cd_clients.mngmt_fee, cd_clients.Retrocession FROM cd_clients INNER JOIN client_data ON cd_clients.id = client_data.cd_clientID where client_data.valDate Between #" & mindate & "# And #" & maxdate & "# GROUP BY cd_clients.name,cd_clients.Currency,cd_clients.VAT,cd_clients.mngmt_fee, cd_clients.Retrocession