i have today an interesting little project to undertake. I am creating the source material for a new report that tests for insurance compliance by supplier. the reports out of the system are 1. Expenditure (incl Acct Nbr, Descriprion,, & Spend, and 2. Insurance Qualifications (which lists which suppliers have lodged current insurance certificates with us and what they are. Also Expiry Date. Both tables have Account Number.
My Spend report lists suppliers by Name and Number in descending order of expenditure. I have just shown the Account Number and Total columns below. (first two cols)
My Insurance report (screenshot two) lists suppliers by Account Number and Insurance Type, with multiple entries per supplier where that supplier has multiple policies. Against each of these entries, the policy expiry date is listed. You can see that both suppliers have more than one insurance type.
Where i need some help is how to determine whether we have been supplied certificates for each of the Insurance Types (Public Liability, Workers Compensation, Professional Indemnity) and that the end date of the insurances is greater than the Month End date.
The result might look like what i have below.
PS I am also working on getting the different variations of each insurance type ironed out so that there is only one version of each type that the suppliers can select from when lodging their certificates.
My Spend report lists suppliers by Name and Number in descending order of expenditure. I have just shown the Account Number and Total columns below. (first two cols)
My Insurance report (screenshot two) lists suppliers by Account Number and Insurance Type, with multiple entries per supplier where that supplier has multiple policies. Against each of these entries, the policy expiry date is listed. You can see that both suppliers have more than one insurance type.
Where i need some help is how to determine whether we have been supplied certificates for each of the Insurance Types (Public Liability, Workers Compensation, Professional Indemnity) and that the end date of the insurances is greater than the Month End date.
The result might look like what i have below.
PS I am also working on getting the different variations of each insurance type ironed out so that there is only one version of each type that the suppliers can select from when lodging their certificates.
