Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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.

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!