Group by Query

Hi All,
I Have this problem:
I have a table like this

Code Bank Year ID Total
123 Bank1 2007 1 1000
123 BanK1 2007 2 1500
123 BanK2 2007 3 800
123 BanK2 2007 4 900
124 Bank1 2007 5 4000
124 BanK1 2007 6 1500

I need new table with aggregate data groupped by Code, Bank Year and
with total of Total:


Code Bank Year Totale
123 Bank1 2007 2500
123 BanK2 2007 1700
124 BanK1 2007 5500

Thanks for your help.

Anluce

Re: Group by Query

> I need new table with aggregate data groupped by Code, Bank Year and
> with total of Total:
>
>
> Code Bank Year Totale
> 123 Bank1 2007 2500
> 123 BanK2 2007 1700
> 124 BanK1 2007 5500

You don't need a new table. All you need is a summary field based on
sum of Totale and a subsummary layout part ( when sorted by Code and Bank ).

The rest of the work FileMaker will do for you.

Jens


--
Free PlugIn for Regular Expressions with FileMaker:
http://jensteich.de/regex-plugin/

Re: Group by Query

On 7 Apr, 10:31, Jens Teich wrote:
> > I need new table with aggregate data groupped by Code, Bank Year and
> > with total of Total:
>
> > Code =A0 =A0 Bank =A0 =A0 =A0Year =A0 =A0 =A0 Totale
> > 123 =A0 =A0 =A0 Bank1 =A0 =A0 2007 =A0 =A0 =A0 2500
> > 123 =A0 =A0 =A0 BanK2 =A0 =A02007 =A0 =A0 =A0 =A01700
> > 124 =A0 =A0 =A0 BanK1 =A0 =A02007 =A0 =A0 =A0 =A05500
>
> You don't need a new table. All you need is a summary field based on
> sum of Totale and a subsummary layout part ( when sorted by Code and Bank =
).
>
> The rest of the work FileMaker will do for you.
>
> Jens
>
> --
> Free PlugIn for Regular Expressions with FileMaker:http://jensteich.de/reg=
ex-plugin/

Hi,
I don't want a report,
but I need a portal that show aggregate data.
Anna

Re: Group by Query

Anluce writes:

> On 7 Apr, 10:31, Jens Teich wrote:
>> > I need new table with aggregate data groupped by Code, Bank Year and
>> > with total of Total:
>>
>> > Code     Bank      Year       Totale
>> > 123       Bank1     2007       2500
>> > 123       BanK2    2007        1700
>> > 124       BanK1    2007        5500
>>
>> You don't need a new table. All you need is a summary field based on
>> sum of Totale and a subsummary layout part ( when sorted by Code and Bank ).

> I don't want a report,
> but I need a portal that show aggregate data.
> Anna

Ok I see. So you need an additional table. This table can be filled via a relationship
with key fields CODE and BANK. Check the option to allow creation of records via this
relationship an you can create missing records via SetField and a loop over all records
in the original table.

You get the addition of TOTALE via the function SUM() and a the same relationship the
other way round.

Jens

--
Free PlugIn for Regular Expressions with FileMaker:
http://jensteich.de/regex-plugin/

Re: Group by Query

On 7 Apr, 14:59, Jens Teich wrote:
> Anluce writes:
> > On 7 Apr, 10:31, Jens Teich wrote:
> >> > I need new table with aggregate data groupped by Code, Bank Year and
> >> > with total of Total:
>
> >> > Code =A0 =A0 Bank =A0 =A0 =A0Year =A0 =A0 =A0 Totale
> >> > 123 =A0 =A0 =A0 Bank1 =A0 =A0 2007 =A0 =A0 =A0 2500
> >> > 123 =A0 =A0 =A0 BanK2 =A0 =A02007 =A0 =A0 =A0 =A01700
> >> > 124 =A0 =A0 =A0 BanK1 =A0 =A02007 =A0 =A0 =A0 =A05500
>
> >> You don't need a new table. All you need is a summary field based on
> >> sum of Totale and a subsummary layout part ( when sorted by Code and Ba=
nk ).
> > I don't want a report,
> > but I need a portal that show aggregate data.
> > Anna
>
> Ok I see. So you need an additional table. This table can be filled via a =
relationship
> with key fields CODE and BANK. Check the option to allow creation of recor=
ds via this
> relationship an you can create missing records via SetField and a loop ove=
r all records
> in the original table.
>
> You get the addition of TOTALE via the function SUM() and a the same relat=
ionship the
> other way round.
>
> Jens
>
> --
> Free PlugIn for Regular Expressions with FileMaker:http://jensteich.de/reg=
ex-plugin/

I have done so.
Many thanks,
Anluce