Prev: Query keeps looking for Query1 field
Next: Date/Time criteria problem
From: cluckers on 28 Apr 2010 11:23
I am running a query that I created in design view. I am linking a number of
tables together to get the result that I need. There are about ten fields in
the querry but I only need to display the values of two fields: 'Account' and
'Value'. There are multiple Values for each Account so I am trying to sum
the Values and Group By Account. On the Design view in the Total I choose
Sum for the Value field and Group By for the Account field. But it does not
group anything. It diplsays all entries that the query produces. Is this
becuase I have other non-displayed fields in my query? Is there a way to do
this?

Thanks
From: Daryl S on 28 Apr 2010 13:21
Cluckers -

What you are suggesting should work. Post your SQL here so we can take a
look and help you figure it out. (Go to View SQL and copy/paste the SQL
statement here).

--
Daryl S


"cluckers" wrote:

> I am running a query that I created in design view. I am linking a number of
> tables together to get the result that I need. There are about ten fields in
> the querry but I only need to display the values of two fields: 'Account' and
> 'Value'. There are multiple Values for each Account so I am trying to sum
> the Values and Group By Account. On the Design view in the Total I choose
> Sum for the Value field and Group By for the Account field. But it does not
> group anything. It diplsays all entries that the query produces. Is this
> becuase I have other non-displayed fields in my query? Is there a way to do
> this?
>
> Thanks
From: vanderghast on 28 Apr 2010 13:27
The GROUP is made over all the fields with the GROUP BY selection in the
graphical designer (or all those which appears in the GROUP BY clause in SQL
view). Be sure to remove from the grid all other fields/expressions other
than the field Account, with GROUP BY selected. Since Value has SUM, not
GROUP BY, you keep it too.

Vanderghast, Access MVP

"cluckers" <cluckers(a)discussions.microsoft.com> wrote in message
news:57518164-FF97-471C-BD41-458B091EF677(a)microsoft.com...
>I am running a query that I created in design view. I am linking a number
>of
> tables together to get the result that I need. There are about ten fields
> in
> the querry but I only need to display the values of two fields: 'Account'
> and
> 'Value'. There are multiple Values for each Account so I am trying to sum
> the Values and Group By Account. On the Design view in the Total I choose
> Sum for the Value field and Group By for the Account field. But it does
> not
> group anything. It diplsays all entries that the query produces. Is this
> becuase I have other non-displayed fields in my query? Is there a way to
> do
> this?
>
> Thanks

From: cluckers on 28 Apr 2010 13:37
The problem is I use the other fields to filter the records that I want. If
I remove the other fields how do I use them to filter the records?

"vanderghast" wrote:

> The GROUP is made over all the fields with the GROUP BY selection in the
> graphical designer (or all those which appears in the GROUP BY clause in SQL
> view). Be sure to remove from the grid all other fields/expressions other
> than the field Account, with GROUP BY selected. Since Value has SUM, not
> GROUP BY, you keep it too.
>
> Vanderghast, Access MVP
>
> "cluckers" <cluckers(a)discussions.microsoft.com> wrote in message
> news:57518164-FF97-471C-BD41-458B091EF677(a)microsoft.com...
> >I am running a query that I created in design view. I am linking a number
> >of
> > tables together to get the result that I need. There are about ten fields
> > in
> > the querry but I only need to display the values of two fields: 'Account'
> > and
> > 'Value'. There are multiple Values for each Account so I am trying to sum
> > the Values and Group By Account. On the Design view in the Total I choose
> > Sum for the Value field and Group By for the Account field. But it does
> > not
> > group anything. It diplsays all entries that the query produces. Is this
> > becuase I have other non-displayed fields in my query? Is there a way to
> > do
> > this?
> >
> > Thanks
>
From: vanderghast on 28 Apr 2010 14:14
You change the GROUP BY to WHERE, in the graphical designer.


Vanderghast, Access MVP


"cluckers" <cluckers(a)discussions.microsoft.com> wrote in message
news:D63CB809-14ED-4021-B1B2-B673B4B1B1E2(a)microsoft.com...
> The problem is I use the other fields to filter the records that I want.
> If
> I remove the other fields how do I use them to filter the records?
>
> "vanderghast" wrote:
>
>> The GROUP is made over all the fields with the GROUP BY selection in the
>> graphical designer (or all those which appears in the GROUP BY clause in
>> SQL
>> view). Be sure to remove from the grid all other fields/expressions other
>> than the field Account, with GROUP BY selected. Since Value has SUM, not
>> GROUP BY, you keep it too.
>>
>> Vanderghast, Access MVP
>>
>> "cluckers" <cluckers(a)discussions.microsoft.com> wrote in message
>> news:57518164-FF97-471C-BD41-458B091EF677(a)microsoft.com...
>> >I am running a query that I created in design view. I am linking a
>> >number
>> >of
>> > tables together to get the result that I need. There are about ten
>> > fields
>> > in
>> > the querry but I only need to display the values of two fields:
>> > 'Account'
>> > and
>> > 'Value'. There are multiple Values for each Account so I am trying to
>> > sum
>> > the Values and Group By Account. On the Design view in the Total I
>> > choose
>> > Sum for the Value field and Group By for the Account field. But it
>> > does
>> > not
>> > group anything. It diplsays all entries that the query produces. Is
>> > this
>> > becuase I have other non-displayed fields in my query? Is there a way
>> > to
>> > do
>> > this?
>> >
>> > Thanks
>>

 | 
Pages: 1
Prev: Query keeps looking for Query1 field
Next: Date/Time criteria problem