Re: Aggregate message

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Lynn Trapp (ltrappNoSpam_at_ltcomputerdesigns.com)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 12:00:50 -0500

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" <dar@discussions.microsoft.com> wrote in message
news:DAE88B85-BA25-4622-9C8E-F06B43FE0981@microsoft.com...
> 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" wrote:
>
> > 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
> >
> >
> > "dar" <dar@discussions.microsoft.com> wrote in message
> > news:762298DC-690A-4DAB-9254-DCFB2DBC2ADC@microsoft.com...
> > > 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?
> > >
> > >
> >
> >
> >

Relevant Pages

  • Re: I just want a report summary
    ... >I am not creating a record for each child. ... >> MS Access MVP ... >>> I would like a report that totals how many children are in each age ... >>>>> make a report that simply has totals for each of field. ...
    (microsoft.public.access.reports)
  • Re: counting values
    ... Duane Hookom ... MS Access MVP ... wanted to combine the totals from 2 or more departments? ... I want to report on how many thought that attending was ...
    (microsoft.public.access.reports)
  • Re: Need Pivot report to give total of entries made from noon to n
    ... >I already have a date and time field which both use the NowFunction, ... > now I no longer need the totals for each day at the end of the day. ... >> Duane Hookom ... >> MS Access MVP ...
    (microsoft.public.access.reports)
  • Re: Beginner Query Help
    ... Create a "totals" query, grouping by your "columnA" and summing on ... <Access MVP> ... "The parawon" wrote in message ... Bob and Sharon share a cash register. ...
    (microsoft.public.access.queries)
  • Re: Cant total in footer
    ... is your #error text box in the Report Footer or Page Footer? ... > used to get the totals by CSR and put SUM in front of it, ... >> MS Access MVP ...
    (microsoft.public.access.reports)