Problem With Double Join

[TastyWheat]

I've done double and even triple joins before, but this isn't working for me, probably because I'm using a SELECT statement instead of a table. Here's the basic (working) query:

SELECT Employee.*, NewPositionID AS [Position], NewStoreID AS [Store]
FROM Employee
LEFT JOIN (
    [Position Change] INNER JOIN [Store Change]
    ON [Position Change].EmployeeID=[Store Change].EmployeeID
) ON Employee.EID=[Position Change].EmployeeID;

Now that works perfectly fine, but the problem is I only want the employee to join with the most recent position and store change. So I tried something like this:

SELECT Employee.*, NewPositionID AS [Position], NewStoreID AS [Store]
FROM Employee
LEFT JOIN (
    (SELECT TOP 1 * FROM [B][Position Change][/B] ORDER BY EffectiveDate Desc)
    INNER JOIN (SELECT TOP 1 * FROM [Store Change] ORDER BY EffectiveDate Desc)
    ON [Position Change].EmployeeID=[Store Change].EmployeeID
) ON Employee.EID=[Position Change].EmployeeID;

It tells me "Syntax error in JOIN operation" and it highlights the first occurence of "[Position Change]" (the part in bold). Using the 1st query I don't see how I can filter out the correct records.

 

[Fifty2One]

i think position is a reserved word so maybe that is creating troubles...

 

[Fifty2One]

jet reserved words...
A
ABSOLUTE
ACTION
ADD
ADMINDB
ALL
ALLOCATE
ALPHANUMERIC
ALTER
AND
ANY
ARE
AS
ASC
ASSERTION
AT
AUTHORIZATION
AUTOINCREMENT
AVG

B
BAND
BEGIN
BETWEEN
BINARY
BIT
BIT_LENGTH
BNOT
BOR
BOTH
BXOR
BY
BYTE

C
CASCADE
CASCADED
CASE
CAST
CATALOG
CHAR
CHARACTER
CHAR_LENGTH
CHARACTER_LENGTH
CHECK
CLOSE
COALESCE
COLLATE
COLLATION
COLUMN
COMMIT
COMP
COMPRESSION
CONNECT
CONNECTION
CONSTRAINT
CONSTRAINTS
CONTAINER
CONTINUE
CONVERT
CORRESPONDING
COUNT
COUNTER
CREATE
CREATEDB
CROSS
CURRENCY
CURRENT
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
CURSOR

D
DATABASE
DATE
DATETIME
DAY
DEALLOCATE
DEC
DECIMAL
DECLARE
DEFAULT
DEFERRABLE
DEFERRED
DELETE
DESC
DESCRIBE
DESCRIPTOR
DIAGNOSTICS
DISALLOW
DISCONNECT
DISTINCT
DOMAIN
DOUBLE
DROP

E
ELSE
END
END-EXEC
ESCAPE
EXCEPT
EXCEPTION
EXCLUSIVECONNECT
EXEC
EXECUTE
EXISTS
EXTERNAL
EXTRACT

F
FALSE
FETCH
FIRST
FLOAT
FLOAT4
FLOAT8
FOR
FOREIGN
FOUND
FROM
FULL

G
GENERAL
GET
GLOBAL
GO
GOTO
GRANT
GROUP
GUID

H
HAVING
HOUR

I
IDENTITY
IEEEDOUBLE
IEEESINGLE
IGNORE
IMAGE
IMMEDIATE
IN
INDEX
INDICATOR
INHERITABLE
INITIALLY
INNER
INPUT
INSENSITIVE
INSERT
INT
INTEGER
INTEGER1
INTEGER2
INTEGER4
INTERSECT
INTERVAL
INTO
IS
ISOLATION

J
JOIN

K
KEY

L
LANGUAGE
LAST
LEADING
LEFT
LEVEL
LIKE
LOCAL
LOGICAL
LOGICAL1
LONG
LONGBINARY
LONGCHAR
LONGTEXT
LOWER

M
MATCH
MAX
MEMO
MIN
MINUTE
MODULE
MONEY
MONTH

N
NAMES
NATIONAL
NATURAL
NCHAR
NEXT
NO
NOT
NOTE
NULL
NULLIF
NUMBER
NUMERIC

O
OBJECT
OCTET_LENGTH
OF
OLEOBJECT
ON
ONLY
OPEN
OPTION
OR
ORDER
OUTER
OUTPUT
OVERLAPS
OWNERACCESS

P
PAD
PARAMETERS
PARTIAL
PASSWORD
PERCENT
PIVOT
POSITION
PRECISION
PREPARE
PRESERVE
PRIMARY
PRIOR
PRIVILEGES
PROC
PROCEDURE
PUBLIC

Q

R
READ
REAL
REFERENCES
RELATIVE
RESTRICT
REVOKE
RIGHT
ROLLBACK
ROWS

S
SCHEMA
SCROLL
SECOND
SECTION
SELECT
SELECTSCHEMA
SELECTSECURITY
SESSION
SESSION_USER
SET
SHORT
SINGLE
SIZE
SMALLINT
SOME
SPACE
SQL
SQLCODE
SQLERROR
SQLSTATE
STRING
SUBSTRING
SUM
SYSTEM_USER

T
TABLE
TABLEID
TEMPORARY
TEXT
THEN
TIME
TIMESTAMP
TIMEZONE_HOUR
TIMEZONE_MINUTE
TO
TOP
TRAILING
TRANSACTION
TRANSFORM
TRANSLATE
TRANSLATION
TRIM
TRUE

U
UNION
UNIQUE
UNIQUEIDENTIFIER
UNKNOWN
UPDATE
UPDATEIDENTITY
UPDATEOWNER
UPDATESECURITY
UPPER
USAGE
USER
USING

VVALUE
VALUES
VARBINARY
VARCHAR
VARYING
VIEW

WWHEN
WHENEVER
WHERE
WITH
WORK
WRITE

X

Y
YEAR
YESNO

Z
ZONE

 

[TastyWheat]

Damn. Really crappy luck. There really isn't a better name for that table so I don't want to rename it. I can get the job done using lookup functions anyway.

 

[pbaldy]

Untested guess, but I'd think you have to alias the subqueries and use the alias name in the join statements.

 

[Fifty2One]

position is a reserved word if it just stands alone so you could use
PositionChange
or
Position_Change
or any other variant of the word. sadly it worked perfectly all the way through the development of your access work because it is a jet reserved word and not an access reserved word...