Aggregate message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I don't know where to begin. For argument's sake, everything is working up
to this point. All my forms, queries, and union query work.
My problem is this. I have sum totals like this:
PT OT ST
25 25 40
50 50 100
and my results of Sum of PT does show 75 which is great. Now I need to
have a total of PT + OT + ST which should show 90. When I add a new field
to the individual queries I get the correct results. However when I added
the new field to my Union query SQL statements it does not work. It says I'm
trying to use a field that is not part of an aggregate statement.
What am I missing?
 
Any time you use an aggregate function (Sum(), Average(), etc.) you have to
include a group by statement that includes all the other fields in the
select list to your query.

SELECT FirstField, SecondField, Sum(ThirdField)
FROM YourTable
GROUP BY FirstField, SecondField

If you will post the SQL for your query, someone here should be able to
give you more specific help.
 
Below is the SQL for the union query. As I stated, the individual queries
work. But when I add the [Total] to all five queries, I get error message.



SELECT "05 Day" AS Expr1, [Query1].[Med RecNumber],Sum([Query1].[PT]) AS
SumOfPT, Sum([Query1].[OT]) AS SumOfOT, Sum([Query1].[ST]) AS SumOfST, [Total]
FROM Query1
WHERE ((([Query1].[TDate]) Between DateAdd("d",-6,[Enter StartDate:]) And
[Enter StartDate:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "14 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 14Date:]) And [Enter
14Date:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "30 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 30Date:]) And [Enter
30Date:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "60 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 60Date:]) And [Enter
60Date:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "90 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 90Date:]) And [Enter
90Date:]))
GROUP BY [Query1].[Med RecNumber]
ORDER BY [Expr1];
 
That's because [Total] is not in your Group By statements. Change each of
them to look like this:

GROUP BY [Query1].[Med RecNumber], [Total]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


dar said:
Below is the SQL for the union query. As I stated, the individual queries
work. But when I add the [Total] to all five queries, I get error message.



SELECT "05 Day" AS Expr1, [Query1].[Med RecNumber],Sum([Query1].[PT]) AS
SumOfPT, Sum([Query1].[OT]) AS SumOfOT, Sum([Query1].[ST]) AS SumOfST, [Total]
FROM Query1
WHERE ((([Query1].[TDate]) Between DateAdd("d",-6,[Enter StartDate:]) And
[Enter StartDate:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "14 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 14Date:]) And [Enter
14Date:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "30 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 30Date:]) And [Enter
30Date:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "60 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 60Date:]) And [Enter
60Date:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "90 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 90Date:]) And [Enter
90Date:]))
GROUP BY [Query1].[Med RecNumber]
ORDER BY [Expr1];


Lynn Trapp said:
Any time you use an aggregate function (Sum(), Average(), etc.) you have to
include a group by statement that includes all the other fields in the
select list to your query.

SELECT FirstField, SecondField, Sum(ThirdField)
FROM YourTable
GROUP BY FirstField, SecondField

If you will post the SQL for your query, someone here should be able to
give you more specific help.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


working
up new
field says
I'm
Click to expand...
Click to expand...
 
I did as you stated, and the error message for the aggregate is gone,
however, now I get a parameter popup box asking for Total.

Lynn Trapp said:
That's because [Total] is not in your Group By statements. Change each of
them to look like this:

GROUP BY [Query1].[Med RecNumber], [Total]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


dar said:
Below is the SQL for the union query. As I stated, the individual queries
work. But when I add the [Total] to all five queries, I get error message.



SELECT "05 Day" AS Expr1, [Query1].[Med RecNumber],Sum([Query1].[PT]) AS
SumOfPT, Sum([Query1].[OT]) AS SumOfOT, Sum([Query1].[ST]) AS SumOfST, [Total]
FROM Query1
WHERE ((([Query1].[TDate]) Between DateAdd("d",-6,[Enter StartDate:]) And
[Enter StartDate:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "14 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 14Date:]) And [Enter
14Date:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "30 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 30Date:]) And [Enter
30Date:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "60 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 60Date:]) And [Enter
60Date:]))
GROUP BY [Query1].[Med RecNumber]

UNION SELECT "90 Day" AS Expr1, Query1.[Med RecNumber], Sum(Query1.PT) AS
SumOfPT, Sum(Query1.OT) AS SumOfOT, Sum(Query1.ST) AS SumOfST, [Total]
FROM Query1
WHERE (((Query1.TDate) Between DateAdd("d",-6,[Enter 90Date:]) And [Enter
90Date:]))
GROUP BY [Query1].[Med RecNumber]
ORDER BY [Expr1];


Lynn Trapp said:
Any time you use an aggregate function (Sum(), Average(), etc.) you have to
include a group by statement that includes all the other fields in the
select list to your query.

SELECT FirstField, SecondField, Sum(ThirdField)
FROM YourTable
GROUP BY FirstField, SecondField

If you will post the SQL for your query, someone here should be able to
give you more specific help.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


I don't know where to begin. For argument's sake, everything is working
up
to this point. All my forms, queries, and union query work.
My problem is this. I have sum totals like this:
PT OT ST
25 25 40
50 50 100
and my results of Sum of PT does show 75 which is great. Now I need to
have a total of PT + OT + ST which should show 90. When I add a new
field
to the individual queries I get the correct results. However when I added
the new field to my Union query SQL statements it does not work. It says
I'm
trying to use a field that is not part of an aggregate statement.
What am I missing?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top