How to get a union query to return a single row per case?

[Rramjet]

I have a union query

SELECT CLIENT.ID, CLIENT.[First Name], CLIENT.[Last Name], CLIENT.[Team], CLIENT.[Referral date], CLIENT.[Closed date]," " as SDQ_Tot, " " as SDQ_P, " " as SDQ_C, " " as SDQ_O, " " as SDQ_T, " " as SDQ_S
FROM CLIENT
UNION
SELECT CROSSTAB_SDQ.ID, " ", " ", " ", " ", " ", CROSSTAB_SDQ.[Total], CROSSTAB_SDQ.P, CROSSTAB_SDQ.C, CROSSTAB_SDQ.O, CROSSTAB_SDQ.T, CROSSTAB_SDQ.S


FROM CROSSTAB_SDQ;

that returns

ID	F name	L Name	Team	Date 1	Date 2	Tot	P	C	O	T	S
1						3		1		2
1	Bill	Duck	X	1/1/1900	2/2/2000
2						5	1		3		1
2	Bob	Weave	Y	1/1/1900	2/2/2000

However, I need

ID	F name	L Name	Team	Date 1	Date 2	Tot	P	C	O	T	S
1	Bill	Duck	X	1/1/1900	2/2/2000	3		1		2
2	Bob	Weave	Y	1/1/1900	2/2/2000	5	1		3		1

Is that possible to achieve?

Then I want to add more unions to that query (there are a number of other counts and totals I need to bring in).

Thanks,
Rramjet.

[June7]

Looks like to me should JOIN Client to Crosstab_SDQ instead of UNION.

[Rramjet]

Okay - but if I simply replace "UNION" with "JOIN" that does not work. I am going to have to go and do some study as I have no idea how a "JOIN" SQL might be consrtucted (Unless you can quickly rewrite my UNION to conform to JOIN SQL syntax...

[Rramjet]

Okay - Yeah, right - I got it. Between the tables in a SELECT query (in Query Design) you can define the type of join by double clicking on the relationship line and specifying what you want displayed. In my case I need an "OUTER JOIN" to display all records from CLIENT and only those from the crosstab qeries where the "joined fields are equal". And no SQL coding necessary... (you can view it of course, but I don't need to in this case).

Thanks heaps June 7!