Max Date (IF no null values, otherwise, display nulls)

[brandon20]

Hi,


I need to display the max of the date for each Name.
This is would be simple, but I need to add one more condition. Which I am not great at in access.


I need to add the max date by name, IF, there are no null values. If there are null values, I need to display them instead of the Max Date.
Here are my data fields and tables:
[Data].[Name]
[Data].[Current Status]
[Data].[End Date]


Here is an example of what I have

Name <tbody> </tbody>	End Date <tbody> </tbody>	Current Status <tbody> </tbody>
A <tbody> </tbody>	10/1/2015 <tbody> </tbody>	CLOSED <tbody> </tbody>
A	9/1/2015 <tbody> </tbody>	CLOSED
A	8/1/2015 <tbody> </tbody>	CLOSED
A	7/1/2015 <tbody> </tbody>	CLOSED
B <tbody> </tbody>		OPEN
B	10/1/2015	CLOSED
B	9/1/2015	CLOSED
B	8/1/2015	CLOSED

<tbody>
</tbody>

and this is what I would want to display from this:

Name	End Date	Current Status
A	10/1/2015	Closed
B		Open

<tbody>
</tbody>

Is there an easy way to write an equation in access to do this?
Thank you for any help.

 

[alansidman]

Here is a Query that will get you the max date for closed and display the Open

SELECT Table1.Name, Max(Table1.[End Date]) AS [MaxOfEnd Date], Table1.[Current Status]
FROM Table1
GROUP BY Table1.Name, Table1.[Current Status];

 

[brandon20]

Hi,

Thanks, but that does not display what I am looking for. This query gives me the max date of the closed and also displays the open.

I need the query to display the Closed if and only if there is no Open for that Name in Current Status, If there is an Open, display only the Open.

Thanks again!


Current data displayed:

Name	End Date	Current Status
A	10/1/2015	Closed
A		Open

<tbody>
</tbody>

What I need displayed:

Name	End Date	Current Status
A		Open

<tbody>
</tbody>

 

[james_lankford]

kind of sounds like a union query

do one query for the closed and one for the open

adjust the "where clause" to limit to what you want

 

[brandon20]

YES! thank you.

I had read about Union queries but just overlooked them quickly.

This worked great and will come in very handy in the future.