many to many join

[ANDRE726]

I have posted this question in other forums, but no one seems to be able to solve this problem. I have 2 tables - one called tblCompanyA, the other called tblCompanyB. The layouts are the same: JobCode, Name, Title. JobCode is 5 positions, alphanumric. Company A and B share many of the same jobcode numbers. However, the are some jobcodes in A that are not in B and some jobcodes in B that are not in A. I would like to create a query that would display the results side by side.
Example:

Company A Company B
JobCode Name Title JobCode Name Title
0630 smith vp 0630 jones vp
0630 bailey vp 0630 johnson vp
0630 white vp
0631 moses avp
0632 brown fvp
0633 madison tr
0634 mason xyz 0634 perry zsw

How can I do this?
Thanks.

[hackmagic]

I have posted this question in other forums, but no one seems to be able to solve this problem. I have 2 tables - one called tblCompanyA, the other called tblCompanyB. The layouts are the same: JobCode, Name, Title. JobCode is 5 positions, alphanumric. Company A and B share many of the same jobcode numbers. However, the are some jobcodes in A that are not in B and some jobcodes in B that are not in A. I would like to create a query that would display the results side by side.
Example:

Company A Company B
JobCode Name Title JobCode Name Title
0630 smith vp 0630 jones vp
0630 bailey vp 0630 johnson vp
0630 white vp
0631 moses avp
0632 brown fvp
0633 madison tr
0634 mason xyz 0634 perry zsw

How can I do this?
Thanks.

i presume you mean that there are 2 separate companies and they may or mauy not have the same jobcodes, and it just so happens that in this case many of them are the same. i notice that jobcode 0634 has 2 different titles. are these 2 different jobs in 2 different companies with the coincidentally the same jobcode.

you could use a union query to solve this - query1 UNION query2 - where query1 selects from tblCompanyA and query2 from tblCompanyB. as long as you select the same number of fields in each query it will work.

any recordsets you create using this query wont be updatable though.


Pete

[ANDRE726]

i presume you mean that there are 2 separate companies and they may or mauy not have the same jobcodes, and it just so happens that in this case many of them are the same. i notice that jobcode 0634 has 2 different titles. are these 2 different jobs in 2 different companies with the coincidentally the same jobcode.

you could use a union query to solve this - query1 UNION query2 - where query1 selects from tblCompanyA and query2 from tblCompanyB. as long as you select the same number of fields in each query it will work.

any recordsets you create using this query wont be updatable though.


Pete

My mistake on the title - should be the same. When I try what you suggest, I get a cartesian product and now my file has many thousands of records. I've read in other forums that I should create a third "join" table to eliminate this problem - but I do not know how to make it work.

[hackmagic]

My mistake on the title - should be the same. When I try what you suggest, I get a cartesian product and now my file has many thousands of records. I've read in other forums that I should create a third "join" table to eliminate this problem - but I do not know how to make it work.

on the title - what should be the same? i am now confused as to what your actual problem is as there does not appear to be any data requiring a join table.

can you post your database structure? if in access a screenshot of the relationships diagram?

Pete

[ANDRE726]

on the title - what should be the same? i am now confused as to what your actual problem is as there does not appear to be any data requiring a join table.

can you post your database structure? if in access a screenshot of the relationships diagram?

Pete

I am trying to do a side by side comparison of jobs at the two companies. Many job codes will match but the number of people in those positions can vary. In other cases, the job appears in company a but not company b - or in company b but not a. The final result should be a list of all positions in both companies with all matches and all unmatched.

[hackmagic]

I am trying to do a side by side comparison of jobs at the two companies. Many job codes will match but the number of people in those positions can vary. In other cases, the job appears in company a but not company b - or in company b but not a. The final result should be a list of all positions in both companies with all matches and all unmatched.

starting to see what you mean. you could run 2 separate queries and process the results so they display with gaps between the rows where appropriate.

what you want is a query that returns its results into a set of fields in another query that is then grouped by jobcode. i'm not sure if this can be done but the only way i can think of is by a nested query, though i have never used one sorry. what you want might be:

SELECT tblCompanyA.Job code as JobcodeA, {SELECT tblCompanyB.Job code as JobcodeB FROM tblCompanyB} FROM tblCompanyA GROUP BY tblCompanyA.Job code

i don't think that will work but it seems thats the knind of think you want to do


Pete

[ANDRE726]

starting to see what you mean. you could run 2 separate queries and process the results so they display with gaps between the rows where appropriate.

what you want is a query that returns its results into a set of fields in another query that is then grouped by jobcode. i'm not sure if this can be done but the only way i can think of is by a nested query, though i have never used one sorry. what you want might be:

SELECT tblCompanyA.Job code as JobcodeA, {SELECT tblCompanyB.Job code as JobcodeB FROM tblCompanyB} FROM tblCompanyA GROUP BY tblCompanyA.Job code

i don't think that will work but it seems thats the knind of think you want to do


Pete

This is a vexing problem that no one seems able to solve. Thanks for your suggestion. I will give it a try.

[MMcCarthy]

Try this ...
[CODE=sql]
SELECT A.JobCode As JobCodeA, A.Name As NameA, A.Title As TitleA, B.JobCode As JobCodeB, B..Name As NameB, B.Title As TitleB
FROM tblCompanyA As A INNER JOIN tblCompanyB As B
ON A.JobCode = B.JobCode
UNION
SELECT A.JobCode As JobCodeA, A.Name As NameA, A.Title As TitleA, "" As JobCodeB, "" As NameB, "" As TitleB
FROM tblCompanyA As A LEFT JOIN tblCompanyB As B
ON A.JobCode = B.JobCode
WHERE B.JobCode Is Null
UNION
SELECT "" As JobCodeA, "" As NameA, "" As TitleA, B.JobCode As JobCodeB, B..Name As NameB, B.Title As TitleB
FROM tblCompanyA As A RIGHT JOIN tblCompanyB As B
ON A.JobCode = B.JobCode
WHERE A.JobCode Is Null
[/CODE]
This should give all jobs from both tables.