MS Access Joinin two queries condition

You have three table names in your FROM clause and no JOINs defined.  Therefore, all the rows in tbl1 are joined to each row in tbl2, and then all the resulting rows are joined to each row in tbl3.  The result of this is a Cartesian Product and it is NOT updateable.  If you have 100 records in tbl1 and 20 records in tbl2 and 5 records in tbl3, your resultset would contain 100 * 20 * 5 or 10,000 records and that probably won't make sense.

in QBE view, draw the correct join lines to connect the tables.  You will probably have to use LEFT JOIN's if the tables are optional.

PS - your table names are not long enough :)

Yes I am just trying to determine when to use the joins.

In Query1 I get 2,992 records
In Query2 I get 1,809 records
So the max amount of records should only be 4,801

So I have alter to this:

SELECT IIf([inttblNAsContacts].[Contact]=[cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1].[PrimaryContact],[PrimaryContact],[id]) AS Expr5, "Company" AS Expr1, "AltCompany" AS Expr2, "AltCompany" AS Expr3, "AltCompany" AS Expr4, cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.AltCompany
FROM cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1 Left Join inttblNAsContacts  ON inttblNAsContacts.Contact = cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.PrimaryContact,  cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2 Left Join inttblNAsContacts AS inttblNAsContacts_1 ON inttblNAsContacts_1.Contact = cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2.id;

Select allOpen in new window

But you can see it still needs work.

Please help and thanks

Well actually the Left Join does not work in Access

SELECT IIf([inttblNAsContacts].[Contact]=[cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1].[PrimaryContact],[PrimaryContact],[id]) AS Expr5, "Company" AS Expr1, "AltCompany" AS Expr2, "AltCompany" AS Expr3, "AltCompany" AS Expr4, cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.AltCompany
FROM cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1 Left Join inttblNAsContacts  ON inttblNAsContacts.Contact = cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.PrimaryContact,  cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2 Left Join inttblNAsContacts AS inttblNAsContacts_1 ON inttblNAsContacts_1.Contact = cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2.id;

Select allOpen in new window

I am sorry must be I typed it in wrong so I currectly have this:

INSERT INTO NOAH2_tblNAsLists ( Contact, GroupType, GroupCode, MasterCode, Description, Notes )
SELECT IIf([inttblNAsContacts].[Contact]=[cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1].[PrimaryContact],[PrimaryContact],[id]) AS Expr5, "Company" AS Expr1, "AltCompany" AS Expr2, "AltCompany" AS Expr3, "AltCompany" AS Expr4, cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.AltCompany
FROM cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1 LEFT JOIN inttblNAsContacts ON cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.PrimaryContact = inttblNAsContacts.Contact, cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2 LEFT JOIN inttblNAsContacts AS inttblNAsContacts_1 ON cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2.id = inttblNAsContacts_1.Contact;

Select allOpen in new window

But I am not getting the correct amount of records

Please help and thanks

SELECT IIf([inttblNAsContacts].[Contact]=[cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1].[PrimaryContact],[PrimaryContact],[id]) AS Expr5, "Company" AS Expr1, "AltCompany" AS Expr2, "AltCompany" AS Expr3, "AltCompany" AS Expr4, cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.AltCompany
FROM cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1 Left Join inttblNAsContacts  ON inttblNAsContacts.Contact = cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.PrimaryContact,  cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2 Left Join inttblNAsContacts AS inttblNAsContacts_1 ON inttblNAsContacts_1.Contact = cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2.id;

Select allOpen in new window

Thank you for helping me but I just combined Query1 into Query2:

SELECT DISTINCT USER_dbo_ams_contact.id, "Company" AS Expr1, "AltCompany" AS Expr2, "AltCompany" AS Expr3, "AltCompany" AS Expr4
FROM inttblNAsContacts AS inttblNAsContacts_1 INNER JOIN ((USER_dbo_ams_contact INNER JOIN (inttblNAsContacts INNER JOIN USER_dbo_ams_contact_custom ON inttblNAsContacts.Contact = USER_dbo_ams_contact_custom.contactID) ON USER_dbo_ams_contact.id = USER_dbo_ams_contact_custom.contactID) INNER JOIN USER_dbo_ams_group ON USER_dbo_ams_contact.orgID = USER_dbo_ams_group.id) ON inttblNAsContacts_1.Contact = USER_dbo_ams_group.primaryContact
WHERE (((USER_dbo_ams_contact.id) Not In (Select PrimaryContact From USER_dbo_ams_Group)) AND ((USER_dbo_ams_contact_custom.AltCompany) Is Not Null And (USER_dbo_ams_contact_custom.AltCompany)<>"None"))
ORDER BY USER_dbo_ams_contact.id;

Select allOpen in new window

Thanks for helping

SELECT IIf([inttblNAsContacts].[Contact]=[cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1].[PrimaryContact],[PrimaryContact],[id]) AS Expr5, "Company" AS Expr1, "AltCompany" AS Expr2, "AltCompany" AS Expr3, "AltCompany" AS Expr4, cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.AltCompany
FROM cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1 Left Join inttblNAsContacts  ON inttblNAsContacts.Contact = cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.PrimaryContact,  cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2 Left Join inttblNAsContacts AS inttblNAsContacts_1 ON inttblNAsContacts_1.Contact = cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2.id;

Select allOpen in new window

INSERT INTO NOAH2_tblNAsLists ( Contact, GroupType, GroupCode, MasterCode, Description, Notes )
SELECT IIf([inttblNAsContacts].[Contact]=[cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1].[PrimaryContact],[PrimaryContact],[id]) AS Expr5, "Company" AS Expr1, "AltCompany" AS Expr2, "AltCompany" AS Expr3, "AltCompany" AS Expr4, cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.AltCompany
FROM cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1 LEFT JOIN inttblNAsContacts ON cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany1.PrimaryContact = inttblNAsContacts.Contact, cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2 LEFT JOIN inttblNAsContacts AS inttblNAsContacts_1 ON cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2.id = inttblNAsContacts_1.Contact;

Select allOpen in new window

The joins to the two tables are on different columns and how are we to know that there is no overlap with rows in the base table joining to both lookup tables?

inttblNAsContacts sounds like it should be the determining factor regarding final row count.

I just created two queries to do what I am looking for:
Query1:

SELECT DISTINCT USER_dbo_ams_group.PrimaryContact, "Company" AS Expr1, "AltCompany" AS Expr2, "AltCompany" AS Expr3, "AltCompany" AS Expr4, IIf([USER_dbo_ams_group].[Name]=[USER_dbo_ams_contact_custom].[AltCompany],[USER_dbo_ams_group].[Name],[USER_dbo_ams_contact_custom].[AltCompany]) AS Expr5
FROM inttblNAsContacts AS inttblNAsContacts_1 INNER JOIN ((USER_dbo_ams_contact INNER JOIN (inttblNAsContacts INNER JOIN USER_dbo_ams_contact_custom ON inttblNAsContacts.Contact = USER_dbo_ams_contact_custom.contactID) ON USER_dbo_ams_contact.id = USER_dbo_ams_contact_custom.contactID) INNER JOIN USER_dbo_ams_group ON USER_dbo_ams_contact.orgID = USER_dbo_ams_group.id) ON inttblNAsContacts_1.Contact = USER_dbo_ams_group.primaryContact
WHERE (((IIf([USER_dbo_ams_group].[Name]=[USER_dbo_ams_contact_custom].[AltCompany],[USER_dbo_ams_group].[Name],[USER_dbo_ams_contact_custom].[AltCompany])) Is Not Null And (IIf([USER_dbo_ams_group].[Name]=[USER_dbo_ams_contact_custom].[AltCompany],[USER_dbo_ams_group].[Name],[USER_dbo_ams_contact_custom].[AltCompany]))<>"None"))
ORDER BY USER_dbo_ams_group.PrimaryContact;

Select allOpen in new window

Query2:

SELECT DISTINCT USER_dbo_ams_contact.id, "Company" AS Expr1, "AltCompany" AS Expr2, "AltCompany" AS Expr3, "AltCompany" AS Expr4, IIf([USER_dbo_ams_group].[Name]=[USER_dbo_ams_contact_custom].[AltCompany],[USER_dbo_ams_group].[Name],[USER_dbo_ams_contact_custom].[AltCompany]) AS Expr5
FROM inttblNAsContacts AS inttblNAsContacts_1 INNER JOIN ((USER_dbo_ams_contact INNER JOIN (inttblNAsContacts INNER JOIN USER_dbo_ams_contact_custom ON inttblNAsContacts.Contact = USER_dbo_ams_contact_custom.contactID) ON USER_dbo_ams_contact.id = USER_dbo_ams_contact_custom.contactID) INNER JOIN USER_dbo_ams_group ON USER_dbo_ams_contact.orgID = USER_dbo_ams_group.id) ON inttblNAsContacts_1.Contact = USER_dbo_ams_group.primaryContact
WHERE (((USER_dbo_ams_contact.id) Not In (Select PrimaryContact From USER_dbo_ams_Group)) AND ((IIf([USER_dbo_ams_group].[Name]=[USER_dbo_ams_contact_custom].[AltCompany],[USER_dbo_ams_group].[Name],[USER_dbo_ams_contact_custom].[AltCompany])) Is Not Null And (IIf([USER_dbo_ams_group].[Name]=[USER_dbo_ams_contact_custom].[AltCompany],[USER_dbo_ams_group].[Name],[USER_dbo_ams_contact_custom].[AltCompany]))<>"None"))
ORDER BY USER_dbo_ams_contact.id;

Select allOpen in new window

And then I just ran them separately.

But if you see something or think it should be down differently please let me know?

Thanks for any help.

Great thanks