Summary list in a form footer

[Malcolm]

Hi,

I have a sales entry form with salesman codes, sales
amounts and first name (Amongst other fields), all hooked
to a single transaction table.

In the form footer I want to show a summary listbox (I
assume this is the best control) of 3 columns for each
salesman of:

salesman code | Salesman name | sum of sales

But I want this to track the form displayed recordset. I
could do a group by query to group salesman + name + sum
of sales, but I'm not sure how to set up a listbox for
this so everything tracks together. Or should it be a
subform?

Any help greatly appreciated.


Regards


Malcolm

 

[Paul Falla]

Dear Malcom

I personally would use a subform. Base it on the totals
query you mention in your posting, and to get it to be
local to the dataset displaying on the form, in your
query, in the criteria of the field which contains the
data that is common to both, enter Forms![Name of your
Form]![Name of the field that contains the common data]

Hope this helps

Paul

 

[Malcolm]

Paul,

Thanks for the reply. I will have a look at this later on
today.

My initial thought though is that this will return a
recordset only for the current record value of the
criteria [field] (Such as a single given salesman), not
the entire recordset which I'm trying to summarise and may
include multiple salesman.

I'm thinking I might be able to set the recordsource for
the subform query to the same as the form, and filter
property to the form's filter value, which is the same
recordset. Does this sound logical?

Again, thanks for the help.


Malcolm

-----Original Message-----
Dear Malcom

I personally would use a subform. Base it on the totals
query you mention in your posting, and to get it to be
local to the dataset displaying on the form, in your
query, in the criteria of the field which contains the
data that is common to both, enter Forms![Name of your
Form]![Name of the field that contains the common data]

Hope this helps

Paul

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

Hi,

I have a sales entry form with salesman codes, sales
amounts and first name (Amongst other fields), all hooked
to a single transaction table.

In the form footer I want to show a summary listbox (I
assume this is the best control) of 3 columns for each
salesman of:

salesman code | Salesman name | sum of sales

But I want this to track the form displayed recordset. I
could do a group by query to group salesman + name + sum
of sales, but I'm not sure how to set up a listbox for
this so everything tracks together. Or should it be a
subform?

Any help greatly appreciated.


Regards


Malcolm


.

.