Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Records Count (MSACCESS 2003)

    I am trying to count how many primary records in a table contain children in a related table, ie count only those that have related children (not the number of children)

    SELECT TDOCS.*, TATH.Title
    FROM TDOCS INNER JOIN TATH ON TDOCS.DOCID = TATH.DOCID;

    Wanting to count the number of records in TDOCS that have a title entry in TATH.

    Any suggestions on a way to do it, thanks

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

    Re: Records Count (MSACCESS 2003)

    Try

    SELECT COUNT(*) AS CountOfPrimaryRecords
    FROM TDOCS
    WHERE DOCID In (SELECT DOCID FROM TATH)

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records Count (MSACCESS 2003)

    Thanks Hans. I put the SQL into a listbox and it works. Is there a way I can get the figure into a variable to show the value in a textbox. Thankyou

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

    Re: Records Count (MSACCESS 2003)

    You can create a query with the SQL that I posted and save it as - for example - qryCount.

    Create a text box on your form or report with control source

    =DLookup("CountOfPrimaryRecords","qryCount")

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records Count (MSACCESS 2003)

    Many thanks Hans, just what I wanted. If I don't come back before Christmas, all the very best wishes to you for XMAS and the New Year.

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

    Re: Records Count (MSACCESS 2003)

    Thanks, and the same to you!

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records Count (MSACCESS 2003)

    Spoke too soon. I am now trying to get the opposite of:
    Mysql = Mysql & "WHERE (((TDOCS.DOCID) in (SELECT DOCID FROM TATH)))"

    I have tried Not In, NotIn !!, <> !!

    Thanks

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

    Re: Records Count (MSACCESS 2003)

    Try

    Mysql = Mysql & "WHERE DOCID Not In (SELECT DOCID FROM TATH)"

    Please note that using Not In results in slow performance if the number of records involved is large.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records Count (MSACCESS 2003)

    Worked nice and fast for me. Thanks again.

Posting Permissions

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