finding something thats not there?

[Seb]

Hi guys

I have a DB for insurance permits.

tblSubtractors
ID
NAME
ADDRESS
PHONE

tblPolicy
ID
SubbyID
POLICYID
Insurer
PolicyNumber
ExpiryDate

tblPolicyTypes
ID
Name


Most subcontractors will have a policy number for each type of policy in tblPolicy. But is there a way I can run a query to show each subby that doesnt have one of the policies? As an example, the query would show me that ABCcontractors doesnt have Marine Transit insurance. XYZContractors doesnt have Public Liability, etc

I know how to show all the policies that have expired, but I'm looking to identify which ones dont have a policy at all

 

[Guus2005]

Use a left or right join and test on a null value.
That's all i can tell since i dont know what the tables are.

 

[Seb]

Hi GUUS2005

This is my SQL

SELECT tblSubcontractors.SubbyName, tblPolicyTypes.PolicyType
FROM (tblSubcontractors LEFT JOIN tblPolicy ON tblSubcontractors.ID = tblPolicy.SubbyID) LEFT JOIN tblPolicyTypes ON tblPolicy.InsuranceType = tblPolicyTypes.ID ;

Obviously, this just shows each Subcontractor, then each Policy they have....I need it to show which ones they dont have???

Does this make sense?

 

[neileg]

You have to think out of the box on this one. You need to be able to produce a dataset that contains every combination of subcontractor and policy type and then match this to your table of existing policies.

The way to produce this dataset is to create a Cartesian Product, which is something we usually try very hard to avoid. It's very easy, though! Create a new query and add in tblSubtractors and tblPolicyTypes. Do not create a join between these tables! If Access puts a join in, take it out. If you run this query you will find you have a record for every subbie and every policy type. I think you will be able to do the rest of this armed with this query

 

[Guus2005]

You have to show the relation between the tables before i can make an educated guess.
Like: a subcontrator can have one or more policies. If the tblPolicy is the one where Subcontractors and their policies are stored then it could be something like:

SELECT tblSubcontractors.ID, tblSubcontractors.NAME, tblPolicyTypes_1.NAME
FROM tblPolicyTypes AS tblPolicyTypes_1, (tblPolicy INNER JOIN tblSubcontractors ON tblPolicy.SubbyID = tblSubcontractors.ID) INNER JOIN tblPolicyTypes ON tblPolicy.POLICYID = tblPolicyTypes.ID
WHERE (((tblSubcontractors.ID)="1") AND ((tblPolicyTypes_1.ID) Not In (select policyid from tblPolicy where subbyid = "1")));

for SubContractor "1"

 

[Seb]

Hi Guus
Sorry for not getting back to you on this one sooner, been on leave.
well, this is how it works:

TblSubcontractors holds in the subcontractor information (name, address, etc)
TblPolicies holds the policy details. (SubcontractorID, PolicyID, expiry, policyissuer, etc)
TblPolicyTypes is a table that holds the different type of policies you can get.

I think I need the query to run through tblSubcontractors, and for each subcontractor, see which policies they do have in tblPolicies, and let me know which ones are missing (compared to the tblPolicies table)

Eg

Subcontractor1 has Motor Vehicle insurance & Marine Transit Insurance. In TBLPolicies, there are 3 different policies. Those 2 and also public liability.
How would I run a query to say Subcontractor1 doesnt have public liability?

again, thanks for your help....

 

[neileg]

Don't you like my advice? It works, you know.

 

[Seb]

Neileg. of course I like your advise!!!....I'm just used to scrolling down to the bottom of the page to view the posts, and didnt see yours.

I'll give this a try during the weekend, and see how I go

Thanks