Subquery Problem

[Guest]

This is my first attempt at writing a subquery...I am trying to return the
top 3 records for each itemname. Here is what I have written:

SELECT tblProduction.Date, tblProduction.ActivityName,
tblProduction.ItemName, tblProduction.Qty,
tblProduction.ManHours, tblProduction.Customer,
tblEmployees.[Employee Name] FROM tblEmployees INNER JOIN (tblProduction
INNER JOIN tblProductionEmployee ON tblProduction.JobNumber =
tblProductionEmployee.JobNumber) ON tblEmployees.[Employee Initials] =
tblProductionEmployee.[Employee Initials]
SELECT TOP 3 tblproduction.Qty
FROM tblproduction AS Dupe
WHERE Dupe.itemname = tblproduction.itemname
ORDER BY Dupe.date DESC

I'm getting a syntax error in query expression. Any help would be
appreciated. Thanks!

 

[Allen Browne]

We can't be sure of your data structure, so you will to adjst this, but the
general idea seems to be to use a WHERE clause to limit the primary key of
tblProductionEmployee to the 3 top quantities for that product + employee.

(It may be that the subquery needs some kind of grouping to get a sum by
product + employee. If so, you might mock up a query that does what you want
for the subquery, and use that as a basis for the subquery.)

Anyway, the subquery goes in the WHERE clause, possibly something like this:

SELECT tblProduction.Date,
tblProduction.ActivityName,
tblProduction.ItemName,
tblProduction.Qty,
tblProduction.ManHours,
tblProduction.Customer,
tblEmployees.[Employee Name]
FROM tblEmployees
INNER JOIN (tblProduction
INNER JOIN tblProductionEmployee
ON tblProduction.JobNumber = tblProductionEmployee.JobNumber)
ON tblEmployees.[Employee Initials]
= tblProductionEmployee.[Employee Initials]
WHERE tblProductionEmployee.ID IN
(SELECT TOP 3 tblproductionEmployee.ID
FROM tblproductionEmployee AS Dupe
WHERE Dupe.itemname = tblproduction.itemname
AND Dupe.[Employee Initials]
= tblEmployeees.[Employee Initials]
ORDER BY Dupe.Qty DESC);