Results 1 to 1 of 1

Thread: Help

  1. #1
    Join Date
    Jul 2003
    Location
    Rhode Island
    Posts
    3

    Red face Unanswered: Help

    I am attempting to Join the results of two quries to compute a budget variance from Oracle tables using an Access fron-end and report. The Quesries are as follows:

    Budget (two versions an original and approved) This query returns a single row for each fiscal period within a fiscal year for each budgeted amount

    SELECT Sum(IIf([Budget_Number]=1,[Budget_Amount],0)) AS Budget, Sum(IIf([Budget_Number]=2,[Budget_Amount],0)) AS Approved, Budget.Activity_Group, Budget.[Fiscal Year], Budget.Period
    FROM Budget
    GROUP BY Budget.Activity_Group, Budget.[Fiscal Year], Budget.Period;

    Actuals:
    Has many rows summed by fical year, period and project.

    SELECT [Actuals].[Fiscal Year], [Actuals].[Period], [Actuals].[Activity_Group], sum([Actuals].[Actual_ Amount]) AS Actual_Amt
    FROM Actuals
    GROUP BY [Actuals].[Fiscal Year], [Actuals].[Period], [Actuals].[Activity_Group];

    I need to join the two tables to compute the variance, any help would be appreciated.
    Thanks,


    Solved my own issue, thanks anyway...
    Resolution:

    SELECT Actuals.[Fiscal Year], Actuals.Period, Actuals.Activity_Group, Sum(Actuals.[Actual_ Amount]) AS Actual_Amt, [%$##@_Alias].Budget, [%$##@_Alias].Approved
    FROM Actuals LEFT JOIN [SELECT Sum(IIf([Budget_Number]=1,[Budget_Amount],0)) AS Budget, Sum(IIf([Budget_Number]=2,[Budget_Amount],0)) AS Approved, Budget.Activity_Group, Budget.[Fiscal Year], Budget.Period
    FROM Budget
    GROUP BY Budget.Activity_Group, Budget.[Fiscal Year], Budget.Period]. AS [%$##@_Alias] ON (Actuals.Activity_Group = [%$##@_Alias].Activity_Group) AND (Actuals.Period = [%$##@_Alias].Period) AND (Actuals.[Fiscal Year] = [%$##@_Alias].[Fiscal Year])
    GROUP BY Actuals.[Fiscal Year], Actuals.Period, Actuals.Activity_Group, [%$##@_Alias].Budget, [%$##@_Alias].Approved
    HAVING (((Actuals.[Fiscal Year])=[Enter Fiscal Year]) AND ((Actuals.Period) Between [Enter Period 1] And [Enter Period 2]));

    Mike
    Last edited by micazzi; 07-27-03 at 13:01.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •