Recent content by NHCPA

Thank you PHV, I appreciate your time and expertise.

Thank you for taking the time to respond. I would still get one transaction twice, as follows: Dept Emp Amt 201 101 500 202 101 250 201 101 250

Each employee would have a payroll transaction every week, however he will only have a dept change once in a couple months or years.

Doing that you would get each payroll transaction twice (one for each dept) As follows: Dept Emp Amt 202 101 500 201 101 500 202 101 250 201 101 250

I have a table with payroll transactions and a table with dept activity (dates employees were transferred to another dept). I am trying to make a query that will add the correct dept for each payroll transaction DeptAct Emp Dept TDate 101 201 01/01/05 101 202 02/01/05 Payroll Emp Amt PDate 101...

I have a table with payroll transactions and a table with dept activity (dates employees were transferred to another dept). My query is supposed to list the payroll transactions along with the dept the employee was in at the time. DeptAct Emp Dept TDate 101 201 01/01/05 101 202 02/01/05...

Thank You dhookom, I appreciate your time. The above works OK, however it only gives me employees that have a record in both tables. I need to have all payroll even if there is no benefits for this employee. Sorry I was not clear about this. Changing it to a left join, only brings in the...

Thank You very much dhookom. I tried the above, however in the above example it gives me two lines for emp# 101, each in the amount of 437.50. It seems that it is averaging both dept's for this emp. The total between both dept's is correct however the per dept is not.

Employee 101 was in two dept for this month, he earned 200 while in dept 30, and 600 in dept 100. So, dept 30 was 25% (200/(200+600)), and dept 100 was 75% (600/800). now benefits of 100-25=75 * 25% = 18.75. 18.75 + 200 = 218.75.

Thank You, I hope this helps. However it does not have to be ratably either one will do. Table 1 Emp # Month Payroll Dept 101 105 200 30 102 105 500 250 103 105 750 130 101 105 600 100 Table 2 Emp # Month Benefit 101 105 100 102...

I am trying to create a query that will give me the total cost per employee/Month/Dept. Table #1 – Employee #, Month, Payroll Amount, Dept # Table #2 – Employee #, Month, Benefit Amount Table #3 – Employee #, Month, Deduction Amount When an employee switches departments he will have more then...

Thank you again Golom I have a table 'table1' which contains the following: Dept Amount ID Dept1 23 41 Dept2 28 41 Dept1 23 42 Dept2 27 42 Dept1 23 43 Dept2 30 43 and a query: SELECT ((A.Amount + (Select B.Amount from table1 as B Where B.ID = (Select Max (ID) From table1 As C Where C.ID <...

Thanks Golom for your time. I tried using it, but I got a error message "Cant have an aggregate function in a Where clause". Please advise. Thanks in advance.

Numbers starting from 1 up to approx 100 now.

I have a query that has the following 3 fields Dept, Sequence #, Amount I am trying to add a field (in this query or a new one) that will average the amount per record with the amount of the record with the previous sequence #. Thanks for any help.