Sql Query statement under microsoft access

Question

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)

Lee · Accepted Answer · 2011-10-01T01:21:13.703

0

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 ...

edited Oct 01 '11 at 01:21

answered Sep 29 '11 at 06:27

Lee

13,462
1
32
45

you forget to mention where attending = true when you count attending, where can i put this condition – Sep 29 '11 at 06:33
true = 1, false = 0 -- so just summing the column should be sufficient. Did you try it? – Lee Sep 29 '11 at 06:41
no i didn't try it yet, when i try it i will tell you if it works or not, thanks lee – Sep 29 '11 at 07:26
I do not believe JOIN is regularly supported by MS Access. You need either INNER or OUTER added to that. – Fionnuala Sep 29 '11 at 13:13
@Remou - thanks. I don't usually use Access (it's been a while in any case). inner join is what's needed here. I've made the necessary adjustments. – Lee Sep 29 '11 at 16:28
@danny-london - I've posted an edit that should provide more robust and explicit handling of the boolean value in the `Attending` field. This solution uses a function that is available in MS-Access only, and I don't have any ready way to test it; but let me know if there are problems, and I'll see if I can help anyway. – Lee Sep 29 '11 at 17:53
Access Boolean is always -1 or 0, not 1 or 0. – Fionnuala Sep 29 '11 at 20:47
the approach I've shown above (using `iff()`) should work regardless of the actual numeric value. – Lee Sep 29 '11 at 22:35
Please see Edit#2 for information about how the syntax must change if you intend to use the query within the Access query editor (as opposed to using it from an external application) – Lee Sep 29 '11 at 22:43
The SQL 89 syntax for a derived table in Access/Jet/ACE requires not just the square brackets, but a trailing period: `[SELECT ...]. AS Alias`. – David-W-Fenton Sep 30 '11 at 22:34
@David-W-Fenton - thanks for the info. I've made the necessary adjustment to the info above. – Lee Oct 01 '11 at 01:21
thanks lee i try this query it is correct, without where statements. i don't know if the problem is using where statement or with parameters. i have form with name MainForm i put instead of {date1} [Forms!MainForm!txtFromDate] and instead of {date2} [Forms!MainForm!txtFromDate] and instead of {name} [[Forms!MainForm!comboName] is there a problem with this parameter – Oct 01 '11 at 05:37
I think your parameters should be something like: `[Forms]![MainForm]![comboName]`. You can also test the where clauses without parameters, by using fixed values in single-quotes, like this: `WHERE emp.EmployeeName = 'Bob Jones'` – Lee Oct 01 '11 at 19:11
thx Lee its correct but i have another question if you can answer it http://stackoverflow.com/questions/7631545/how-to-create-datesheet-form-microsoft-access-to-insert-data – Oct 03 '11 at 06:06
Sorry, I know a lot about SQL, but not much at all about the MS Access platform. I looked at your other question, but I couldn't even begin to tell you where to start. @Remou seems quite knowledgeable though - hopefully he can help you. – Lee Oct 03 '11 at 20:29

Fionnuala · Answer 2 · 2011-09-29T22:59:05.043

0

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.

edited Sep 29 '11 at 22:59

answered Sep 29 '11 at 13:10

Fionnuala

90,370
7
114
152

This assumes that `EmployeeName` is unique within the `Employee` table. (i.e. no two employees have the same name). This may or may not be a valid assumption. For a large organization, it is almost certainly not valid. The structure of the `Employee` table would seem to imply that `Employee.ID` is the unique field, while `Employee.EmployeeName` is not so constrained. If multiple employees do have the same name, then your query will roll their results together into a single summary row. – Lee Sep 29 '11 at 17:17
@Lee Ta. Corrected. The above can be built in the query design window, useful for the person new to Access, which I believe the OP to be. – Fionnuala Sep 29 '11 at 18:32
In regard to @Lee's comment, `AND EmployeeName = [Name]` should then probably read `AND e.ID = [ID: ]` – Fionnuala Sep 29 '11 at 18:36
Isn't it illegal to select a non-aggregated column in an aggregate (group by) query? If you change to group by `e.ID`, then I think `e.EmployeeName` would need to become something like `First(e.EmployeeName)`. (see: [microsoft docs](http://office.microsoft.com/en-us/access-help/group-by-clause-HP001032236.aspx), "All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.") – Lee Sep 29 '11 at 22:57
@danny Your final line simplified shows `Salary + OT - Discount - Loan` but I think you want `(Salary + OT) - (Loan - Discount)` In other words, discount is discounted from the loan, not subtracted from the total salary. This is what is shown above. – Fionnuala Sep 30 '11 at 07:52

Sql Query statement under microsoft access

2 Answers2