SQL For Complex Inner Join

[compooper]

Hi folks,



Can you help me write a complex SQL/VBA code to do the following:

background:
There are multiple companies, providing multiple services.
I have taken the time to make a table that essentially has all service codes, descriptions for each company

The table, lets call it tblMapping, looks something like this
mappingID Autonumber
CompanyID Number
Svc_Code Text
Svc_Description Text
AccountID Number
SvcID Number
SvcPrice Number

AccountID is the account a service belongs to. For example, hair cuts belongs to Salon Account

SvcID is a generic name I came up with for common services between companies
For example, CompanyA might have hair cuts and Company B might call it Hair-Cuts
So SvcID says Hair-Cuts just to keep things simple

SvcPrice is the quoted price by the company. So if anything is less/more, something is fishy and I should know.

What I need to do:
So the whole point of the tblmapping, is so I can keep things related.

I want to develop a query that looks a really big table of invoices (tblInvoices) from all the companies

It will use CompanyID (selected by a user) to filter through the data
It will then take each service code & description and match it to the mapping
Compare the prices and spit out any prices that are not equal to the mapping

The difficulty I am having:

Some companies have svc_codes that are blank & just a svc_description
Some companies only svc_codes
Some comapnies have both svc_codes and svc_description, but sometimes like to add a few words to their description like

1198 Hair-cutes (This client had the softest hair)

Some companies might add a service out of the blue and it won't be in my mapping
And some companies are just awesome.

I've tried writing numerous SQL statements with inner joins, but honestly its just not working.
Any ideas?

[compooper]

This is sort of what I am trying to do:

SELECT tblInvoices.CompanyID, tblInvoices.InvTypeID, tblInvoices.InvPeriod, tblInvoices.App_policynum, tblInvoices.App_lname, tblInvoices.App_fname, tblInvoices.Svc_code, tblInvoices.Svc_description, tblmapping.Svc_price
FROM tblmapping INNER JOIN tblInvoices ON IIF(CompanyID=6, tblInvoices.Svc_code=tblmapping.Svc_code and tblInvoices.Svc_description=tblmapping.Svc_descrip tion, false) OR IIF(CompanyID=4, tblInvoices.Svc_code Is null AND tblInvoices.Svc_description=Svc_description, false) OR IIF(CompanyID=1 OR CompanyID=2 OR CompanyID=3 OR CompanyID=5 OR CompanyID=7, tblInvoices.Svc_code=tblmapping.Svc_code, false)
ORDER BY tblInvoices.ID;

But clearly it doesn't work.
How do you run an IIF statement, and if its false go to the next IIF statement in SQL?