>>>Hi All,
>>>
>>>I have 5 tables all with the same fields, one of which is the date. I want to write a query that will return a single row for each date that contains the values of all the other fields in each table. I have tried using the distinct key word in my select statement, but it only seem to work on a single column.
>>>
>>>What I get is multiple rows for each date, with some columns having a null value.
>>>
>>>Does anyone have any ides how to accomplish the seeming simple task?
>>>
>>>Thanks,
>>>
>>>Jim
>>
>>
>>select distinct from (select T1.Fields from Table1 T1 UNION ALL Select T2.Fields from Table2 T2 etc.) X
>>
>>Or please explan in more details what exactly do you want.
>
>Thanks Naomi,
>
>Her is the statement written as you suggest.
>
>SELECT DISTINCT FROM(select Well_5.DailyRuntime, Well_5.DailyStarts, Well_5.DailyFlowrate from Well_5 union all
> select Well_6.DailyRuntime, Well_6.DailyStarts, Well_6.DailyFlowrate from Well_6)
>
>this returns an error 'Incorrect syntax near the keyword FROM'
>
>I have three additional tables representing other wells that have the same columns and also need to be included in this query. I also need to retrieve one of the date columns from any of the tables since they all have data for the requested date.
>
>Thanks again for your help,
>
>Jim

You should alias the derived table and also you should have at least one field in field list part of the SELECT:

SELECT DISTINCT ??????
       FROM (select Well_5.DailyRuntime, Well_5.DailyStarts, Well_5.DailyFlowrate from Well_5
             union all
             select Well_6.DailyRuntime, Well_6.DailyStarts, Well_6.DailyFlowrate from Well_6) Tbl1

BTW SELECT Distinct is redundant in that case. If you use just use UNION not UNION ALL that will give you DISTINCT records

select Well_5.DailyRuntime, Well_5.DailyStarts, Well_5.DailyFlowrate from Well_5
union
select Well_6.DailyRuntime, Well_6.DailyStarts, Well_6.DailyFlowrate from Well_6