Form footer summary

[Malcolm]

Hi,

I'm trying to nail my problem - a simple sales form linked
to a transaction table, where I display in the form footer
a groupby summary of each [salesman] and their [sum of
sales] for the displayed records.

Paul suggested a subform, which I am having trouble with
the query as I want it to track the form recordset and any
filters. Jonathan suggested dsum, but this only gives
totals for a given salesman (ie. current record) but I am
after a groupby list of all displayed salesman.

Both ideas are appreciated, but I haven't been able to
solve it. If I use a subform I can include fields from the
form recordset in the recordsource property in a query,
such as:

SELECT forms!mainform.form.salesman FROM forms!
mainform.form.recordset where ... groupby...

All seems to work except the FROM clause, which I can't
seem to establish any reference to the form recordset. I
can use the forms recordsource, which is a simple SELECT
query, but this is all records not the displayed ones. Can
anyone help here? I need the form recordset as the subform
FROM source.

So close, yet so far...


Thanks in advance, especially Paul & Jonathan.


Malcolm

 

[SFAxess]

I am a little confused as to how you are displaying the
salesmen on the parent form. Is there more than 1
salesman field?
Would it not work to set the recordsource of your footer
subform to "SELECT Salesman, SUM(Sales) FROM
qryYourParentFormQuery GROUP BY Salesman;"
Put 2 text boxes (Salesman and Total Sales) in the
subform and set the view to "Continuous Forms".
(In this scenario the parent form's recordset is saved as
a query object, "qryYourParentFormQuery" and not linked
to the parent)

 

[Marshall Barton]

I'm trying to nail my problem - a simple sales form linked
to a transaction table, where I display in the form footer
a groupby summary of each [salesman] and their [sum of
sales] for the displayed records.

Paul suggested a subform, which I am having trouble with
the query as I want it to track the form recordset and any
filters. Jonathan suggested dsum, but this only gives
totals for a given salesman (ie. current record) but I am
after a groupby list of all displayed salesman.

Both ideas are appreciated, but I haven't been able to
solve it. If I use a subform I can include fields from the
form recordset in the recordsource property in a query,
such as:

SELECT forms!mainform.form.salesman FROM forms!
mainform.form.recordset where ... groupby...

All seems to work except the FROM clause, which I can't
seem to establish any reference to the form recordset. I
can use the forms recordsource, which is a simple SELECT
query, but this is all records not the displayed ones. Can
anyone help here? I need the form recordset as the subform
FROM source.

I won't guarentee this to be 100% reliable (can't really
count on the filter property all the time).

Anyway, create a Totals query named qrySummary that provides
the summary you want except without any filtering. Then, at
some strategic(?) place in your code, set the subform's
record source to use the main form's Filter property:

Me.subfrom.Form.RecordSource =
"SELECT * FROM qrySummary WHERE " & Me.Filter

Where that "strategic place" is in your code is not at all
clear. The Current event would probably work, except you
would be resetting the subform far more often than needed.
If you have a newer(?) version of Access, there might even
be a filter applied kind of event that would be ideal.

 

[Malcolm]

Thanks SFA

There is only 1 salesman field, and I can do what you have
suggested except this doesn't track record filters set by
the user.

However the combination of your idea and Marshalls
(Modifying the select statement as form filters change)
might work ok.

I will have a play. Thanks.

-----Original Message-----
I am a little confused as to how you are displaying the
salesmen on the parent form. Is there more than 1
salesman field?
Would it not work to set the recordsource of your footer
subform to "SELECT Salesman, SUM(Sales) FROM
qryYourParentFormQuery GROUP BY Salesman;"
Put 2 text boxes (Salesman and Total Sales) in the
subform and set the view to "Continuous Forms".
(In this scenario the parent form's recordset is saved as
a query object, "qryYourParentFormQuery" and not linked
to the parent)

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

Hi,

I'm trying to nail my problem - a simple sales form linked
to a transaction table, where I display in the form footer
a groupby summary of each [salesman] and their [sum of
sales] for the displayed records.

Paul suggested a subform, which I am having trouble with
the query as I want it to track the form recordset and any
filters. Jonathan suggested dsum, but this only gives
totals for a given salesman (ie. current record) but I am
after a groupby list of all displayed salesman.

Both ideas are appreciated, but I haven't been able to
solve it. If I use a subform I can include fields from the
form recordset in the recordsource property in a query,
such as:

SELECT forms!mainform.form.salesman FROM forms!
mainform.form.recordset where ... groupby...

All seems to work except the FROM clause, which I can't
seem to establish any reference to the form recordset. I
can use the forms recordsource, which is a simple SELECT
query, but this is all records not the displayed ones. Can
anyone help here? I need the form recordset as the subform
FROM source.

So close, yet so far...


Thanks in advance, especially Paul & Jonathan.


Malcolm


.

.

 

[Malcolm]

Marshall,

If I create a totals query for the subform then I can't
filter it based on a primary key value or anything that is
in the forms recordset, because it is just that - a totals
query with groupings.

I should be able to use your logic and SFA's, by doing the
grouping in the subform recordsource SELECT statement, and
applying form filters as they are applied to the main
form, which seems to alleviate the above problem.

That's how I see it anyway - I will have a play and see
what comes out. I hope you are right!

Databases are fun!!

Thanks for your help.

Malcolm

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

I'm trying to nail my problem - a simple sales form linked
to a transaction table, where I display in the form footer
a groupby summary of each [salesman] and their [sum of
sales] for the displayed records.

Paul suggested a subform, which I am having trouble with
the query as I want it to track the form recordset and any
filters. Jonathan suggested dsum, but this only gives
totals for a given salesman (ie. current record) but I am
after a groupby list of all displayed salesman.

Both ideas are appreciated, but I haven't been able to
solve it. If I use a subform I can include fields from the
form recordset in the recordsource property in a query,
such as:

SELECT forms!mainform.form.salesman FROM forms!
mainform.form.recordset where ... groupby...

All seems to work except the FROM clause, which I can't
seem to establish any reference to the form recordset. I
can use the forms recordsource, which is a simple SELECT
query, but this is all records not the displayed ones. Can
anyone help here? I need the form recordset as the subform
FROM source.

I won't guarentee this to be 100% reliable (can't really
count on the filter property all the time).

Anyway, create a Totals query named qrySummary that provides
the summary you want except without any filtering. Then, at
some strategic(?) place in your code, set the subform's
record source to use the main form's Filter property:

Me.subfrom.Form.RecordSource =
"SELECT * FROM qrySummary WHERE " & Me.Filter

Where that "strategic place" is in your code is not at all
clear. The Current event would probably work, except you
would be resetting the subform far more often than needed.
If you have a newer(?) version of Access, there might even
be a filter applied kind of event that would be ideal.

 

[Marshall Barton]

Marshall,

If I create a totals query for the subform then I can't
filter it based on a primary key value or anything that is
in the forms recordset, because it is just that - a totals
query with groupings.

I should be able to use your logic and SFA's, by doing the
grouping in the subform recordsource SELECT statement, and
applying form filters as they are applied to the main
form, which seems to alleviate the above problem.

Yes, you can filter the group by query using form values,
then apply the form's filter stuff in the subform's record
source SQL. Kind of a multi-layered approach to filtering.

One added complication is that you'll have to Requery the
subform in the AfterUpdate event of each control that's used
as a criteria in the group by query.
--
Marsh
MVP [MS Access]

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

I'm trying to nail my problem - a simple sales form linked
to a transaction table, where I display in the form footer
a groupby summary of each [salesman] and their [sum of
sales] for the displayed records.

Paul suggested a subform, which I am having trouble with
the query as I want it to track the form recordset and any
filters. Jonathan suggested dsum, but this only gives
totals for a given salesman (ie. current record) but I am
after a groupby list of all displayed salesman.

Both ideas are appreciated, but I haven't been able to
solve it. If I use a subform I can include fields from the
form recordset in the recordsource property in a query,
such as:

SELECT forms!mainform.form.salesman FROM forms!
mainform.form.recordset where ... groupby...

All seems to work except the FROM clause, which I can't
seem to establish any reference to the form recordset. I
can use the forms recordsource, which is a simple SELECT
query, but this is all records not the displayed ones. Can
anyone help here? I need the form recordset as the subform
FROM source.

I won't guarentee this to be 100% reliable (can't really
count on the filter property all the time).

Anyway, create a Totals query named qrySummary that provides
the summary you want except without any filtering. Then, at
some strategic(?) place in your code, set the subform's
record source to use the main form's Filter property:

Me.subfrom.Form.RecordSource =
"SELECT * FROM qrySummary WHERE " & Me.Filter

Where that "strategic place" is in your code is not at all
clear. The Current event would probably work, except you
would be resetting the subform far more often than needed.
If you have a newer(?) version of Access, there might even
be a filter applied kind of event that would be ideal.

 

[SFAxess]

Another way to alleviate the performance drag is to hide
the footer and have a command button (or toggle) that
says "View Totals". When the user clicks the command
button (or toggle), it sets the recordsource of the
subform and unhides the footer so the user can see the
totals only when needed and your program executes the
code only when necessary. I have used this strategy in a
form that displayed calculations in the footer (which
really drags down record navigation), and it worked great.
Best of luck!

-----Original Message-----
Marshall,

If I create a totals query for the subform then I can't
filter it based on a primary key value or anything that is
in the forms recordset, because it is just that - a totals
query with groupings.

I should be able to use your logic and SFA's, by doing the
grouping in the subform recordsource SELECT statement, and
applying form filters as they are applied to the main
form, which seems to alleviate the above problem.

That's how I see it anyway - I will have a play and see
what comes out. I hope you are right!

Databases are fun!!

Thanks for your help.

Malcolm

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

I'm trying to nail my problem - a simple sales form linked
to a transaction table, where I display in the form footer
a groupby summary of each [salesman] and their [sum of
sales] for the displayed records.

Paul suggested a subform, which I am having trouble with
the query as I want it to track the form recordset and any
filters. Jonathan suggested dsum, but this only gives
totals for a given salesman (ie. current record) but I am
after a groupby list of all displayed salesman.

Both ideas are appreciated, but I haven't been able to
solve it. If I use a subform I can include fields from the
form recordset in the recordsource property in a query,
such as:

SELECT forms!mainform.form.salesman FROM forms!
mainform.form.recordset where ... groupby...

All seems to work except the FROM clause, which I can't
seem to establish any reference to the form recordset. I
can use the forms recordsource, which is a simple SELECT
query, but this is all records not the displayed ones. Can
anyone help here? I need the form recordset as the subform
FROM source.

I won't guarentee this to be 100% reliable (can't really
count on the filter property all the time).

Anyway, create a Totals query named qrySummary that provides
the summary you want except without any filtering.

Then,

at
some strategic(?) place in your code, set the subform's
record source to use the main form's Filter property:

Me.subfrom.Form.RecordSource =
"SELECT * FROM qrySummary WHERE " & Me.Filter

Where that "strategic place" is in your code is not at all
clear. The Current event would probably work, except you
would be resetting the subform far more often than needed.
If you have a newer(?) version of Access, there might even
be a filter applied kind of event that would be ideal.

.