Results 1 to 5 of 5

Thread: Total Problem

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Total Problem

    I'm having a problem creating a total for a control. Running Access 97 SR2 on Win98.

    I have a form which was constructed using the New Form wizard to get data from 2 tables and create a single form (not a subform within a form). This form will be a subform in another form (a placement of a person at a job). One table supplies just 1 field (a nominal commission rate for a recruiter) while the other supplies the rest (actual commission rate, a flat commission if applicable, any deductions, the effort% when there is >1 recruiter).

    This subform can show up to 2 recruiters who cooperate in placing the person. The subform in question is calculating the recruiters' commission.

    I think I have followed the book on this one and still am getting a #NAME? error. The calculations of the commission starts with the recruiters' actual commission for the placement, which is entered in the subform. I need to make sure that the total commission when there is >1 recruiter does not exceed 50% (of the company's net commission). Easy enough?

    Enter the commission in the detail (or pull it from the existing table). Can put a check for an individual commission that it does not exceed 50%. Enter the total of the field in the subform's form footer as
    =Total([controlname-from-detail])

    I've seen examples of this in books, other people's DBs, etc. I can't get it to work. I've made sure I've gotten the correct name for the detail's control by using the expression builder and alternatively by copying and pasting the detail's Name property into the total control's Control Source property. Nothing seems to help.

    After the commission, there are various calculations to get the final net commission. I also need to make sure that the effort% adds to 100% (this too is an entry in the form bound to the underlying table). I know that I can't total controls that are calculated. However, I've not bothered trying to fix these yet since the straightforward total of controls pulling values from tables isn't even working.

    Ideas?

    By the way: is there a way to allow entry of whole % (like 32%) but still have the value treated as a decimal for calculation purposes. I know I can divide the number by 100 in calculations but I was hoping for an easier way.

    Fred <img src=/S/frown.gif border=0 width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Problem

    So what total are you trying to show, the total for the recruiters for a particular person, or the total for all recruiters for all people? In either case, you would need a built-in or user-defined function to create the sum of the values. So unless Total is a user-defined function in your database, it's the reason you're getting the #NAME? error. Also, is this on a form or a report? You talk about a form, but you also mention not being able to total controls that are calculated, which is a report issue.

    You can format a number so that it is entered as a decimal but displayed as something like 32%, if that's what you mean.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Problem

    One common problem is that using the access wizards uses the same name for everything. So you can have a control, a fields and a table all called, say, fred. Then when you reference fred access doesnot know which fred you want.

    Check particularly that the name of the field used in the control you are totalling is not the same as the name of the control.

    HTH
    David Grugeon
    Brisbane Australia

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Problem

    Another possibility is to use DSUM. This directly queries the underlying tables rather than the controls.

    The answer to the sum of calculated controls is that you put a control for the running sum in the detail and make it invisible. you then put the same control in the footer and make it visible. Look for RunningSum in help
    David Grugeon
    Brisbane Australia

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Total Problem

    Hi Charlotte and Dave,

    Thanks for the quick responses. Since posting, I have solved the problem. However, I did not post in haste. A friend and I spent the better part of a day working this.

    However, a question or 2 remains so don't toss this yet.

    To answer a few questions:
    - The total would go on the footer of a form. The form is showing the involved recruiters for a single candidate/placement. I have to ensure that the total commission divided by all recruiters (probably never >2) is not more than 50% of the company's commission (the other 50% allowing for overhead, etc.). Likewise, the level of effort spent by the recruiters must =100%. In both cases, a construct like
    =SUM(some-detail-control) should have worked as the source for the control in the footer. It didn't and wasn't obvious as to why not.

    - I ran into the issue of same-named controls all over the place. That was one of the first things I tried, to no avail.

    - the issue of not summing controls that are calculations is also a form issue. My friend mentioned it but it was based on observations. I actually found it in Help by looking for Sum and it was quite explicit.

    - it even seems that I can have sums of items that are not calculated but, as soon as I put in the first sum of a calculated control, I get the name error for the OTHER controls that are summing just plain values. Rather poor design, I think. I got around this by putting everything into a query for which the recordset is selected. Then I just refer to SUM(name-in-query-for-calculated-field) as my source for the control in the footer.

    - But I will have to check out the Running sum solution. That has promise.

    Now to the last question - the % question.

    Charlotte: I think I may not have been clear. If a recruiter gets a 35% commission, I want the user of the DB to enter 35 and not .35. But I want the calculations with the field to be treated as .35. I know I can divide by 100 when doing the calculation but I was hoping for a better way.

    Thanks again.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •