Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    toronto
    Posts
    1

    Unanswered: summing totals based on user selections of dates

    hello,
    this is a difficult question to ask, but i'll try anyway.
    i have two tables containing a pay date, an employee number and the pay for that pay date (2 tables because of two different methods to keep track of pay).
    in a form, the user of the DB selects a start date and end date for which they wish to find out how much an employee was paid in that range of dates. for example:

    TABLE1:

    paydate empno totalpay
    20020901 1 123
    20020902 1 234
    20020902 2 234
    20020903 2 435
    20020904 3 567
    20020905 4 786
    20020905 1 154
    20020906 2 365


    TABLE2:

    paydate empno totalpay
    20020901 2 324
    20020901 3 213
    20020903 1 123
    20020904 4 679
    20020904 2 543
    20020905 1 346
    20020906 3 123
    20020907 3 783
    20020907 1 245


    in my form, in one textbox, the user specifies the start date, for instance, 20020903 (sept.3, 2002) and an end date 20020905. i want to generate a query that sums the totals from each table for each employee, for instance:

    EmpNo TotalfromTable1 TotalfromTable2
    1 154 469
    2 435 543
    etc...

    any help is greatly appreciated as i am struggling to find a way to do this properly.
    for any further explanations you can email me at:

    waleed.ayoub@tdsb.on.ca

    thanks,
    waleed.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hopefully, you have a table of Employees that you can link these two table to. If so, try this:

    select Employees.empno
    sum(Table1.totalpay) TotalFromTable1
    sum(Table2.totalpay) TotalFromTable2
    from Employees.empno
    left outer join Table1 on Employees.empno = Table1.empno and Table1.paydate between StartDate and EndDate
    left outer join Table2 on Employees.empno = Table2.empno and Table2.paydate between StartDate and EndDate

    blindman

Posting Permissions

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