SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 MS Access
 Correlated query returning only 1 record & repeat
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bretedward
Starting Member

USA
7 Posts

Posted - 08/10/2014 :  17:48:06  Show Profile  Reply with Quote
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

gbritton
Aged Yak Warrior

806 Posts

Posted - 08/10/2014 :  18:48:04  Show Profile  Reply with Quote
duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195644
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000