Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014
    Posts
    2

    Unanswered: Correlated query returning only 1 record & repeating

    I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as “Furthermore” will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.

    The desired output is:

    Yellow Blue
    11/23/2013 11/19/2013
    11/19/2103 10/01/2012
    10/01/2102 10/08/2010
    10/08/2010 12/14/2007

    The actual output is:
    Yellow Blue
    11/23/2013 11/19/2013
    11/19/2103 11/19/2013
    10/01/2102 11/19/2013
    10/08/2010 11/19/2013
    11/23/2013 10/01/2102
    11/19/2103 10/01/2102
    10/01/2102 10/01/2102
    10/08/2010 10/01/2102
    The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.

    Here is the SQL:

    SELECT Long_List.Yellow,Short_List.Blue
    FROM
    (
    SELECT DISTINCT BirthDate AS Blue
    FROM (
    SELECT DISTINCT BirthDate FROM citizens
    UNION
    SELECT DISTINCT DeathDate FROM citizens
    WHERE DeathDate IS NOT NULL
    )
    WHERE BirthDate <(
    SELECT MAX(Pink)
    FROM
    (

    SELECT DISTINCT BirthDate AS Pink
    FROM (
    SELECT DISTINCT BirthDate FROM citizens
    UNION
    SELECT DISTINCT DeathDate FROM citizens
    WHERE DeathDate IS NOT NULL
    )
    )
    )
    ORDER BY BirthDate DESC
    ) AS Short_List
    ,
    (
    SELECT DISTINCT BirthDate AS Yellow
    FROM (
    SELECT DISTINCT BirthDate FROM citizens
    UNION
    SELECT DISTINCT DeathDate FROM citizens
    WHERE DeathDate IS NOT NULL
    )
    WHERE BirthDate > (
    SELECT MIN(Red)
    FROM
    (

    SELECT DISTINCT BirthDate AS Red
    FROM (
    SELECT DISTINCT BirthDate FROM citizens
    UNION
    SELECT DISTINCT DeathDate FROM citizens
    WHERE DeathDate IS NOT NULL
    )
    )
    )
    ORDER BY BirthDate DESC
    ) AS Long_List
    ORDER BY Short_List.Blue DESC,Long_List.Yellow DESC

  2. #2
    Join Date
    Aug 2014
    Posts
    2

    Correlated query returning only 1 record & repeating

    I have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as “Furthermore” will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.

    The desired output is:

    Yellow Blue
    11/23/2013 11/19/2013
    11/19/2103 10/01/2012
    10/01/2102 10/08/2010
    10/08/2010 12/14/2007

    The actual output is:

    Yellow Blue
    11/23/2013 11/19/2013
    11/19/2103 11/19/2013
    10/01/2102 11/19/2013
    10/08/2010 11/19/2013
    11/23/2013 10/01/2102
    11/19/2103 10/01/2102
    10/01/2102 10/01/2102
    10/08/2010 10/01/2102
    The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.


    Here is the SQL:

    SELECT Long_List.Yellow,Short_List.Blue
    FROM
    (
    SELECT DISTINCT BirthDate AS Blue
    FROM (
    SELECT DISTINCT BirthDate FROM citizens
    UNION
    SELECT DISTINCT DeathDate FROM citizens
    WHERE DeathDate IS NOT NULL
    )
    WHERE BirthDate <(
    SELECT MAX(Pink)
    FROM
    (

    SELECT DISTINCT BirthDate AS Pink
    FROM (
    SELECT DISTINCT BirthDate FROM citizens
    UNION
    SELECT DISTINCT DeathDate FROM citizens
    WHERE DeathDate IS NOT NULL
    )
    )
    )
    ORDER BY BirthDate DESC
    ) AS Short_List
    ,
    (
    SELECT DISTINCT BirthDate AS Yellow
    FROM (
    SELECT DISTINCT BirthDate FROM citizens
    UNION
    SELECT DISTINCT DeathDate FROM citizens
    WHERE DeathDate IS NOT NULL
    )
    WHERE BirthDate > (
    SELECT MIN(Red)
    FROM
    (

    SELECT DISTINCT BirthDate AS Red
    FROM (
    SELECT DISTINCT BirthDate FROM citizens
    UNION
    SELECT DISTINCT DeathDate FROM citizens
    WHERE DeathDate IS NOT NULL
    )
    )
    )
    ORDER BY BirthDate DESC
    ) AS Long_List
    ORDER BY Short_List.Blue DESC,Long_List.Yellow DESC

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can we see the table design(s), the data, or perhaps best yet the table(s) in a zipped db attached to a post here

    to be honest I cant follow your design where you are referring to yellow, blue

    specifying a required otuput, showing the actual output but not including the data is a bit awkward.

    what you want shoudl eb possible with a properly formed JOIN not a UNION
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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