Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    281
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I have a query with right or is it a left join
    SELECT Ref_SAE.Code, Count(Ref_SAE.Code) AS CompteDeCode
    FROM Ref_SAE LEFT JOIN ETA ON Ref_SAE.Code = ETA.CodeSAE
    WHERE (((ETA.StatutCSST)=Yes))
    GROUP BY Ref_SAE.Code
    ORDER BY Ref_SAE.Code;

    the Ref_SAE table has 32 catégories the eta table has some 12 000 cases joined by the variable codeSAE. But ou the table has no results for REF_SAE 19 and 31.
    I wanted my query to give the count for all 32 categories meaning a zero in the listing for code = 19 and 31

    this is what I get
    code count
    1 2372
    2 22
    3 67
    4 30
    5 190
    6 162
    7 57
    8 39
    9 2
    10 45
    11 385
    12 93
    13 58
    14 13
    15 547
    16 2750
    17 3
    18 40
    20 19
    21 3116
    22 106
    23 70
    24 2
    25 23
    26 773
    27 28
    28 323
    29 488
    30 1106
    32 70

    I thought this would be simple

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,940
    Thanks
    192
    Thanked 723 Times in 659 Posts
    Give this a try:
    Code:
    SELECT Ref_SAE.Code, Count(ETA_SAE.Code) AS CompteDeCode
    FROM Ref_SAE LEFT JOIN ETA ON Ref_SAE.Code = ETA.CodeSAE
    WHERE (((ETA.StatutCSST)=Yes))
    GROUP BY Ref_SAE.Code
    ORDER BY Ref_SAE.Code;
    I tested using a couple of files I have using this query:
    Code:
    SELECT Agencies.Agency, Count([Contracts]![Agency]) AS ContractCnt
    FROM Agencies LEFT JOIN Contracts ON Agencies.Agency = Contracts.Agency
    GROUP BY Agencies.Agency
    ORDER BY Agencies.Agency;
    I received the output below:

    I hope this helps.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,015
    Thanks
    0
    Thanked 2 Times in 2 Posts
    SELECT Ref_SAE.Code, Count(Ref_SAE.Code) AS CompteDeCode
    FROM Ref_SAE LEFT JOIN ETA ON Ref_SAE.Code = ETA.CodeSAE
    WHERE (((ETA.StatutCSST)=Yes))
    GROUP BY Ref_SAE.Code
    ORDER BY Ref_SAE.Code;
    You cannot do this with outer joins if you are querying the child table,
    you need to set up a query first on the child table, then link to the child query with your outer join otherwise the where will eliminate entries that have no entry

    Create a query on child first such as

    Code:
    SELECT ETA.CodeSAE FROM ETA WHERE ETA.StatutCSST=True
    Save this as say qryETACSSTStatusYesCodes

    Then Create a Left Join from Ref Table to this

    Code:
    SELECT Ref_SAE.Code, Count(Ref_SAE.Code) AS CompteDeCode
    FROM Ref_SAE LEFT JOIN qryETACSSTStatusYesCodes 
    ON Ref_SAE.Code = qryETACSSTStatusYesCodes .CodeSAE
    GROUP BY Ref_SAE.Code
    ORDER BY Ref_SAE.Code;
    An alternative would be to use a SUB Query, but in Access this can be quite slow.
    Andrew

  5. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    281
    Thanks
    9
    Thanked 0 Times in 0 Posts
    this worked

    SELECT Ref_SAE.Code, Ref_SAE.Libelle, [sousR_eta sae].CompteDeCodeEtablissement
    FROM Ref_SAE LEFT JOIN [sousR_eta sae] ON Ref_SAE.Code = [sousR_eta sae].CodeSAE
    ORDER BY Ref_SAE.Code;


    where [sousR_eta sae] =

    SELECT ETA.CodeSAE, Count(ETA.CodeEtablissement) AS CompteDeCodeEtablissement
    FROM ETA
    WHERE (((ETA.StatutCSST)=Yes))
    GROUP BY ETA.CodeSAE
    ORDER BY ETA.CodeSAE;


    But can someone give me a link to a microst page that explains why my original query would not work.

  6. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,015
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Your original query would not work, because your WHERE is applying the filter to the result set of the main query.
    So although the LEFT Join will return all rows from the parent,
    the WHERE (((ETA.StatutCSST)=Yes)) targets the result set and thus eliminates any rows that do not match this condition,
    which must be any row that is NOT in the child table.
    By creating the query on the child as a separate query, then the filter is applied to this on it's own first.
    When this is joined to the Parent table with a left join, the result set is a standard outer join query returning all rows from the parent.
    Andrew

Posting Permissions

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