Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rows missing in query results (2000)

    If I select December 2003 in the startup form of the attached mdb and launch Query6M, you get 2 rows in the result but Query6M is based also on Query5M which returns 4 rows. How come the rows whose Descr0 field returns "MANCANZA-ACQUA-MANUTENZIONE" and "MANUTENZIONE" in Query5M are not returned in Query6M?

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

    Re: Rows missing in query results (2000)

    Query6M is based not only on Query5M, but also on Query5_2M, Query5_3M and Query5_4M. Query5_3M returns only 2 records because QueryFermi3M returns only 2 records. Since you used inner joins in Query6M, you get only records for which Descr0 occurs in all contributing queries.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rows missing in query results (2000)

    Thank you Hans,
    So how should I structure Query6M in order to show the missing rows?

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

    Re: Rows missing in query results (2000)

    1. Remove the join between Query5_2M and Query5_3M.
    2. Create a join between Query5M and Query5_2M on Descr0.
    3. Double click each of the three join lines in turn, and select the option to include ALL records from Query5M. You may have to move things around in the query design window to be able to click each line.
    4. change the expression on which you put a condition to

    Nz([query5M].[totale],0)+Nz([Query5_2M].[totale],0)+Nz([query5_3M].[totale],0)+Nz([query5_4M].[totale],0)

    The SQL becomes

    SELECT Query5_2M.Descr0, Nz(Query5M.[1],0)+Nz(query5_4M.[1],0)+Nz(Query5_2M.[1],0)+Nz(query5_3M.[1],0) AS 1, Nz(Query5M.[2],0)+Nz(query5_4M.[2],0)+Nz(Query5_2M.[2],0)+Nz(query5_3M.[2],0) AS 2, Nz(Query5M.[3],0)+Nz(query5_4M.[3],0)+Nz(Query5_2M.[3],0)+Nz(query5_3M.[3],0) AS 3, Nz(Query5M.[4],0)+Nz(query5_4M.[4],0)+Nz(Query5_2M.[4],0)+Nz(query5_3M.[4],0) AS 4, Nz(Query5M.[5],0)+Nz(query5_4M.[5],0)+Nz(Query5_2M.[5],0)+Nz(query5_3M.[5],0) AS 5, Nz(Query5M.[6],0)+Nz(query5_4M.[6],0)+Nz(Query5_2M.[6],0)+Nz(query5_3M.[6],0) AS 6, Nz(Query5M.[7],0)+Nz(query5_4M.[7],0)+Nz(Query5_2M.[7],0)+Nz(query5_3M.[7],0) AS 7, Nz(Query5M.[8],0)+Nz(query5_4M.[8],0)+Nz(Query5_2M.[8],0)+Nz(query5_3M.[8],0) AS 8, Nz(Query5M.[9],0)+Nz(query5_4M.[9],0)+Nz(Query5_2M.[9],0)+Nz(query5_3M.[9],0) AS 9, Nz(Query5M.[10],0)+Nz(query5_4M.[10],0)+Nz(Query5_2M.[10],0)+Nz(query5_3M.[10],0) AS 10, Nz(Query5M.[11],0)+Nz(query5_4M.[11],0)+Nz(Query5_2M.[11],0)+Nz(query5_3M.[11],0) AS 11, Nz(Query5M.[12],0)+Nz(query5_4M.[12],0)+Nz(Query5_2M.[12],0)+Nz(query5_3M.[12],0) AS 12, query5M.totale+Query5_2M.totale+query5_3M.totale+q uery5_4M.totale AS totale
    FROM ((Query5_3M RIGHT JOIN Query5M ON Query5_3M.Descr0 = Query5M.Descr0) LEFT JOIN Query5_4M ON Query5M.Descr0 = Query5_4M.Descr0) LEFT JOIN Query5_2M ON Query5M.Descr0 = Query5_2M.Descr0
    WHERE (((Nz([query5M].[totale],0)+Nz([Query5_2M].[totale],0)+Nz([query5_3M].[totale],0)+Nz([query5_4M].[totale],0))<>0));

    or a slight variation thereof (depending on how you created the joins).

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rows missing in query results (2000)

    Giorgo,

    I don't know if I understand the problem correctly.
    I create an union query (Query6MUnion) with the 4 M5 queries to get all the lines of each query.
    Then I create a total query (Query6MTotal) based on Query6MUnion to sum all the columns grouped by Descr0.
    Is it that what you want ?
    Francois

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rows missing in query results (2000)

    Perfect! Thank you Francois

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rows missing in query results (2000)

    Hans,
    I've implemented your solution(see attachment). The problem is that now Query6M fetches all the rows that are in Query5M but disregards any rows that are present in the other queries but not in Query5M. How can I modify Query6M so that it encompasses all the rows in all the queries(obviously summing the values where the description is the same)?

Posting Permissions

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