I'm having some trouble with a query, unable to identify the error.

[spoorthy1997]

Can someone help me with this query, it’s quite simple but everytime I execute it, it comes back with an error.
The question is:

List and total the number of all full-time tutorial staff and their location.

(Tables) Employees (Fields) EmployeeID, FirstName, LastName
(Tables) Contracts (Fields) ContractID, EmployeeID, Location, Status

SELECT COUNT(*)"Contracts"."EmployeeID", "Employees"."FirstName", "Employees"."LastName", "Contracts"."Location" 
FROM "Contracts", "Employees" 
WHERE "Contracts"."EmployeeID" = "Employees"."EmployeeID" 
AND "Contracts"."Status" = 'Full-time'
;

[Ratslinger]

Your question specifies both a list & a count. A list will return multiple records & a count will return a single record. You also are not clear about the count - my guess here would be a count of the employees who are full-time at each location.

List of Full-time employees:

SELECT "Employees"."EmployeeID", "Employees"."FirstName", "Employees"."LastName", "Contracts"."Location" FROM "Contracts", "Employees"  WHERE "Contracts"."EmployeeID" = "Employees"."EmployeeID" AND "Contracts"."Status" = 'Full-time'

Count of Full-time employees by location:

SELECT "Contracts"."Location",  COUNT("Contracts"."Location") AS "LocationCount" FROM "Contracts" WHERE "Contracts"."Status" = 'Full-time'  GROUP BY  "Contracts"."Location","Contracts"."Status"

When you have a question and the question involves an error, please specify exactly what the error is. Also include LO version & probably OS.

EDIT:

To carry this out to its’ fullest I’ve included a sample Base file: SQLdemo.odb

Contained within are 2 tables (Contracts & Employees) and 3 queries (FullTimeList, CountByLocation, and JoinTables). The first two queries are as stated above. The third query shows how adding a total with detail does not produce clear results (although the total is correct, it is not correct for that record but rather for all ‘Full-time’ employees in a location). Someone looking at these results would not be able to interpret them correctly!

[LKeithJordan]

@Ratslinger, you make some good points. However, I still believe you are going to need a JOIN for this list of employees.

[Ratslinger]

Join is not necessary - both statements tested & work as is. You can, however join the two tables & produce a result from the join showing each employee detail and duplicated COUNT for Contract totals.

[LKeithJordan]

REVISION:

@Ratslinger makes some valid points in his response, and negates the need for the COUNT function in my SQL statement (since the rest of my SQL statement deals with the list). However, I believe a JOIN is still needed for the list of employees. I therefore revise my SQL statement as follows:

SELECT Contracts.EmployeeID, Employees.FirstName, Employees.LastName, Contracts.Location
FROM Contracts
LEFT JOIN Employees
ON Contracts.EmployeeID=Employees.EmployeeID
AND Contracts.Status = Full-time;

Also, please be aware I am not testing my script prior to responding (as I prefer to do).

^^^^^^^^^^^^^^^^

ORIGINAL RESPONSE

I’m basing my reply purely on looking at your code snippet, but I would try this:

SELECT COUNT(Contracts.EmployeeID) as NumEmployees, Contracts.EmployeeID, Employees.FirstName, Employees.LastName, Contracts.Location
FROM Contracts
LEFT JOIN Employees
ON Contracts.EmployeeID=Employees.EmployeeID
AND Contracts.Status = Full-time;

Please click the check mark next to the response you believe best answers your question.