Can I combine fields without using UNION?

[Guest chuckie]

I have two tables, call them table A and B. Both tables have a field, call it DATE and another field, call it LOCATION.I want a query that will return a record set that that contains:All of the fields from table ABUTFor the DATE field, it should get the Max(DATE) field vale for the same LOCATION in table B and if that date is newer, use that instead of the date held in table A.Does that make sense? I can't use a UNION because my two datasets are not the same in column count etc. I have managed to do a LEFT JOIN which returned all the fields from table A AND the Max(DATE) field from table B as a seperate column. But then I can't sort teh dataset because the dates are fragmented over two columns.Can you help?Thanks

[jesh]

Perhaps something along the lines of this?

SELECT A.Location, 	(CASE WHEN Max(B.Date) > Max(A.Date) THEN Max(B.Date) ELSE Max(A.Date) END) AS DateFROM A	LEFT JOIN B ON A.Location = B.LocationGROUP BY A.Location, B.Location

Or perhaps this:

SELECT Location, Max(Date)FROM (	SELECT A.Location, A.Date FROM A	UNION	SELECT B.Location, B.Date FROM B)GROUP BY Location