[RESOLVED] Using GROUP, COUNT and WHERE in SELECT

[chilling]

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:

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)  ;

which has the following error:
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

[szlamany]

We don't do ACCESS here...

But what you are attempting - having a WHERE clause with an AGGREGATE, is not permitted - it requires the use of a HAVING clause.

This might work:

HAVING [AUDIT_HEADER].[ITEM_COUNT] <> Count(AUDIT_SPLIT.HEADER_TRAN_NUMBER)

But I don't have SQL to play with right now...

HAVING is evaluated on the "working resultset" after any WHERE clauses. Since the "working resultset" has a GROUP BY - that GROUP BY is applied first and then the HAVING clause "limits" rows from the "working resultset"...

[chilling]

Thanks - that pointed me in the right direction.

SELECT AUDIT_SPLIT.[HEADER_TRAN_NUMBER], [AUDIT_HEADER].[ITEM_COUNT],  
           Count(AUDIT_SPLIT.[HEADER_TRAN_NUMBER]) AS CountOfHEADER_TRAN_NUMER
  FROM AUDIT_HEADER 
    INNER JOIN AUDIT_SPLIT 
    ON AUDIT_HEADER.TRAN_NUMBER=AUDIT_SPLIT.[HEADER_TRAN_NUMBER]
  GROUP BY AUDIT_SPLIT.[HEADER_TRAN_NUMBER],  [AUDIT_HEADER].[ITEM_COUNT]
  HAVING  [AUDIT_HEADER].[ITEM_COUNT]=Count(AUDIT_SPLIT.HEADER_TRAN_NUMBER);

Re Access - I tend to use access to sort out queries when I am having problems rather than try and do it in code.