Hi,

I was just wondering if you could tell me if the code i have pasted seems to look ok. When running it in Access i get an error "invalid syntax 'PICONSOLIDATED'. Im trying to run a query that looks at another query which creates a temp table then displays results.

SELECT DUPS.[SLOT ID] AS PIConsolSLOT, DUPS.[ITEM #] AS PIConsolITEM, ItemReport.[PRIME SLOT ID] AS IReportSLOT, ItemReport.[ITEM#] AS IReportITEM INTO MOVED_ITEMS FROM

(SELECT [PICONSOLIDATED].[ITEM #], [PICONSOLIDATED].[SLOT ID], [PICONSOLIDATED].[ITEM DESCRIPTION], [PICONSOLIDATED].[SLOT USAGE]

FROM (SELECT [PI COUNT].[SLOT ID], [PI COUNT].[ITEM #], [PI COUNT].[ITEM DESCRIPTION], [PI COUNT].[SLOT STATUS], [PI COUNT].[SLOT USAGE], [PI COUNT].[SLOT HEIGHT], Left([SLOT ID],1) AS LETTER, Sum([PI COUNT].[PALLET QTY]) AS [SumOfPALLET QTY], [PALLET STACKS]*[PALLET FACINGS] AS MultiSlot FROM [PI COUNT] GROUP BY [PI COUNT].[SLOT ID], [PI COUNT].[ITEM #], [PI COUNT].[ITEM DESCRIPTION], [PI COUNT].[SLOT STATUS], [PI COUNT].[SLOT USAGE], [PI COUNT].[SLOT HEIGHT], Left([SLOT ID],1), [PALLET STACKS]*[PALLET FACINGS]) AS PICONSOLIDATED

WHERE ((([PICONSOLIDATED].[ITEM #]) In (SELECT Tmp.[ITEM #] FROM [PICONSOLIDATED] AS Tmp WHERE (((Tmp.[SLOT USAGE])='Prime')) GROUP BY Tmp.[ITEM #] HAVING (((Count(*))>1)))) AND (([PICONSOLIDATED].[SLOT USAGE])='Prime')) ORDER BY [PICONSOLIDATED].[ITEM #]) AS DUPS

INNER JOIN ItemReport ON DUPS.[ITEM #] = ItemReport.[ITEM#] WHERE (((DUPS.[SLOT ID]) <> [ItemReport].[PRIME SLOT ID])) ORDER BY DUPS.[ITEM #];

I appreciate any help, pretty new to all this.

Regards,
Jonathan