Include / Exclude in calcs

[Guest]

Simple table
Qty rate Include (tick box!)
150 £15 Yes
250 £10 Yes
15 £20 No
155 £15 Yes
etc etc

I want to show average; min; max values for the data when 'Include' = yes
User can select via tick box which records he wants to include/exclude ie no
defined parameter
Any ideas

Saintsman

 

[Guest]

Hello,

You dont say exactly how you want to present this information.

My initial thought would be to put the records into a form. Create a query
(query 1)only for those records where you have clicked yes and create a
summary query (query 2) based on query 1 which calculates what you need and
put that query information into the same form (maybe as a subform).

You would then need an after update event of some kind to change the
calculation every time you select or unselect a box.

 

[Guest]

Simply restrict a query on the Include column, e.g.

SELECT
AVG(Qty) AS AvgQuantity,
MIN(Qty) AS MinQuantity,
MAX(Qty) AS MinQuantity,
AVG(Rate) AS AvgRate,
MIN(Rate) AS MinRate,
MAX(Rate) AS MaxRate
FROM YourTable
WHERE Include = TRUE;

You could create a form bound to this query and then open it from a button
in the footer of a form bound to the table once the user has checked the
Include field for the records in question. Be sure you first save the
current record in the first form though, so the code to open the second form
would be along these lines:

RunCommand acCmdSaveRecord
DoCmd.OpenForm "frmAggregations"

If you use scubadiver's idea of a subform based on the aggregate query
(which is a good one) then you don't need to open a second form; you can
simply requery the subform in the AfterUpdate event of the Include check box
on the main form:

RunCommand acCmdSaveRecord
Me.sfrAggregations.Requery

The subform would then update dynamically whenever Include is checked or
unchecked on the main form. Note that sfrAggregations is the name of the
subform control in the main form, i.e. the control which houses the subform,
not the name of its underlying form object; unless both have the same name of
course.

Ken Sheridan
Stafford, England