MS Access Joinin two queries condition

Hi Experts,

I have 2 queries that I am combining into one:
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, cnv0113_USER_dbo_ams_contact_custom_NOAH2_tblNAsListsAltCompany2, inttblNAsContacts;

Open in new window


So what I want is if the primarycontact does exist then I want to use the Primarycontact(Query1). Else use
ID(Query2)
Also the samething with AltCompany (either use query1 or query2)

It looks like I am not doing this correctly and am getting way too many records.

In the other 2 queries I don't get that many so I am thinking that the IIF statement is incorrect?

Please help and thanks
Amour22015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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 :)
Amour22015Author Commented:
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;

Open in new window


But you can see it still needs work.

Please help and thanks
Amour22015Author Commented:
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;

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Amour22015Author Commented:
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;

Open 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;

Open in new window

Amour22015Author Commented:
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;

Open 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;

Open 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;

Open in new window

PatHartmanCommented:
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.
Amour22015Author Commented:
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;

Open 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;

Open 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.
PatHartmanCommented:
Now that you have included the join type, you should get more understandable results

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
Great thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.