Creating a total query

[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...

 

[KARL DEWEY]

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];

 

[John Spencer]

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

 

[Goldar]

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

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.

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...

 

[Goldar]

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

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...