Sql Query statement under microsoft access

i have 2 tables:

Employee:
    ID
    SalaryPerDay
    overTimeHoursPrice
    .....
    .....

Schedule:
    ID
    EmployeeID
    Date
    Attending  (boolean)
    loan
    discount
    overTimeHours

with many to one relationship

i want a query to return

[employee name] and

[sum(loan)] and

[sum(discount)] and

[sum(overTimeHours)] and

[count(attending)] where attending = true and

[count(attending) * SalaryPerDay] and

[sum(overTimeHours)* overTimeHoursPrice ] and

[(count(attending) * SalaryPerDay) + (sum(overTimeHours)* overTimeHoursPrice) - (sum(discount)) - (sum(loan))]

1- where Date >= [date1] And Date <开发者_如何学编程= [date2]

2- where Date >= [date1] And Date <= [date2] And EmployeeName = [name]

(date1 and date2 and name are parameters)

Something like this should do the trick....

SELECT 
  emp.EmployeeName, sum_loan, sum_discount, sum_overTimeHours, count_attending, 
  (count_attending*SalaryPerDay) as totalDayPay,
  (sum_overTimeHours*overTimeHoursPrice) as totalOverTimePay,
  ((count_attending*SalaryPerDay) + (sum_overTimeHours*overTimeHoursPrice) -
    sum_discount - sum_loan) as grandTotal
FROM Employee emp
  INNER JOIN (SELECT
      EmployeeID,
      sum(loan) as sum_loan,
      sum(discount) as sum_discount,
      sum(overTimeHours) as sum_overTimeHours,
      sum(iif(Attending,1,0)) as count_attending
    FROM Schedule
    WHERE Date >= {date1} and Date <= {date2}
    GROUP BY EmployeeID
  ) sch
  ON emp.ID = sch.EmployeeID
WHERE emp.EmployeeName = {name}

Note the two WHERE clauses. You can adjust these as needed to achieve your two different parameterized restrictions.

Edit #1:
Due to some uncertainty about the actual numeric value of the "boolean" stored in the Schedule.Attending field, I've adjusted the query above to account for the boolean value explicitly. To accomplish this, I've made use of the MSAccess-specific expression function, IIF(). This is a much more robust solution than just assuming that the field will contain either a 1 or a 0.

Edit #2: I should also note that the syntax varies slightly depending on where you're using it. The above is the "standard sql" syntax for the derived table (that's the subquery that's inside parenthesis following the INNER JOIN keywords). If you're running this query through an ODBC connection, then the syntax above is valid.

However, If you're trying to create a Query within Access itself, you'll need to use square brackets with a trailing period [ ]. instead of parenthesis ( ) around the subquery. So instead of:

SELECT ... FROM Employee emp INNER JOIN (SELECT ... ) sch ON ...

use this:

SELECT ... FROM Employee emp INNER JOIN [SELECT ... ]. sch ON ...

I think you want:

SELECT e.EmployeeName, 
   Sum(s.loan) AS SumOfloan, 
   Sum(s.discount) AS SumOfdiscount, 
   Sum(s.overTimeHours) AS SumOfoverTimeHours, 
   Sum(Abs([Attending])) AS Attnd, 
   Sum([SalaryPerDay]*Abs([Attending])) AS SalyAttnd, 
   Sum([overTimeHoursPrice]*[overtimehours]) AS OTCost, 
   Sum(([SalaryPerDay]*Abs([Attending])+[overTimeHoursPrice]*[overtimehours])-([loan]-[discount])) AS Due
FROM Employee e
INNER JOIN Schedule s ON e.ID = s.EmployeeID
WHERE s.Date Between [date1] And [Date2]
AND EmployeeName = [Name] 
GROUP BY e.ID, e.EmployeeName

Note that a Boolean is either 0 or -1, so [SalaryPerDay]*Abs([Attending] = Salary * 1, if attending or 0, if not attending.