Displaying Summary of Multiple Records on a Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have a master table tied to a subtable with a one-to-many relationship.
We need a summary of one field on the subtable to show on the form of the
master table.
 
Assuming that by "summary", you mean that you want to sum the contents of
that specific field, create text box SummaryBox on the master form.Set its
control source to: =DSum("[FieldName]","[SubTableName]")

If you need to filter the summary to the single master record on the master
form, add another control MasterKey (use instead the name of the primary key
here) bound to the primary key for the master table (make it invisible if the
primary key does not need to be seen by the users) and add the where clause
to the above to get something like this (assuming your master key is numeric):

=DSum("[FieldName]","[SubTableName]","[MasterKey] = " & [MasterKey])

If your master key is text, use this instead:

=DSum("[FieldName]","[SubTableName]","[MasterKey] = '" & [MasterKey] & "'")

The first MasterKey is the name of the field in the SubTable that has the
relationship to the primary key in the Master table; the second MasterKey is
the name of the primary key in the Master Table itself; they could be the
same if your tables are set up that way.
 
Back
Top