Join Properties; DISTINCT to DISTINCT possible?

[jezjez]

Hi,

In the standard Join Properties box you have three option:

1 Only rows where joined fields both tables are equal
2 All records from table a and only those from table b where joined fields are equal
and 3 the oppiosite of 2

I have two tables matched on user where the user might have records in table a and none in table b and another user that might have records in table b and none in table a.

But i need them both to be output as the query result. I can not get the query to return all users even if they do not have matching records on a or b.

help appreciated...
thanks
db

 

[jezjez]

Thanks Pat;

but can i specify a Union All - isn't that exactly my problem...? Where is this 'Union' setting?

cheers, Duncan

 

[jezjez]

Thanks Pat; i also found UNION in help and had a first crack at the UNION query; i got it to work but it just appended one set of data to the bottom of the next..
So next i'll look at the grouping by and/or removal of duplicates bit...

what i have is:

query 1
user recorda
fred 5
colin 2
tom 0
paul 3

query 2
user record b
fred 3
paul 0
harold 2
tom 4

and what result i need is:

user reca recb
fred 5 3
colin 2 0
tom 0 4
paul 3 0
harold 0 2

The whole getting null to 0 is driving me crazy too, but thats perhaps a seperate issue...

cheers
Duncan