Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: Simple Calculation Help

    I'm trying to calculate columns with a simple validation rule however I'm having a rough time... I keep getting errors.

    The best way to describe what I'm looking for would be calculating the totals of specific line items or part numbers.

    I have columns "EmployeeID" , "ReasonCodeID", "Date" and "Number"

    These 4 columns are in the form "History Subform" and part of the master Form "Employee History"

    Now what I want to happen is on the Employee History form, I'd like to have 4 text boxes that show the results of the sum of the "Number"s based on 4 different reason codes.

    So for instance...

    ReasonCode Number
    1 100
    1 200
    2 50
    2 200
    3 50
    3 50
    4 250
    4 100


    The text box "txt_Reason1" should display 300
    The text box "txt_Reason2" should display 250
    The text box "txt_Reason3" should display 100

    etc.

    I have a query that shows the calculations for each Reason Code and employee, but I can't seem to extract just the total depending on what Employee is being displayed on the main form.

    =nz(Sum([txt_Number])) will do a calculation for me, on all of the numbers, so the above would be 1000 but I now need to separate each Reason with an individual calculation.

    Any help?

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    You can use the DSum Function

    In the textbox data source type the following

    = DSum("{Sum Field}", "{Table}", "ReasonCode = 1")

    Where
    {Sum Field} = the field you want to Sum
    {Table} = The table the field is from

    The last part youhave to change in each textbox

    IE textbox 1
    "ReasonCode = 1"

    textbox 2
    "ReasonCode = 2"

    etc
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    Aug 2003
    Posts
    3

    Almost.....

    That worked with a minor tweek. This actually gave me the total per each ReasonCode as I had requested, but that was due in part to believing that the Sum would be for the individual employee which was already being generated on each subform. I had to expand the validation out to include employeeID.

    ie:

    = DSum("[Sum Field]", "[Table Name]", "ReasonCode = 1 and EmployeeID = txt_EmployeeID")

    Thanks for the help.

    Chris



    Originally posted by machado
    You can use the DSum Function

    In the textbox data source type the following

    = DSum("{Sum Field}", "{Table}", "ReasonCode = 1")

    Where
    {Sum Field} = the field you want to Sum
    {Table} = The table the field is from

    The last part youhave to change in each textbox

    IE textbox 1
    "ReasonCode = 1"

    textbox 2
    "ReasonCode = 2"

    etc

Posting Permissions

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