Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2009
    Posts
    7

    Question Unanswered: Can't get Having Clause to work??

    I'm new to access and I can't get this having clause to work?
    What i'm I doing wrong

    SELECT DISTINCT [ih-O-350].ih_ibid, [ih-O-350].ih_vin, [ih-O-350].ih_eqpinit, [ih-O-350].ih_eqpno, [ih-O-350].ih_carrier, [ih-O-350].ih_iaid, [ih-O-350].ih_idtm, [ih-O-350].ih_purpose, [ih-O-350].ih_idate, [ih-O-350].ih_location, [ih-O-350].ih_rampid, [ie-O-350].ie_arid, [ie-O-350].ie_ardesc, [ie-O-350].ie_tyid, [ie-O-350].ie_tydesc, [ie-O-350].ie_svid, [ie-O-350].ie_svdesc
    FROM [ih-O-350] INNER JOIN [ie-O-350] ON ([ih-O-350].ih_ibid=[ie-O-350].ie_ibid) AND ([ih-O-350].ih_ihid=[ie-O-350].ie_ihid);
    GROUP BY [ih-O-350].ih_vin
    HAVING(((Count(ih-O-350.ih_vin))>1));

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First drop the ; before GROUP BY.

    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It still won't work.

    Also get rid of the DISTINCT - it has no meaning in a GROUP BY query.
    You then need to ensure that ALL the columns that are not aggregated (e.g. aren't COUNTed, SUMed, MAXed etc) are in the GROUP BY clause. In this case, it is every column in your SELECT.
    Unfortunately, although your query would now be syntactically correct - it will NEVER return any rows.

    How about you explain what you want it to do? Perhaps if you show some data in the table, and the result you want it would help.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, reading a third time it *might* return rows. Depends on the table relationship
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2009
    Posts
    7
    I'm try to get all the VINS, but there are duplicate vins so I want to eliminate the duplicate rows that have this VINS. Hope that makes sense.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Once more drop the DISTINCT.

    Have a nice day!

  7. #7
    Join Date
    Mar 2009
    Posts
    7

    this is what I have....

    SELECT [ih-O-350].ih_ibid, [ih-O-350].ih_vin, [ih-O-350].ih_eqpinit, [ih-O-350].ih_eqpno, [ih-O-350].ih_carrier, [ih-O-350].ih_iaid, [ih-O-350].ih_idtm, [ih-O-350].ih_purpose, [ih-O-350].ih_idate, [ih-O-350].ih_location, [ih-O-350].ih_rampid, [ie-O-350].ie_arid, [ie-O-350].ie_ardesc, [ie-O-350].ie_tyid, [ie-O-350].ie_tydesc, [ie-O-350].ie_svid, [ie-O-350].ie_svdesc
    FROM [ih-O-350] INNER JOIN [ie-O-350] ON ([ih-O-350].ih_ibid=[ie-O-350].ie_ibid) AND ([ih-O-350].ih_ihid=[ie-O-350].ie_ihid);
    GROUP BY [ih-O-350].ih_vin
    HAVING (((Count(ih-O-350.ih_vin))>1));

    Error message: The LEVEL clause includes a reserved word or arguement that is misspelled or missing, or the punctuation is incorrect.

    I tried going through all the lines to make sure all is correct.
    thanks again.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Once more: Drop the ; before the GROUP BY !!! The ; is a terminator in SQL and must only appear at the end of the phrase.

    Have a nice day!

  9. #9
    Join Date
    Mar 2009
    Posts
    7

    another error

    SELECT [ih-O-350].ih_ibid, [ih-O-350].ih_vin, [ih-O-350].ih_eqpinit, [ih-O-350].ih_eqpno, [ih-O-350].ih_carrier, [ih-O-350].ih_iaid, [ih-O-350].ih_idtm, [ih-O-350].ih_purpose, [ih-O-350].ih_idate, [ih-O-350].ih_location, [ih-O-350].ih_rampid, [ie-O-350].ie_arid, [ie-O-350].ie_ardesc, [ie-O-350].ie_tyid, [ie-O-350].ie_tydesc, [ie-O-350].ie_svid, [ie-O-350].ie_svdesc
    FROM [ih-O-350] INNER JOIN [ie-O-350] ON ([ih-O-350].ih_ibid=[ie-O-350].ie_ibid) AND ([ih-O-350].ih_ihid=[ie-O-350].ie_ihid)
    GROUP BY [ih-O-350].ih_vin
    HAVING (((Count(ih-O-350.ih_vin))>1));

    I changed what you suggested. Now i'm getting this error:

    Syntax error (missing operator) in query expression
    '(((Count(ih-O-350.ih_vin))>1))'.

    Sorry I am a newbie...

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No problem

    Check for missing spaces between ie_svdesc and FROM, and between ih_vin and HAVING.

    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •