SQL query to find covering staff - Syntax error

[James Grant]

Hi Everyone,

To my understanding this code should work; however there's a syntax error somewhere within the sub-query and I cannot find it. It's very possible that my understanding is insufficient and there's a better way to achieve my ends, hence a little background on the motivation behind the query:

I run a staffing database for my department. My boss wants a report that can be run to identify which staff members are covering or 'acting up' and whom they're replacing. Using 3 of my tables (Allocations, Positions, Staff; which allows a Staff member to be Allocated a Position) I can already identify which staff are acting up, and which staff are not current. Logically this then allows the user (if sufficiently familiar with the department's staff) to figure out who's filling in.

This is not what my boss wants - because the report will be passed further up the chain (to people who are not familiar with the department's staff) they need a report that identifies both the replacement and whom-was-replaced side-by-side.

So I'm trying to develop the following query:

SELECT aa.[Position code], p.[Title], p.[Description], aa.[Team], aa.[Payroll number], s.[First name], s.[Surname], s.[Description]
FROM ((tblAllocations aa INNER JOIN tblPositions p ON aa.[Position code]=p.[Position code]) INNER JOIN tblStaff s ON aa.[Payroll number]=s.[Payroll number])
WHERE IN
(SELECT ab.[Position code]
FROM tblAllocations ab
WHERE aa.[Position code]=ab.[Position code]
AND aa.[Team]=ab.[Team]
AND aa.[Payroll number]!=ab.[Payroll number]
AND ((aa.[Acting]=-1 AND ab.[Current position]=0)
OR (aa.[Current position]=0 AND ab.[Acting]=-1)));

The main query simply isolates the fields I want displayed on the final query. The sub-query is attempting to find any duplicated Position codes belonging to the same Team with different Staff (i.e. the old record of who was originally in the position remains in the database and simply becomes marked as Inactive or not current). Further, it then tries to filter those results based upon either the first or the second identified Staff being Acting or Not Current.

The bolded IN above shows where Access is finding a syntax error. I'm sure I'm either asking too much of a single query, have missed an obvious typo, or am attempting to do this the wrong way entirely. Originally I simply tried to do all of this utilising a single-level query (no sub-query) but I was unable to find the correct syntax for creating a duplicate table (i.e. Allocations AS aa, AS ab) whilst also implementing a JOIN.

Many thanks for any assistance, please let me know if you need any more information.

James

[NeoPa]

The format of the WHERE clause is wrong James (See line #3). There needs to be some form of field reference for IN to work with. IE :

WHERE [X] IN(...)

[James Grant]

Hi NeoPa, thanks for your rapid reply!

I'm sure you've heard this many times over, but within 10 minutes of posting this topic I thought to myself "Oh you've got to be kidding...THAT'S IT!" Glaringly obvious haha.

After updating the syntax as you also concluded the query worked perfectly and I'm on to my next query/report.

For clarity here is a copy of the functional, corrected code:

SELECT aa.[Position code], p.Title, p.Description, aa.Team, aa.[Payroll number], s.[First name], s.Surname, s.Description, aa.Acting, aa.[Current position]
FROM (tblAllocations AS aa INNER JOIN tblPositions AS p ON aa.[Position code]=p.[Position code]) INNER JOIN tblStaff AS s ON aa.[Payroll number]=s.[Payroll number]
WHERE aa.[Position code] IN  (SELECT ab.[Position code]  FROM tblAllocations ab  WHERE aa.[Position code]=ab.[Position code]  AND aa.[Team]=ab.[Team]  AND aa.[Payroll number]<>ab.[Payroll number]  AND ((aa.[Acting]=-1 AND ab.[Current position]=0)  OR (aa.[Current position]=0 AND ab.[Acting]=-1)));
 

[NeoPa]

Always a pleasure James :-)