Show totals and max date from subform on main form

[jdttek]

I have a mainform with names of customers and a subform with invoices for customers, linked by customer number. I am trying to build a summary box on the MAIN FORM that will show an invoice subtotal, a count of invoices and a 1st and last (min / max) date from the linked subform. Any help on how to approach this wld be greatly appreciated.

Thanks
jdttek

 

[beetee]

check out the Access help on DLookup, DSum, DMax, and DMin.

These functions allow you to calculate aggregate values such as sums, etc.

You can then add a control with a control source that is the appropriate aggregate function, e.g.

=DSum("[FieldToSum]","SubFormTable","[SubFormLinkField] = MainFormLinkField&quot, e.g.

=DSum("[Cost]","OrderDetails","[OrderID] = Forms![OrdersForm]![ID]&quot

 

[jdttek]

thanks beetee.
I will try this and let you know how this works. Should I expect this to slow the app down any given the added calculation?
(PS: I had tried to accomplish the same thing by creating total queries off of the subform table. This worked OK but created considerable query bloat since I had to create several queries to get the summary data I needed. Plus, by adding it to the mainform query, stopped me from making changes to the main form recordset. Think your solution will work fine and solve these problems. thanks).

JDTTEK

 

[beetee]

DLookup is probably about the most inefficient way to do things.

A better way, but a bit harder to explain, would be to generate a query (actually, similar to the dlookup expression) that would calculate ALL the aggregates to need.

Then, create unbound controls, write a bit of code to open the query on your main form's on current event, and assign values to the control variables by opening a recordset based on the aggregate query.

Perhaps someone has an easier solution.

I believe there's a also a sum function you can add to forms, but those would go in the sub-form. I've never used it.

If you find you have a *large* number of items to sum (which seems unlikely in your case) you might want to go with the above example.

 

[jdttek]

Your suggestion sounds kind of like wehat aI had tried by creating the aggregate query. I had added that query to the mainform query, which is what had blocked changes after it was added. Will try your suggestion. Will I need to always open the aggregate query 1st to access data before linking to unbound controls on main form? Is that what you mean by opening the recordset on the unbound query?

 

[beetee]

There may well be a better way to do this.


In the Form_Current event

private sub Form_Current
dim MyDb as database
set MyDB = currentdb

dim AggRst as recordset
set AggRst = mydb.openrecordset("MyAggregateQuery&quot

me.OrderSum = aggrst("OrderSum&quot
...
myrst.close
set myrst = nothing
mydb.close
set mydb = nothing
end sub

The concept here is:

Create an aggregate query that will return only 1 row, and have the sums/max/etc. that you need. If necessary, you can perform a sum on an expression, e.g. TotalOrder: sum([Qty] * [UnitCost])

This query is based on the table used by the sub form. The query should have a where clause that includes the link field between the main form and the sub form.

Add controls to the main form that as containers for the query columns.

Then, as displayed in the code above, in the on current event for your main form, open the query and assign to the controls.