Thread: summing totals based on user selections of dates

1. Registered User
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. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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
•