summing trouble (1 Viewer)

Ok, from looking at my original excel data sheets, the net premium is dependent on the discounts which I have chosen to not include due to its variability and complexity.

Since you have dependent values, it sounds like you really do need to incorporate the discounts into your database. The questions would be where and how. Are discounts related to the vessel? the policy? the customer? Are there different types of discounts? What are the rules for applying a discount? Can a vessel/policy/customer receive multiple discounts?
 
Unfortunately I do not really know all the answers to those questions. I am not very involved with the underwriting side of these insurance policies. I just know that there are many different types of discounts and every single policy has them applied differently. I believe the discounts are agreed upon factors between our company and the customer.

This is why I am choosing to simply extract the calculated values with their applied discounts and manually enter them. To incorporate the discounts into the database would add at least 12 different kind of discounts that are possible with no real parameters as to how they are selected.
 
As a database developer (which is what you are now--whether you like it or not), this is where you would get with the people who know and try to understand their requirements and then apply those requirements to the database within the rules of normalization. If the ultimate goal is to enter data directly into the database rather than the spreadsheets, then having the structure to handle the discounts is necessary, and I strongly recommend getting that table structure set up now.

BTW, now that you are the "database developer", be prepared for your users to ask you to do "other things" with the database.
 
I completely understand your points and perspective in regards to the database and understanding their requirements.

However, most of the office is located in London, whereas I am in New York so sitting down and discussing the intricacies of the discounts is very difficult to do as they are usually pretty busy and hard to reach.

Also, very few other people have access to access, no pun intended. I think our ultimate goal is for the underwriters to provide me with their completed data excel sheet, which i will then "cleanup" to enter into my "sheet1" to be imported into the database. They would never be entering data into the database itself, although that would be ideal and eliminate the need for me to do each step for them.

With these factors in mind how do you suppose I move forward and reach our intended goal situation.
 
OK, then, it sounds like the net value(s) would go in the same table where you put the gross rate (tblPolicyVesselLocationProducts). You would then have to modify the 9th query accordingly as indicated below

INSERT INTO tblPolicyVesselLocationProducts ( fkPolicyVesselLocationID, fkProductID, longDeductible, currInsuredAMT, currROW, currSL, LineSize, field1, field2... )
SELECT DISTINCT tblPolicyVesselLocations.pkPolicyVesselLocationID, tblProducts.pkProductID, Sheet1.deductible, Sheet1.InsuredAmt, Sheet1.longROW, Sheet1.SL, Sheet1.LineSiZe, field1, field2...
FROM (((((((Sheet1 INNER JOIN tblVesselTypes ON Sheet1.txttype=tblVesselTypes.txtVesselTypeName) INNER JOIN tblLocations ON Sheet1.txtLocation=tblLocations.txtLocationName) INNER JOIN tblVessels ON (Sheet1.txtVesselName=tblVessels.txtVesselName) AND (tblVesselTypes.pkVesselTypeID=tblVessels.fkVesselTypeID)) INNER JOIN tblOwners ON Sheet1.txtOwner=tblOwners.txtOwnerName) INNER JOIN tblPolicy ON (Sheet1.txtpolicyNo=tblPolicy.txtPolicyNumber) AND (tblOwners.pkOwnerID=tblPolicy.fkOwnerID)) INNER JOIN tblProducts ON Sheet1.txtproduct=tblProducts.txtProductName) INNER JOIN tblVesselLocations ON (tblLocations.pkLocationID=tblVesselLocations.fkLocationID) AND (tblVessels.pkVesselID=tblVesselLocations.fkVesselID)) INNER JOIN tblPolicyVesselLocations ON (tblPolicy.pkPolicyID=tblPolicyVesselLocations.fkPolicyID) AND (tblVesselLocations.pkVesselLocationID=tblPolicyVesselLocations.fkVesselLocationID);
 
I am sorry but I am not really sure how to interpret that stuff.

I understand the INSERT INTO part and added the necessary field1, field2, etc.

In regards to the rest, The “SELECT DISTINCT…” stuff, is that an SQL statement I should be typing in, and if so where.
 
In regards to the rest, The “SELECT DISTINCT…” stuff, is that an SQL statement I should be typing in, and if so where.

The SELECT DISTINCT represents from where the append query is going to get the data it is going to append; in other words Sheet1 (as well as related tables that hold the key values ie. primary/foreign keys). So you have to add the corresponding fields of sheet1 to the SELECT DISTINCT clause so that the append query will know what to append.

You can switch from the design grid view to SQL view to input the additional table fields as well as the additional field names of sheet1

In the SQL text, the green text below specifies the destination table and the corresponding fields that will receive the data. The purple text shows the source fields. The light blue text represents the source table(s) which you do not have to change. This shows all of the joins between the related tables, so that the correct data relative to the key fields are appended.



INSERT INTO tblPolicyVesselLocationProducts ( fkPolicyVesselLocationID, fkProductID, longDeductible, currInsuredAMT, currROW, currSL, LineSize, field1, field2... )
SELECT DISTINCT tblPolicyVesselLocations.pkPolicyVesselLocationID, tblProducts.pkProductID, Sheet1.deductible, Sheet1.InsuredAmt, Sheet1.longROW, Sheet1.SL, Sheet1.LineSiZe, field1, field2...
FROM (((((((Sheet1 INNER JOIN tblVesselTypes ON Sheet1.txttype=tblVesselTypes.txtVesselTypeName) INNER JOIN tblLocations ON Sheet1.txtLocation=tblLocations.txtLocationName) INNER JOIN tblVessels ON (Sheet1.txtVesselName=tblVessels.txtVesselName) AND (tblVesselTypes.pkVesselTypeID=tblVessels.fkVessel TypeID)) INNER JOIN tblOwners ON Sheet1.txtOwner=tblOwners.txtOwnerName) INNER JOIN tblPolicy ON (Sheet1.txtpolicyNo=tblPolicy.txtPolicyNumber) AND (tblOwners.pkOwnerID=tblPolicy.fkOwnerID)) INNER JOIN tblProducts ON Sheet1.txtproduct=tblProducts.txtProductName) INNER JOIN tblVesselLocations ON (tblLocations.pkLocationID=tblVesselLocations.fkLo cationID) AND (tblVessels.pkVesselID=tblVesselLocations.fkVessel ID)) INNER JOIN tblPolicyVesselLocations ON (tblPolicy.pkPolicyID=tblPolicyVesselLocations.fkP olicyID) AND (tblVesselLocations.pkVesselLocationID=tblPolicyVe sselLocations.fkVesselLocationID);
 
I am trying to use this as my sql statement;

INSERT INTO tblPolicyVesselLocationProducts ( fkPolicyVesselLocationID, fkProductID, longDeductible, currInsuredAMT, currROW, currSL, LineSize, dblGrossRate, dblGrossPremium, dblNetPremium )
SELECT DISTINCT tblPolicyVesselLocations.pkPolicyVesselLocationID, tblProducts.pkProductID, Sheet1.deductible, Sheet1.InsuredAmt, Sheet1.longROW, Sheet1.SL, Sheet1.LineSiZe, Sheet1.GrossRATE, sheet1.GrossPremium, sheet1.NetPremium
FROM (((((((Sheet1 INNER JOIN tblVesselTypes ON Sheet1.txttype=tblVesselTypes.txtVesselTypeName) INNER JOIN tblLocations ON Sheet1.txtLocation=tblLocations.txtLocationName) INNER JOIN tblVessels ON (Sheet1.txtVesselName=tblVessels.txtVesselName) AND (tblVesselTypes.pkVesselTypeID=tblVessels.fkVessel TypeID)) INNER JOIN tblOwners ON Sheet1.txtOwner=tblOwners.txtOwnerName) INNER JOIN tblPolicy ON (Sheet1.txtpolicyNo=tblPolicy.txtPolicyNumber) AND (tblOwners.pkOwnerID=tblPolicy.fkOwnerID)) INNER JOIN tblProducts ON Sheet1.txtproduct=tblProducts.txtProductName) INNER JOIN tblVesselLocations ON (tblLocations.pkLocationID=tblVesselLocations.fkLo cationID) AND (tblVessels.pkVesselID=tblVesselLocations.fkVessel ID)) INNER JOIN tblPolicyVesselLocations ON (tblPolicy.pkPolicyID=tblPolicyVesselLocations.fkP olicyID) AND (tblVesselLocations.pkVesselLocationID=tblPolicyVe sselLocations.fkVesselLocationID);

I am receiving " syntax error (missing operator) in query expression ". "

I think this may mean I have a space or something where there should not be but am not sure where the error is. Was wondering if you could identify this error.

Thanks
 

Attachments

Something really got messed up with query 9. Instead of trying to figure out what happened. I went back to the last DB I posted, edited it for the additional fields, imported your new sheet1 and then ran the 9 queries. That DB is attached. You can compare your query to the one in the attached DB for differences. I also, adjusted the field datatypes in the primary table to match those of sheet1. You had different datatypes which might cause some problems but it did not have anything to do with the syntax error you received. The syntax error could be as simple as a missing parenthesis.
 

Attachments

Hi jzwp,

Everything was working great with the database and the queries I had been making and running were meeting our needs. BUT, as you said, I have now been asked to “do other things” with the database.

Instead of Inception Year, now we want to have Inception Date and Expiry Date. I made these changes without a problem, added the necessary columns to “sheet1”, added those fields to the tblPolicy, and adjusted qry5AppendPolicies.

The next addition that was asked was to add another type of deductible which is Collision Liabilities, which I am calling CollisionLiab. I added this to the tblPolicyVesselLocationProducts, as well as made the adjustments to the qry9AppendPolicyVesselLocationProductDetail.

I also made the change to include the collision liability values to the Exposure equation.

However, after making these changes it seems to have something wrong after qry5. When I run qry6AppendVessels there are way more rows then there should be. Not sure if this is a Cartesian Product issue or what.

If you have a chance to take a look at the append queries to maybe spot the mistake that would be great. Thanks so much for your continued assistance.
 

Attachments

...add another type of deductible which is Collision Liabilities

If you have more than 1 type of deductible, that describes a one-to-many relationship which will impact your table structure which in turns alters several of the queries.

Relative to your other issue, in the database I posted yesterday, I ran the append queries as a test. If you ran them again after making your changes without first deleting all of the records I had appended then you now have duplicate records. You will have to delete all records in all tables (working backwards from tblPolicyVesselLocationProducts). Then run the compact and repair utility and then rerun the append queries.
 
sorry, it is not really a type of deductible. it is simply just another metric that is a possibility for policies.

does it need to be individually joined to something in particular?

also, i did not know that i needed to delete the records that were already in there before running the append queries.

i deleted all the records in the reverse order then began re appending them in order. everything ran properly until i got to qry8AppendPolicyVesselLocations. I am not sure if this is due to a relationship that i am missing with the new CollisionLiab field or something else.
 
...that i am missing with the new CollisionLiab field or something else.

The new field should not come into play until query 9 since you added the field to tblPolicyVesselLocationProducts, correct. Did you get an error or just no records appended?

also, i did not know that i needed to delete the records that were already in there before running the append queries.

Sorry, I was not very clear. In post #27, I mentioned about creating duplicates if you do several imports and run the queries for each import data set. The same would apply if you run the append queries multiple times on the same data set. There are ways to restrict the creation of duplicates, but that would make the queries even more complex and I wanted to avoid overwhelming you.
 
I definitely appreciate you keeping the queries less complex and less overwhelming.

I am not receiving an error, just no records being appended.
 
Regarding your statement about the collision liability

it is not really a type of deductible. it is simply just another metric that is a possibility for policies.

Is the collision liability per vessel (PolicyVesselLocationProducts) or over the entire policy? If it applies to the policy then it needs to go in tblPolicy


In term of the query not appending any records, the best that I could recommend is to compare the SQL text of a version of the query that worked to you query that is failing and see what changed.
 

Users who are viewing this thread

Back
Top Bottom