Subform Only Showing Last Group Record (1 Viewer)

lwallis

Registered User
Joined
Feb 11, 2010
Messages
23
I have one query that the main form is based on and another query the subform is based on.

I linked the subform to the main form by a common field "Branch"

My main form displays Grouped Employee overhead totals based on Branch
Example:
Branch 1 Employee overhead cost...........15,000.00
Branch 2 Employee overhead cost...........25,000.00

The subform displays grouped branch expense overhead totals based on Branch
Example:
Branch 1 Branch expense...........125,000.00
Branch 2 Branch expense...........155,000.00

I am trying to display both the employee overhead total and branch expense in the Branch Header of the mainform.
Example:
Branch 1 Employee Overhead...15,000.00 Branch Expense...125,000.00
Branch 2 Employee Overhead...25,000.00 Branch Expense...155,000.00

I tried using an IIF statement in a text box in the mainform branch header section to return the branch expense if subform branch = mainform branch.......When I run the report I get all of the Employee overhead but only the last record for Branch 2 branch expense displays. Example:
Branch 1 Employee Overhead..15,000.00
Branch 2 Employee Overhead..25,000.00 Branch Expense...155,000.00

Then

I tried just putting a text box in the main branch header subreportName.Report.TotalField
When I run the report it returns all employee overhead with the Branch 2 total for every branch in the mainform...
Example:
Branch 1 Employee Overhead..15,000.00 Branch Expense...155,000.00
Branch 2 Employee Overhead..25,000.00 Branch Expense...155,000.00

is there a way to do this... I feel like I'm so close but so far :banghead:

Thank you,
Lynda
 

JHB

Have been here a while
Joined
Jun 17, 2012
Messages
7,720
Use DSum to get the value, I've put in an extra text control in the header of the report to show you how, (database attached).
Be aware of you have doublet value in some branch like in "AMA-910".
 

Attachments

lwallis

Registered User
Joined
Feb 11, 2010
Messages
23
JHB,
Thank you so much for all your help.
I'm not a programmer, just someone that had a tiny bit of DB knowledge from 15 years ago so I got elected!

I would still be stuck on form creation without your help.

Thank you again!
Lynda
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom