
November 26th, 2004, 06:24 AM
|
|
Registered User
|
|
Join Date: Nov 2004
Posts: 1
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
SQl Union Aggregate Pb
Hi, I have an access union that is being used as a string a couple of times. I want to aggregate just a part of the query, and havent been having any luck. Is it possible to do this.
Basically i would like to have a Grouped sum of the following values i.e
Select Sum([Extended Cost LC]) AS..., Sum([Extended Cost $]) AS..., ICIS.[ICIS Backup], (
SELECT ICIS.MonthNo, ICIS.[ICIS Backup], ICIS.Acct, Sum(Round([GBP],2)) AS [Extended Cost LC], Sum(Round([USD],2)) AS [Extended Cost $], ICIS.[Journal Source], ICIS.[File Link] FROM ICIS INNER JOIN 115 ON ICIS.[ICIS Link] = [115].[ICIS Inv #] GROUP BY ICIS.MonthNo, ICIS.[ICIS Backup], ICIS.Acct, ICIS.[Journal Source], ICIS.[File Link] HAVING (((ICIS.MonthNo)=7 Or (ICIS.MonthNo)=8 Or (ICIS.MonthNo)=9) AND ((ICIS.[ICIS Backup])=115) AND ((ICIS.Acct)=746000) AND ((ICIS.[Journal Source])='ICIS'))
UNION
SELECT ICIS.MonthNo, ICIS.[ICIS Backup], ICIS.Acct, Round(Sum([150].[Extended Cost LC]),2) AS [Extended Cost LC], Round(Sum([150].[Extended Cost $]),2) AS [Extended Cost $], ICIS.[Journal Source], ICIS.[File Link] FROM ICIS INNER JOIN 150 ON ICIS.[ICIS Link] = [150].[ICIS Inv #] GROUP BY ICIS.MonthNo, ICIS.[ICIS Backup], ICIS.Acct, ICIS.[Journal Source], ICIS.[File Link] HAVING (((ICIS.MonthNo)=7 Or (ICIS.MonthNo)=8 Or (ICIS.MonthNo)=9)
AND ((ICIS.[ICIS Backup])=150) AND ((ICIS.Acct)=746000) AND ((ICIS.[Journal Source])='ICIS'))
UNION
SELECT ICIS.MonthNo, ICIS.[ICIS Backup], ICIS.Acct, Round(([Net Local]),2) AS [Extended Cost LC], Round(([Total Dollar Value]),2) AS [Extended Cost $], ICIS.[Journal Source], ICIS.[File Link] FROM ICIS WHERE (((ICIS.MonthNo)=7 Or (ICIS.MonthNo)=8 Or (ICIS.MonthNo)=9) AND ((ICIS.Acct)=746000) AND ((ICIS.[Journal Source])='Manual') AND ((ICIS.Ent)=710) AND ((ICIS.[ICIS Link])=999999)) Group BY ICIS.[ICIS Backup] )
My Backup data point is my problem.
Thanks in advance
|