-
I have an Access table called Employee_Work_History_Days with the following fields :
Employee_Id - Text
Work_Date - Date
Payment_Id - Text
Gross - Currency
Week_Number - Number
Employee_Id, Work_Date and Payment_Id make up the primary key.
I need to total up the sum for the Gross for a particular Employee and Week Number. I can do this, but I also need it to ignore a specified Work_Date and Payment_Id.
For instance I need a total for the Gross for employee 'SWE0001' on Week_Number 1, except for the Payment_Id '02' on Work_Date 03/04/2000.
Can anybody help?
-
SELECT SUM(Gross) FROM Employee_Work_History_Days
WHERE Week_Number=1 AND NOT (Payment_ID=2 AND Work_Date = '03/04/2000')
This might work - I can't test it at the mo.
-
I was just about to close the thread and post a message saying I'd already figured it out when you replied.
I tried yours and with a some minor alterations it worked, so thanks a lot.
Heres how I had solved it :
SELECT SUM(Gross) AS GrossForWeek
FROM Employee_Work_History_Days
WHERE Employee_Id = 'SWE0001'
AND Week_Number = 1
AND Gross NOT IN
(SELECT Gross
FROM Employee_Work_History_Days
WHERE Employee_Id = 'SWE0001'
AND Payment_Id = '02'
AND Work_Date = #03/04/2000#)
Cheers anyway. :)
