Creating a total query

  • Thread starter Thread starter Goldar
  • Start date Start date
G

Goldar

I have a table (tblBudget) that contains three fields: [GL Account],
[Sequence no] and [CY Amount]. Each [GL Account] has one header record
([Sequence no]=0) and may have many detail records ([Sequence no]=1+). The
[CY Budget] field in the header record is the sum of the [CY Budget] field in
all of the detail records. I tried to create a query to calculate the total
of each group of detail records ([Sequence no]>0), but I keep getting an
error. My query is:

SELECT tblBudget.[GL Account], Sum(tblBudget.[CY Budget]) AS [Budget]
FROM tblBudget
GROUP BY tblBudget.[GL Account]
HAVING (((tblBudget.[Sequence no])>0));

The error that I get is : "You tried to execute a query that does not
include the specified expression ('tblBudget.[Sequence no]>0' as part of an
aggregate function."

What is wrong with my query?

Thanks...
 
What is wrong with my query?
Try this --
SELECT tblBudget.[GL Account], (SELECT Sum([XX].[CY Budget]) FROM tblBudget
AS [XX] WHERE [XX].[GL Account] = tblBudget.[GL Account] AND [XX].[Sequence
no]>0) AS [Budget]
FROM tblBudget
GROUP BY tblBudget.[GL Account];
 
SELECT tblBudget.[GL Account]
, Sum(tblBudget.[CY Budget]) AS [Budget]
FROM tblBudget
WHERE tblBudget.[Sequence no]>0
GROUP BY tblBudget.[GL Account]



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Great! Now, is there some way that I can eliminate thosse records whose
[Budget] amount is zero, or do I need another query to do that

KARL DEWEY said:
Try this --
SELECT tblBudget.[GL Account], (SELECT Sum([XX].[CY Budget]) FROM tblBudget
AS [XX] WHERE [XX].[GL Account] = tblBudget.[GL Account] AND [XX].[Sequence
no]>0) AS [Budget]
FROM tblBudget
GROUP BY tblBudget.[GL Account];

--
Build a little, test a little.


Goldar said:
I have a table (tblBudget) that contains three fields: [GL Account],
[Sequence no] and [CY Amount]. Each [GL Account] has one header record
([Sequence no]=0) and may have many detail records ([Sequence no]=1+). The
[CY Budget] field in the header record is the sum of the [CY Budget] field in
all of the detail records. I tried to create a query to calculate the total
of each group of detail records ([Sequence no]>0), but I keep getting an
error. My query is:

SELECT tblBudget.[GL Account], Sum(tblBudget.[CY Budget]) AS [Budget]
FROM tblBudget
GROUP BY tblBudget.[GL Account]
HAVING (((tblBudget.[Sequence no])>0));

The error that I get is : "You tried to execute a query that does not
include the specified expression ('tblBudget.[Sequence no]>0' as part of an
aggregate function."

What is wrong with my query?

Thanks...
Click to expand...
Click to expand...
 
Now it works perfectly. Thanks for all of your help!

John Spencer said:
SELECT tblBudget.[GL Account]
, Sum(tblBudget.[CY Budget]) AS [Budget]
FROM tblBudget
WHERE tblBudget.[Sequence no]>0
GROUP BY tblBudget.[GL Account]



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a table (tblBudget) that contains three fields: [GL Account],
[Sequence no] and [CY Amount]. Each [GL Account] has one header record
([Sequence no]=0) and may have many detail records ([Sequence no]=1+). The
[CY Budget] field in the header record is the sum of the [CY Budget] field in
all of the detail records. I tried to create a query to calculate the total
of each group of detail records ([Sequence no]>0), but I keep getting an
error. My query is:

SELECT tblBudget.[GL Account], Sum(tblBudget.[CY Budget]) AS [Budget]
FROM tblBudget
GROUP BY tblBudget.[GL Account]
HAVING (((tblBudget.[Sequence no])>0));

The error that I get is : "You tried to execute a query that does not
include the specified expression ('tblBudget.[Sequence no]>0' as part of an
aggregate function."

What is wrong with my query?

Thanks...
Click to expand...
Click to expand...
 
Back
Top