Search code examples
sqlms-access

Pulling Data from the Wrong Table

I am trying to pull data from the tabled called "Trip". However, my query is currently returning results from another table called "TripEmployeeExpPurpose". My query, query result, and relationships are shown below.

Current Query (incorrect):

SELECT TOP 1 Employee.EmpID, Employee.EmpName, Employee.Title, Employee.GroupTitle, Count(*) AS TotalTrips 

FROM Employee, TripEmployeeExpPurpose, Trip 

WHERE Employee.EmpID=TripEmployeeExpPurpose.EmpID AND TripEmployeeExpPurpose.TripID=Trip.TripID 

GROUP BY Employee.EmpID, Employee.EmpName, Employee.Title, Employee.GroupTitle 

ORDER BY COUNT(*) DESC;

Result (incorrect): enter image description here

Relationship: enter image description here

Expected Result: Currently, Access is using "TripEmployeeExpPurpose" which contains 1442 records. However, we want to pull the data from "Trip" which contains 403 records. (Both table's TripID field is the PK and are connected through the relationship).

Problem: The data from our current result is data pulled from "TripEmployeeExpPurpose". How can I edit the query such that the information is pulled from the "Trip" table instead?

Solution

  • Trip table is not useful in this query because it does not serve to define or limit results by employee. It can only provide descriptive details about trips and your query does not pull any of that data. Consider:

    SELECT TOP 1 Employee.EmpID, EmpName, Title, GroupTitle, Count(*) AS TotalTrips 
FROM Employee INNER JOIN (SELECT DISTINCT TripID, EmpID FROM TripEmployeeExpPurpose) AS Q1 
ON Employee.EmpID = Q1.EmpID 
GROUP BY Employee.EmpID, EmpName, Title, GroupTitle 
ORDER BY Count(*) DESC;

    If you do want to pull data from Trip table (maybe to restrict results to a specific date period), include it in SELECT DISTINCT query with INNER JOIN ON TripID fields and use a WHERE clause as applicable.