Correlated query returning only 1 record & repeating

[bretedward]

[FONT=&quot]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.
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]The desired output is:[/FONT]
[FONT=&quot]Yellow[/FONT][FONT=&quot] Blue[/FONT]
[FONT=&quot]11/23/2013 11/19/2013[/FONT]
[FONT=&quot]11/19/2103 10/01/2012[/FONT]
[FONT=&quot]10/01/2102 10/08/2010[/FONT]
[FONT=&quot]10/08/2010 12/14/2007[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]The actual output is:[/FONT]
[FONT=&quot]Yellow[/FONT][FONT=&quot] Blue[/FONT]
[FONT=&quot]11/23/2013 11/19/2013[/FONT]
[FONT=&quot]11/19/2103 11/19/2013[/FONT]
[FONT=&quot]10/01/2102 11/19/2013[/FONT]
[FONT=&quot]10/08/2010 11/19/2013[/FONT]
[FONT=&quot]11/23/2013 10/01/2102[/FONT]
[FONT=&quot]11/19/2103 10/01/2102[/FONT]
[FONT=&quot]10/01/2102 10/01/2102[/FONT]
[FONT=&quot]10/08/2010 10/01/2102[/FONT]
[FONT=&quot]The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.[/FONT]


[FONT=&quot]Here is the SQL:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]SELECT Long_List.Yellow,Short_List.Blue[/FONT]
[FONT=&quot]FROM [/FONT]
[FONT=&quot]([/FONT]
[FONT=&quot]SELECT DISTINCT BirthDate AS Blue[/FONT]
[FONT=&quot]FROM ([/FONT]
[FONT=&quot]SELECT DISTINCT BirthDate FROM citizens[/FONT]
[FONT=&quot]UNION[/FONT]
[FONT=&quot]SELECT DISTINCT DeathDate FROM citizens[/FONT]
[FONT=&quot]WHERE DeathDate IS NOT NULL[/FONT]
[FONT=&quot])[/FONT]
[FONT=&quot]WHERE BirthDate <([/FONT]
[FONT=&quot]SELECT MAX(Pink)[/FONT]
[FONT=&quot]FROM [/FONT]
[FONT=&quot]([/FONT]

[FONT=&quot]SELECT DISTINCT BirthDate AS Pink[/FONT]
[FONT=&quot]FROM ([/FONT]
[FONT=&quot]SELECT DISTINCT BirthDate FROM citizens[/FONT]
[FONT=&quot]UNION[/FONT]
[FONT=&quot]SELECT DISTINCT DeathDate FROM citizens[/FONT]
[FONT=&quot]WHERE DeathDate IS NOT NULL[/FONT]
[FONT=&quot])[/FONT]
[FONT=&quot])[/FONT]
[FONT=&quot])[/FONT]
[FONT=&quot]ORDER BY BirthDate DESC[/FONT]
[FONT=&quot]) AS Short_List[/FONT]
[FONT=&quot],[/FONT]
[FONT=&quot]([/FONT]
[FONT=&quot]SELECT DISTINCT BirthDate AS Yellow[/FONT]
[FONT=&quot]FROM ([/FONT]
[FONT=&quot]SELECT DISTINCT BirthDate FROM citizens[/FONT]
[FONT=&quot]UNION[/FONT]
[FONT=&quot]SELECT DISTINCT DeathDate FROM citizens[/FONT]
[FONT=&quot]WHERE DeathDate IS NOT NULL[/FONT]
[FONT=&quot])[/FONT]
[FONT=&quot]WHERE BirthDate > ([/FONT]
[FONT=&quot]SELECT MIN(Red)[/FONT]
[FONT=&quot]FROM[/FONT]
[FONT=&quot]([/FONT]

[FONT=&quot]SELECT DISTINCT BirthDate AS Red[/FONT]
[FONT=&quot]FROM ([/FONT]
[FONT=&quot]SELECT DISTINCT BirthDate FROM citizens[/FONT]
[FONT=&quot]UNION[/FONT]
[FONT=&quot]SELECT DISTINCT DeathDate FROM citizens[/FONT]
[FONT=&quot]WHERE DeathDate IS NOT NULL[/FONT]
[FONT=&quot])[/FONT]
[FONT=&quot])[/FONT]
[FONT=&quot])[/FONT]
[FONT=&quot]ORDER BY BirthDate DESC[/FONT]
[FONT=&quot]) AS Long_List[/FONT]
[FONT=&quot]ORDER BY Short_List.Blue DESC,Long_List.Yellow DESC[/FONT]

 

[vbaInet]

Welcome to the forum!

Can you please edit your post and re-paste a formatted SQL enclosing it in code tags so that we can see which parts are nested and which aren't. Here's how to use code tags:

http://www.access-programmers.co.uk/forums/showthread.php?t=200247

 

[plog]

Here's the code parsed and wrapped in tags:

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
				)
			)
		)
	) 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
				)
			)
		)
	) AS Long_List
ORDER BY Short_List.Blue DESC,Long_List.Yellow DESC

You can't go for broke in a query this complex. If it doesn't work (like where you are now), its near impossible to debug. What you should do is break all the sub-queries into individual queries to make sure they return the results you expect.

Second, ORDER BY clauses in sub-queries are noise--they don't add anything to the mix they're just more code that the end user has to look at.

Third, and what I think is your issue--this query is a cartesian product (http://en.wikipedia.org/wiki/Cartesian_product) of the same underlying data source. There are no 'sides' to this query. Your main FROM clause has no JOIN in it much less a RIGHT or LEFT one.

Looking at the code ShortList and LongList return the exact same data. What your main query does is just bring all the data in them together indiscriminately. You don't join them together so every record in ShortList gets matched with every record in LongList.

I don't think you can get to where you want to go from this query. I finally wrapped my mind about what this query actually does, but I don't know what your intentions are. So, can you explain to me in plain English what it is you are trying to do? In your explanation you are not allowed to reference this query, you can only tell me about the table citizens and the data in it.

 

[bretedward]

Thanks.

I have tested all of the sub-queries and they all work. If I switch the short_list and long_list the problem follows the move - the sub-query on the right of the FROM only gets 1 record. It may very well be the Cartesian Product issue.

I tried using the joins, but they produced the same undesired result.

What I am trying to do is get all of the birth dates and death dates (without any NULL death dates) into 1 column and eliminate any duplicates. That works fine.

Then have 2 columns with the same data except 1 column will not have maximum date and the other column will not have the minimum date.

[FONT=&quot]The desired output is:[/FONT]
[FONT=&quot]Yellow[/FONT][FONT=&quot] Blue[/FONT]
[FONT=&quot]11/23/2013 11/19/2013[/FONT]
[FONT=&quot]11/19/2103 10/01/2012[/FONT]
[FONT=&quot]10/01/2102 10/08/2010[/FONT]
[FONT=&quot]10/08/2010 12/14/2007[/FONT]

 

[plog]

Let's try this again:

So, can you explain to me in plain English what it is you are trying to do? In your explanation you are not allowed to reference this query, you can only tell me about the table citizens and the data in it.

Can you show me starting data from citizens? Make it as dirty as possible (include nulls, etc) and then what the desired output would be based on that sample data.

 

[bretedward]

Here it is the citizens table:

Name BirthDate DeathDate
Jerry Smith 12/14/2007 11/19/2013
John Johnson 10/08/2010 11/23/2013
Will Williams 10/01/2012
Sandy Sanders 10/08/2010 11/23/2013
Rosa Rosenthal 11/19/2013

 

[bretedward]

[FONT=&quot]The desired output is:[/FONT]

[FONT=&quot]Yellow[/FONT][FONT=&quot] Blue[/FONT]
[FONT=&quot]11/23/2013 11/19/2013[/FONT]
[FONT=&quot]11/19/2013 10/01/2012[/FONT]
[FONT=&quot]10/01/2102 10/08/2010[/FONT]
[FONT=&quot]10/08/2010 12/14/2007[/FONT]

 

[vbaInet]

Here's the Short_list part re-written:

	(SELECT DISTINCT BirthDate AS Blue
	 FROM 
		(SELECT BirthDate
		 FROM citizens
		 UNION
		 SELECT DeathDate 
		 FROM citizens
		 WHERE DeathDate IS NOT NULL
		)
	 WHERE BirthDate <
		(SELECT Max(BirthDate) 
		 FROM citizens
		 UNION
		 SELECT Max(DeathDate) 
		 FROM citizens
		)
	)

Obviously repeat the same for the other side swapping Max() for Min().

For the full outter join, since the dates are going to be unique, you can:
1. Sort by the date field
2. Create an incremental field in both queries
3. Create another query based on step 2
4. Join both queries on the numbered field

Or

1. Build two subreports based on the queries
2. Place them side-by-side

My preference being option 2.

 

[plog]

I can name that tune in 2 queries:

SELECT BirthDate AS EventDate
FROM citizens
GROUP BY BirthDate
HAVING (BirthDate Is Not Null)
UNION SELECT DeathDate As EventDate
FROM citizens
GROUP BY DeathDate
HAVING (DeathDate Is Not Null);

Paste that into a new query and name it 'sub_DateRanges'. Then to produce the results you want, this is the SQL:

SELECT EventDate AS Yellow, DateValue(DMax("EventDate","sub_DateRanges","[EventDate]<#" & [EventDate] & "#")) AS Blue
FROM sub_DateRanges
WHERE (EventDate)>DMin("[EventDate]","sub_DateRanges")
ORDER BY EventDate DESC;

 

[vbaInet]

In retrospect all that the Min() and Max() criteria is doing is returning all records except the min and max dates in Yellow and Blue respectively. So I wonder if the OP has actually thought this through.

 

[bretedward]

Yes, vbaInet, the intent was just that - one column with all dates except min and the other column with all dates except max. I know that it seems kind of strange, but that is what I need to achieve.

Thanks for your help.

 

[vbaInet]

Just making sure you're clear about your objective.

Have you looked at the queries provided already?

NB: Your main query should be:

SELECT BirthDate AS EventDate
FROM citizens
UNION
SELECT DeathDate AS EventDate
FROM citizens
WHERE DeathDate IS NOT NULL

...because UNION will perform a DISTINCT filter on both tables anyway (something I missed in my post).

Get the SQL working first then convert the DMin() and DMax() functions in plog's post to subqueries. If you need proper side-by-side, you'll still need to number each record and join by that field or display both in subreports.