Groupby summary in form footer

[Malcolm]

Hi,

Second try here - I just can't get this to play the game
with me.

I have a single form sales table. In the form footer I
want to show a summary for the displayed, possibly
filtered, records for each salesman, for example:

salesman | sum of sales

I just can't make something that seems simple to happen. I
have tried subforms (Which I use many in different
situations) with groupby queries. I can achieve the
GroupBy subform, but just can't link it to the displayed
main form records. Because it is a groupby query I cant
link master / child fields.

I've tried using a listbox, and anything else my little
brain can come up with.

A simple textbox allows me to display sum(record), but not
as a grouped listing.


Can someone shine some light? I am sure the answer is
staring me in the face.

Thanks in advance.


Malcolm

 

[Jonathan Parminter]

-----Original Message-----

Hi,

Second try here - I just can't get this to play the game
with me.

I have a single form sales table. In the form footer I
want to show a summary for the displayed, possibly
filtered, records for each salesman, for example:

salesman | sum of sales

...

Thanks in advance.


Malcolm

.

Hi Malcolm,
have you considered using a dsum?

Use the following as an example, assuming that the form
named, frmSales is based on a table, tblSales
and that the field to sum is named, SaleTotal
and that the sales table is linked to the table of
Salesman names using a field, SalesmanID....

=DSum("[SaleTotal]","[tblSales]","[SalesmanID]=forms!
[frmSales]![SalesmanID]")

Luck
Jonathan

 

[Malcolm]

Thanks for the reply Jonathan.

This will give me a sum for a given salesman, being the
form current record. I want to show a summary table (Or
listbox or subform) showing all salesman grouped, and the
total sales for each salesman.

Example, the filtered recordset shows
Fred $50
Mick $20
Fred $75
John $34
Mick $25

to summarise in the form footer:

Salesman |Total sales
Fred $125
Mick $45
John $34

If there were 27 salesman, they would all be listed

dsum might be useful, but I really need all salesman
listed.

Any other thoughts?

Thanks in advance

Malcolm

-----Original Message-----

-----Original Message-----

Hi,

Second try here - I just can't get this to play the game
with me.

I have a single form sales table. In the form footer I
want to show a summary for the displayed, possibly
filtered, records for each salesman, for example:

salesman | sum of sales

...

Thanks in advance.


Malcolm

.

Hi Malcolm,
have you considered using a dsum?

Use the following as an example, assuming that the form
named, frmSales is based on a table, tblSales
and that the field to sum is named, SaleTotal
and that the sales table is linked to the table of
Salesman names using a field, SalesmanID....

=DSum("[SaleTotal]","[tblSales]","[SalesmanID]=forms!
[frmSales]![SalesmanID]")

Luck
Jonathan
.

 

[Jonathan Parminter]

Hi Malcolm,
how about a listbox that is bound to a summary query?

In the design view of a select query have the two fields,
Salesman and SalesTotal. Use the View menu to show the
Totals row.

In the Totals row for the Salesman column leave the
default GroupBy. For the SalesTotal column change GroupBy
to sum.

If it's a filtered list that you require, add a third
field, SalesmanID. Change GroupBy to Where. In the
criteria row set a criteria that matches the filtered list.

Luck
Jonathan

-----Original Message-----

Thanks for the reply Jonathan.

This will give me a sum for a given salesman, being the
form current record. I want to show a summary table (Or
listbox or subform) showing all salesman grouped, and the
total sales for each salesman.

Example, the filtered recordset shows
Fred $50
Mick $20
Fred $75
John $34
Mick $25

to summarise in the form footer:

Salesman |Total sales
Fred $125
Mick $45
John $34

If there were 27 salesman, they would all be listed

dsum might be useful, but I really need all salesman
listed.

Any other thoughts?

Thanks in advance

Malcolm

-----Original Message-----

-----Original Message-----

Hi,

Second try here - I just can't get this to play the game
with me.

I have a single form sales table. In the form footer I
want to show a summary for the displayed, possibly
filtered, records for each salesman, for example:

salesman | sum of sales

...

Thanks in advance.


Malcolm

.

Hi Malcolm,
have you considered using a dsum?

Use the following as an example, assuming that the form
named, frmSales is based on a table, tblSales
and that the field to sum is named, SaleTotal
and that the sales table is linked to the table of
Salesman names using a field, SalesmanID....

=DSum("[SaleTotal]","[tblSales]","[SalesmanID]=forms!
[frmSales]![SalesmanID]")

Luck
Jonathan
.

.