Tricky query

[BobJones]

I have an insurance policy database keeping track of insurance policy details for household and commercial shops and businesses.

Tables I have are tblPolicyHousehold, tblPolicyCommercialS (Shop), tblPolicyCommercialB (Business).
They each contain the following fields (the other fields they contain aren't relevant to this)... SumInsured, extCover1Desc, extCover1SumInsured, extCover2Desc, extCover2SumInsured, extCover2Desc, extCover2SumInsured

I want write a query to list all the policies with the building sum insured. Easy enough... Heres the catch.

In some of the extended cover fields it will say in the description...
"Building of 123 AnyStreet" so I want to get the value of that extended cover item where the description for it contains building...

So I want to get extCover1SumInsured where extCover1Desc LIKE "*Building*" and adding that value to the SumInsured field...

Any ideas?

 

[ColinEssex]

You should only have one table for the policies (not 3) and that table should be linked to the "customer" table. You should have a fields in the policies table to indicate what type it is.

You should also not have the fields for the extended insurance. Again this can be done with one field in the policies table.

You do not say what the other fields are, I'm hoping they're not repeating customer information.

Look up "normalisation", its best to get the structure and relationships right, then the rest is much easier.

Col

 

[BobJones]

Ok... Here are the table structures.

As each of the policies by nature have different things that they cover for, this is the reason I have made policies based on these... Otherwise the tblPolicy would be a huge table with potentionally hundreds of empty fields.

tblPolicy
CustCode
SalesAccount
PolicyType (ie. Household, Business, Shop)
PolicyNumber (Primary key)
Insurer
RenewalDate
Premium
ReviewalDate
Narrative

tblPolicyHousehold
PolicyNumber
BuildingsSumInsured
ContentsSumInsured
ExtCover1Desc
ExtCover1SumInsured
...Right the way to ExtCover50SumInsured

tblPolicyCommercialS
Well this contains things like FoodContentsSumInsured, AlcoholSumInsured... etc etc ExtCover20SumInsured

tblPolicyCommercialB
Contains MachinerySumInsured... etc etc
ExtCover10SumInsured


Does this explain things a bit better or do you stil suggest one table?