This is a MS Access, SELECT syntax query
I have two tables ...
AUDIT_HEADER - the link field is TRAN_NUMBER
AUDIT_SPLIT - the link field is HEADER_TRAN_NUMBER
Relevant fields are ..
AUDIT_HEADER.TRAN_NUMBER
AUDIT_HEADER.ITEM_COUNT
AUDIT_SPLIT.TRAN_NUMBER
AUDIT_SPLIT.HEADER_TRAN_NUMBER
What I am trying to write is a select query that shows me the AUDIT_SPLIT records where the number of AUDIT_SPLIT records when grouped by AUDIT_SPLIT.HEADER_TRAN_NUMBER do not equal the AUDIT_HEADER.ITEM_COUNT.
This is my feeble attemp:
which has the following error:Code:SELECT AUDIT_SPLIT.HEADER_TRAN_NUMBER, Count(AUDIT_SPLIT.HEADER_TRAN_NUMBER) AS CountOfHEADER_TRAN_NUMBER FROM AUDIT_HEADER INNER JOIN AUDIT_SPLIT ON AUDIT_HEADER.TRAN_NUMBER = AUDIT_SPLIT.HEADER_TRAN_NUMBER GROUP BY AUDIT_SPLIT.HEADER_TRAN_NUMBER WHERE [AUDIT_HEADER].[ITEM_COUNT] <> Count(AUDIT_SPLIT.HEADER_TRAN_NUMBER) ;
Syntax error (missing operator) in query expression 'AUDIT_SPLIT.HEADER_TRAN_NUMBER WHERE [AUDIT_HEADER].[ITEM_COUNT] <> Count(AUDIT_SPLIT.HEADER_TRAN_NUMBER)'
Any thoughts very welcome




Reply With Quote