Expression Problems in Query

[Stacey Crowhurst]

Hi. I am trying to create a query that adds (Approved Budget) + (Revised
Budget) to get (Current Budget). Here is my SQL:

SELECT [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc], (Sum([Query - Cash Flow_Budgets]![Line
Amount]+[Query - Cash Flow_Revised Budgets]![Line Amount])) AS [Current
Budget]
FROM [Query - Cash Flow_Budgets] INNER JOIN [Query - Cash Flow_Revised
Budgets] ON [Query - Cash Flow_Budgets].[Budget ID] = [Query - Cash
Flow_Revised Budgets].[Budget ID]
GROUP BY [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc];

When I have the group by selected it shows me no data. When I remove group
by I get the aggregate function error. It seems like such a simple
calculation, I don't know where I went wrong. Help please!!!

Thanks,
Stacey

 

[KARL DEWEY]

Try this --
FROM [Query - Cash Flow_Budgets] LEFT JOIN [Query - Cash Flow_Revised
Budgets] ON [Query - Cash Flow_Budgets].[Budget ID] = [Query - Cash
Flow_Revised Budgets].[Budget ID]

 

[Stacey Crowhurst]

The query runs and shows the rows I expect, but the current budget column is
empty.

Try this --
FROM [Query - Cash Flow_Budgets] LEFT JOIN [Query - Cash Flow_Revised
Budgets] ON [Query - Cash Flow_Budgets].[Budget ID] = [Query - Cash
Flow_Revised Budgets].[Budget ID]

--
KARL DEWEY
Build a little - Test a little

Hi. I am trying to create a query that adds (Approved Budget) + (Revised
Budget) to get (Current Budget). Here is my SQL:

SELECT [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc], (Sum([Query - Cash Flow_Budgets]![Line
Amount]+[Query - Cash Flow_Revised Budgets]![Line Amount])) AS [Current
Budget]
FROM [Query - Cash Flow_Budgets] INNER JOIN [Query - Cash Flow_Revised
Budgets] ON [Query - Cash Flow_Budgets].[Budget ID] = [Query - Cash
Flow_Revised Budgets].[Budget ID]
GROUP BY [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc];

When I have the group by selected it shows me no data. When I remove group
by I get the aggregate function error. It seems like such a simple
calculation, I don't know where I went wrong. Help please!!!

Thanks,
Stacey

 

[KARL DEWEY]

Try it with the Nz function added --
SELECT [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc], (Sum([Query - Cash Flow_Budgets]![Line
Amount]+Nz([Query - Cash Flow_Revised Budgets]![Line Amount]))) AS [Current
Budget]
FROM [Query - Cash Flow_Budgets] LEFT JOIN [Query - Cash Flow_Revised
Budgets] ON[Query - Cash Flow_Budgets].[Budget ID] = [Query - Cash
Flow_Revised Budgets].[Budget ID]
GROUP BY [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc];

--
KARL DEWEY
Build a little - Test a little

The query runs and shows the rows I expect, but the current budget column is
empty.

Try this --
FROM [Query - Cash Flow_Budgets] LEFT JOIN [Query - Cash Flow_Revised
Budgets] ON [Query - Cash Flow_Budgets].[Budget ID] = [Query - Cash
Flow_Revised Budgets].[Budget ID]

--
KARL DEWEY
Build a little - Test a little

Hi. I am trying to create a query that adds (Approved Budget) + (Revised
Budget) to get (Current Budget). Here is my SQL:

SELECT [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc], (Sum([Query - Cash Flow_Budgets]![Line
Amount]+[Query - Cash Flow_Revised Budgets]![Line Amount])) AS [Current
Budget]
FROM [Query - Cash Flow_Budgets] INNER JOIN [Query - Cash Flow_Revised
Budgets] ON [Query - Cash Flow_Budgets].[Budget ID] = [Query - Cash
Flow_Revised Budgets].[Budget ID]
GROUP BY [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc];

When I have the group by selected it shows me no data. When I remove group
by I get the aggregate function error. It seems like such a simple
calculation, I don't know where I went wrong. Help please!!!

Thanks,
Stacey

 

[Stacey Crowhurst]

Yup! Thanks so much for your help

Try it with the Nz function added --
SELECT [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc], (Sum([Query - Cash Flow_Budgets]![Line
Amount]+Nz([Query - Cash Flow_Revised Budgets]![Line Amount]))) AS [Current
Budget]
FROM [Query - Cash Flow_Budgets] LEFT JOIN [Query - Cash Flow_Revised
Budgets] ON[Query - Cash Flow_Budgets].[Budget ID] = [Query - Cash
Flow_Revised Budgets].[Budget ID]
GROUP BY [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc];

--
KARL DEWEY
Build a little - Test a little

The query runs and shows the rows I expect, but the current budget column is
empty.

Try this --
FROM [Query - Cash Flow_Budgets] LEFT JOIN [Query - Cash Flow_Revised
Budgets] ON [Query - Cash Flow_Budgets].[Budget ID] = [Query - Cash
Flow_Revised Budgets].[Budget ID]

--
KARL DEWEY
Build a little - Test a little


:

Hi. I am trying to create a query that adds (Approved Budget) + (Revised
Budget) to get (Current Budget). Here is my SQL:

SELECT [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc], (Sum([Query - Cash Flow_Budgets]![Line
Amount]+[Query - Cash Flow_Revised Budgets]![Line Amount])) AS [Current
Budget]
FROM [Query - Cash Flow_Budgets] INNER JOIN [Query - Cash Flow_Revised
Budgets] ON [Query - Cash Flow_Budgets].[Budget ID] = [Query - Cash
Flow_Revised Budgets].[Budget ID]
GROUP BY [Query - Cash Flow_Budgets].[CCP No], [Query - Cash
Flow_Budgets].Type, [Query - Cash Flow_Budgets].[Budget Code], [Query - Cash
Flow_Budgets].[Budget Code Desc];

When I have the group by selected it shows me no data. When I remove group
by I get the aggregate function error. It seems like such a simple
calculation, I don't know where I went wrong. Help please!!!

Thanks,
Stacey