Sum calculated text boxes

[TJones8]

Hi, I have a bit of a nasty report set up.

Advisor :
Applicant :
Details :

The report is grouped and sorted by Advisor then Applicant.
Each applicant can have many Details (in this case policies). Each applicant also has only one Proc fee to pay, but the way the query is set up brings back one proc fee per detail but only displays it once.
In the applicant footer, a total field adds each policy fee and one proc fee :-

Sum([PolFee])+[ProcFee]

My problem is that i need each advisor to total up all the applicants' total in the footer. Unfortuantly, i cannot sum the applicant total text box and if i sum the above code, it adds one proc fee per policy!!

Any help is greatfully recieved.

Tim Jones if at first you don't succeed, get a 10lb lump hammer

 

[annsolomon]

You didn't give us any info about your tables. Sounds like your ProcFee field might be in the wrong table.

I'd have 3 separate tables: tblAdvisor, tblApplicant, and tblDetails all joined on a key that identifies the applicant (e.g. ApplicantID). Ann

 

[TJones8]

The table layout is...

Advisor (ID, name, etc..)
Applicatn (ID, name, Advisor ID, Proc Fee, other junk)
Policy (ID, Applicant ID, Fee, stuff) if at first you don't succeed, get a 10lb lump hammer

 

[annsolomon]

I don't see an ApplicantID field in the Advisor and Applicatn tables. How are your tables related? Ann