Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax help, I think (Access 97/no sr)

    I know that two separate queries will do the trick: 1st one is your select distinct. 2nd query is based on 1st query and does the count. If anyone knows if this is possible with only one query, please tell us how.

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL syntax help, I think (Access 97/no sr)

    You don't need the DISTINCT, you need a group by, or totals, query. Try something like this:

    SELECT Company, Count([Company]) As CompanyCount
    FROM [main table] GROUP BY [Main table].Company;

    That will give you a list of Company values and the count for each listed Company.
    Charlotte

  3. #3
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax help, I think (Access 97/no sr)

    Hi Charlotte,

    Thank you for your reply, but that's not quite what I was looking for. I don't need a count of how many times each company is represented.

    I need a count of unique values. In my first post above, there are 3 distinct companies. I need the SQL to return 3, not Taunton 3, Better Homes and Gardens 2, etc...

  4. #4
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL syntax help, I think (Access 97/no sr)

    Ok, I've definitely done my homework on this one, but I'm still not getting it.

    For giggles, I've checked;
    SQLCOURSE.COM
    SQLCOURSE2.COM
    ACCESS DATABASE DESIGN & PROGRAMMING BY O'REILLY
    ACCESS 97 UNLEASED BY SAMS
    ACCESS 97 FOR WINDOWS FOR DUMMIES BY JOHN KAUFELD
    BLAH, BLAH, BLAH, ETC...

    I'm trying to write a very simple query with SQL. I think my problem is syntax, not content, so hopefully someone can just point right to it and say "There's your problem..."

    Let's say my data looks like this (my magazine collection <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>);

    <table border=1><td>Taunton</td><td>Taunton</td><td>Taunton</td><td>Better Homes and Gardens</td><td>Better Homes and Gardens</td><td>August Home</td><td>August Home</td><td>August Home</td></table>
    I simply want to count the distinct values. I can do a SQL statement for distinct values; SELECT DISTINCT [main table].Company
    FROM [main table];
    That would return;
    <table border=1><td>Taunton</td><td>Better Homes and Gardens</td><td>August Home</td></table>Instead, I want it to return;
    <table border=1><tr><td>3</td></table>All the reference that I've checked suggest that SELECT count([main table].Company)
    FROM [main table];
    will do the trick, but that statement is returning the number of all values, or
    <table border=1><tr><td>8</td></table>
    I know it's simple, but it still eludes me, even after all of this searching... Can anyone help?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SQL syntax help, I think (Access 97/no sr)

    Hi,
    Try:
    SELECT count(sqryGroup.Company) as CoCount FROM (SELECT DISTINCT [main table].Company from [main table]) As sqryGroup
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: SQL syntax help, I think (Access 97/no sr)

    I didn't think you could have a subquery inside a main query in Access97.
    I have tried it and it came back with an error "Syntax error in From clause".
    When I clicked OK on the error dialogue box it highlighted the SELECT after the left hand bracket.
    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Pat

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL syntax help, I think (Access 97/no sr)

    It's legal, but it's tricky to make work because the quey engine insists on changing the punctuation in SQL view. Don't try to look at the SQL in A97. Create a new query based on the Orders table from the Northwind database, and create this expression as the ony field:

    CoCount: Count(sqryGroup.CustomerID)

    The query should run (at least it does in SR-2), but if you look at it in SQL view, it will probably give you the syntax error you described.
    Charlotte

  8. #8
    New Lounger
    Join Date
    Apr 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax help, I think (Access 97/no sr)

    SELECT DISTINCT COUNT(fieldname) AS howmany
    FROM tablename

    it's that simple

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

    Re: SQL syntax help, I think (Access 97/no sr)

    But how does it know what sQryGroup is, if you have not created a query called sQryGroup.
    I am obviously missing something!

    I went into a new query based upon a table of mine (in a table called [MidWk Hdr Det] theres a date field called DateofVenue) and created just this expression as you described "Cocount: Count(sQryGroup.DateofVenue)" and I get a dialog box requesting entry of parameter sQryGroup.DateofVenue
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Pat

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL syntax help, I think (Access 97/no sr)

    Sorry, but that doesn't work. It will return a count of every record in the table. What you're telling the query engine is to Count the fieldname in tablename and return the unique instance of that count.
    Charlotte

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL syntax help, I think (Access 97/no sr)

    Sorry, I didn't mean to confuse you. You can actually paste Rory's SQL into a query in A2k, save it, and back save it to 97 and it will run ... as long as you don't open it in SQL view.

    Otherwise, you have to create a query from the subquery in Rory's code and use that as the "table" for your count. Since you were seeing brackets rather than parens, I assumed you had managed to save the query but were then having trouble getting it out of SQL view. I actually figured out a way to make that work years ago, but I've forgotten how. It had to do with tweaking the punctuation around the FROM subquery.
    Charlotte

  12. #12
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax help, I think (Access 97/no sr)

    Hi Charlotte, Rory, et al.,

    I've tried the methods suggested, and I'm still confused.

    I built a query in the Northwind database based on Rory's subquery. But then I wasn't able to see how I could relate that to my query in my database.

    Then I went into my database and created a new query with Rory's subquery code. Then I tried creating a second query with the full code in his post, but I got the "...enclose the subquery in paren..." error, and I'm still not sure where to proceed.

    I'm very new to all of this. Can someone spell it out for me step by step?

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL syntax help, I think (Access 97/no sr)

    Create the "subquery" in your database and save it as a query. Then create a new query and in the Add Tables dialog, click on the queries tab. Your saved "subquery" should be in that list and you can add it to your new query just the same way you would a table. Does that help?
    Charlotte

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

    Re: SQL syntax help, I think (Access 97/no sr)

    I have attached a WinZip file containing an example based on your original question.

    It contains one table (Main Table) and two queries (qryUniqueCompanies and qryCountCompanies).

    Note: Although it might be possible to do it in one query in Access 2000 and then convert it to 97, I don't think that it is possible to create a single query in Acces 97.
    Attached Files Attached Files

  15. #15
    New Lounger
    Join Date
    Apr 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax help, I think (Access 97/no sr)

    I don't see why you said my query didn't work...

    You can have
    1 DOG
    2 DOG
    3 CAT
    4 CAT
    5 Cat
    The number needed to be returned would be 2, that exactly why my query would do.

Page 1 of 2 12 LastLast

Posting Permissions

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