Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Distinct & Count statement in Access (Access 2000)

    I've read up that the standard sql Distinct-Count statement does not work in access. Anyhow, I was able top get this working in one query

    SELECT Count(tblSheets.[RefDWG]) AS CountOfRefDWG
    FROM [SELECT [RefDWG] FROM [tblSheets] GROUP BY [RefDWG]]. AS TOTALPMLS;

    However when I modified it for another query like the one below, it comes up with an error message saying "Invalid Bracketing of name 'SELECT [ExcelsheetName'

    SELECT Count(tblSheets.[ExcelsheetName]) AS CountOfExcelSheetName
    FROM [SELECT [ExcelsheetName] FROM [tblSheets] GROUP BY [ExcelsheetName]]. AS TOTALPMLS;

    The same error message appears even with the original one when I attempt to modify it, very strange.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Distinct & Count statement in Access (Access 2000)

    Try changing:
    SELECT Count(tblSheets.[ExcelsheetName]) AS CountOfExcelSheetName
    FROM [SELECT [ExcelsheetName] FROM [tblSheets] GROUP BY [ExcelsheetName]]. AS TOTALPMLS;
    to:
    SELECT Count(tblSheets.[ExcelsheetName]) AS CountOfExcelSheetName
    FROM (SELECT [ExcelsheetName] FROM [tblSheets] GROUP BY [ExcelsheetName]) AS TOTALPMLS;

    That is use round brackets around the 2nd SELECT statement.

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Distinct & Count statement in Access (Access 2000)

    Hey, what do you know, it worked!. Patt, are you sure your not the guy who invented MS Access [img]/forums/images/smilies/wink.gif[/img]

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Distinct & Count statement in Access (Access 2000)

    Nah, Hans, Wendell, Charlotte and others are way ahead of me.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Distinct & Count statement in Access (Access 2000)

    Don't put yourself down, Pat. You've been doing excellent work for Mark (and for many others)!

Posting Permissions

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