Joining Queries

[funderburgh]

I am having trouble joining two queries, I want to include all records from each query.

Query 'N" has three records, query "S' has four for the matching ID field. I am hoping to have the result produce seven records.

When I use the following:

FROM qryN INNER JOIN qryS ON N-ID = S-ID
(generated in the Query By Example Grid)

For each N I get 3 S's for 12 records.

I get the same result for LEFT and RIGHT JOINS with records in different order.

Thanks for any suggestions and thanks to Bob Larson for getting me to this point.

 

[pbaldy]

It sounds like you want a UNION query, not a join. Try:

SELECT Field1, Field2
FROM qryN
UNION ALL
SELECT Field1, Field2
FROM qryS

 

[namliam]

Each ID equal in N and S get joined... lets say these are your tables
ID / Value
1 7
1 8
1 9

ID / Value
1 a
1 b
1 c
1 d

Then each 1 from the first table is joined with each 1 in the second
1 7 1 a
1 7 1 b
1 7 1 c
1 7 1 d
1 8 1 a
1 8 1 b
1 8 1 c
1 8 1 d
1 9 1 a
1 9 1 b
1 9 1 c
1 9 1 d

This is natural join behaviour, I think I agree wtih Paul, you seem to be looking for a union rather than a join.

Good luck