How to JOIN Four tables, with muliple constraints

Question

I have to write a SQL statement to show a sum for similar types, I also need add another exclusion constraint from another table.

This is using Microsoft Access 2010. The problem reads: Write an SQL statement to show the sum of HoursWorked for each Type of OWNER but exclude services of employees who have an ExperienceLevel of Junior and exclude any Type with less than three members.

 SELECT OWNER.Type, SUM(HoursWorked) AS Total_Work_Hours
FROM OWNER AS O, PROPERTY AS P, SERVICE AS S, EMPLOYEE AS E
WHERE O.OwnerID = P.OwnerID
AND P.Property= S.PropertyID
AND S.EmployeeID=E.EmployeeID
AND O.Type>3;

Muhammad Waheed · Answer 1 · 2019-01-24T12:27:49.940

1

You may try this.

SELECT O.[Type], SUM(e.HoursWorked) AS Total_Work_Hours,count(e.employeeid) as Total_Members
FROM OWNER AS O, PROPERTY AS P, SERVICE AS S, EMPLOYEE AS E
WHERE O.OwnerID = P.OwnerID
AND P.Property= S.PropertyID
AND S.EmployeeID=E.EmployeeID
AND e.ExperienceLevel NOT IN ('Junior')
Group by o.[Type]
Having count(e.employeeid)>=3;

edited Jan 24 '19 at 12:27

answered Jan 24 '19 at 04:40

Muhammad Waheed

1,048
1
13
30

For some reason I still get the same error, to enter a parameter for OWNER.Type – Johanna Wood Jan 24 '19 at 04:50
Then you must have been writing incorrect column name for `owner.type`. verify it's name in database – Muhammad Waheed Jan 24 '19 at 04:52
"Enter Parameter Value ?" Owner Type. The name of the table is Owner, and the column name is Type. – Johanna Wood Jan 24 '19 at 05:04
you may check now – Muhammad Waheed Jan 24 '19 at 05:09
If possible kindly edit your question and provide all tables and their columns names. – Muhammad Waheed Jan 24 '19 at 05:09
`Type` is a reserved name in Microsoft Access. You shouldn't use that as object or field names. Because that can cause such behaviour, try to replace `Type` by `[Type]` in your query to clear that it is a field name. – AHeyne Jan 24 '19 at 06:26
I have tried [Type] and ['Type'], also tried parenthesis brackets – Johanna Wood Jan 24 '19 at 12:06
Share the tables structure please. So that I may assist you precisely – Muhammad Waheed Jan 24 '19 at 12:08
For the Owner Table. Column Names are OwnerID, OwnerName, Email, Type. Data Types are Autonumber, Short Text, Short Text, Short Text. Respectively – Johanna Wood Jan 24 '19 at 12:25

score 0 · Accepted Answer · answered Jan 24 '19 at 12:42

0

Learn to use proper, explicit, standard JOIN syntax. You also need a proper GROUP BY and should be qualifying all column references:

SELECT o.Type, SUM(HoursWorked) AS Total_Work_Hours
FROM ((OWNER AS O INNER JOIN
       PROPERTY AS P
       ON O.OwnerID = P.OwnerID
      ) INNER JOIN
      SERVICE AS S
      ON P.Property = S.PropertyID
     ) INNER JOIN
     EMPLOYEE AS E
     ON S.EmployeeID = E.EmployeeID
WHERE O.Type > 3 AND
      E.ExperienceLevel <> 'Junior'
GROUP BY o.Type
HAVING COUNT(*) >= 3;

answered Jan 24 '19 at 12:42

Gordon Linoff

1,242,037
58
646
786

:-) old-style joins are still as much "standard" as they have always been. – Erwin Smout Jan 24 '19 at 14:15
Thank you, I will! I am learning still and I am still trying to better understand how to join tables, there seems to be a lot of ways to do so. – Johanna Wood Jan 25 '19 at 00:03

How to JOIN Four tables, with muliple constraints

2 Answers2