Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Posts
    4

    Question Unanswered: count how many documents are in the table for each name (was "Query Help")

    Hello,

    Brief overview. Got 2 tables, client table and document table. Both tables have client name as the primary key. Client table shows client info, address, phone, dob. Document table shows client name, document, document type.
    I need to write a query that will count how many documents are in the table for each name.

    This is attempt at it, please let me know whats wrong. Thanks.

    SELECT count [client table].client name as cli_name, count ([document table].name as doc_qty)
    FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
    GROUP BY [client table].name
    ORDER BY [client table].name

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Your syntax is funny:
    Try
    count ([document table].name) as doc_qty
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jan 2007
    Posts
    4
    wow, that was it, thanks a lot

  4. #4
    Join Date
    Jan 2007
    Posts
    6
    Agree also no count at the start.

    Code:
    SELECT [client table].client name as cli_name, count ([document table].name) as doc_qty
    
    FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
    GROUP BY [client table].name
    ORDER BY [client table].name
    Also so check the ID`s think they might be wrong or badly named.
    Ie. the document table link on id not clientID

    If it does not work post the tables as well

  5. #5
    Join Date
    Jan 2007
    Posts
    4
    yeah, the id thing was throwing it off too, i had to rejoin by name and client name from both tables after I gave some thought as to what exactly this query needed to do

    working query:
    SELECT [client table].[client name] as cli_name, count ([document table].[name]) as doc_qty
    FROM [client table] INNER JOIN [document table] ON [client table].[client name] = [document table].name
    GROUP BY [client table].[client name]
    ORDER BY [client table].[client name];

    Had another question, I'm supposed to place a constraint into both tables to lock them and not allow any new data to be entered. I have read how to write it in code, but am using access. Is there any way to modify and create tables in access2003 in sql view? I really cant stand the GUI.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Sure, you can use a CREATE TABLE statement just as you would using Query Analyzer.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jan 2007
    Posts
    4
    sweet, thanks a lot for making my life a whole lot easier(for today atleast)

Posting Permissions

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