>Hi,
>
>The following is part of an SQL union, the problem is that I am getting two duplicate records instead of one.
>
>As far as I was aware UNION enliminates duplicates, whereas UNION ALL with give you all records
>
>Both tables are identical i.e. a main transaction file and an archive file
>
>

>Select Table1.field1, COUNT(*) as total ;
>FROM Table1 ;
>GROUP BY 1 ;
>UNION ;
>select Table2.field1, COUNT(*) as total ;
>FROM Tabel2 ;
>GROUP BY 1 ;
>order by 1 ;
>INTO Cursor curCursor1 nofilter
>

>
>Thanks in advance

Neil,

Union eliminates duplicate records - not duplicate fields


If one table is an archive and does not contain anything that is in Table2, would the following not make more sense ?

	select	Field1, ;
		sum(Total) as Total ;
	from ( ;
		select	Field1, ;
			cnt(*) as Total ;
		from Table1 ;
		group by 1;
		union all ;
		select	Field1, ;
			cnt(*) as Total ;
		from Table2 ;
		group by 1;
	) xxx ;
	group by 1