summing trouble (2 Viewers)

[aml5171]

Hi all,

I have been creating a database for my company to capture "open exposure" for oil rigs we insure across our entire portfolio. I have created the tables and set up the database (attached below), but our goal is to be able to compare and analyze different data points across the portfolio (i.e. total exposure, location, coverage type, etc.)

Now that I have the database set up and have began to fill it with our data my goal is to create queries or forms that my co-workers can use to extract various aggregations of information. I have been able to figure out how to set the criteria that they type in (ie. vessel name, location, etc.) but when I run my query, (the one in particular is to select a vessel by name "Richmond") and then display the sum of the exposure for that vessel, "Richmond". Now I know that there are only 6 instances of Richmond in my data so I assumed it would "sum" those 6 exposures but it seems to be adding up every single vessel's exposure.

Ultimately, what we would like to be able to do is have the users select/type in different criteria and then run a query on said criteria such as "Total Exposure” for all "Vessels" in "Africa".

I am a novice access user but have been finding enough information from various forums and tutorials, especially this one, and think I just need a push in the right direction to get to where I need to be.

Thanks in advance for any advice and help as it is much appreciated.

 

[jzwp22]

Your vessel table in the query is not joined to the other tables in the query, this will cause a Cartesian Product to be created and then summed.

(FROM Vessel, Products INNER JOIN [Policy Details] ON Products.ProductID = [Policy Details].ProductID)

Looking at your tables I have some concerns about your structure. The vessel table should have only the information pertinent to the vessel (1 record per vessel).

It appears that you are tracking a vessel from one location to another. This suggests that a vessel can have many locations (over time) which describes a one-to-many relationship. Am I close?

Could you explain what your database is designed to do and what business process you are trying to model?

Also, I noticed that you are using lookups (list/combo boxes) at the table level. Although Access has this capability, it is generally not recommended. See this site for the problems it can cause.

 

[aml5171]

jzwp, thank you very much for your time and help.

I am not exactly sure what this means: (FROM Vessel, Products INNER JOIN [Policy Details] ON Products.ProductID = [Policy Details].ProductID)

Is this something I should be typing in manually somewhere? or is this just the breakdown of what i should do?

In regards to the database, what it represents is the data that my company enters into a contractors database in an excel sheet to record all open contracts. the reason on the vessel table there are so many repeats is because we often have multiple policies per vessel in the same location just with different product types, (PD, IV, etc. in my examples)

Should I create an additional table such as vessel details to contain the vessels location and unit type?

I am not trying to track a vessel from location to location because the vessels are large sea rigs and generally are in the same location for many many years. What my “superiors” in the office would like me to create is a database that they can use to run a multitude of queries to compare and analyze all the data across the multiple policies.

What I mean by this is that I would like to be able to have this database, (eventually with all our data, right now just has data from one individual “owner”), on the shared drive and a co worker can pull it up and run a search for example of sum of all exposures of all vessels in Africa.

In regards to the lookups that I am using, I read that website and see the issues involved. What are my other options if I do not use lookups? Do I just manually enter that data and then create relationships?

my apologies for how basic most of these issues probably are but i am new to access and would really like to learn how to create a useful database for my employer.

again thanks in advance for both your time and help. it is all much appreciated!

 

[jzwp22]

I am not exactly sure what this means: (FROM Vessel, Products INNER JOIN [Policy Details] ON Products.ProductID = [Policy Details].ProductID)

Sorry, this is a portion of the query you had in your database; it is the SQL text (SQL View window) rather than the design grid view. The query will be of little value once your table structure is cleaned up.

The table structure is the foundation of any successful relational database application and of course, there are some rules to follow when you are creating that table structure: rules of normalization. So it is worth the effort to analyze your data and uncover the relationships so that you can build a good structure.

With your vessel table, it should only have fields that describe the vessel and only 1 record per vessel. I also recommend that you have an autonumber primary key field in every table. This allows Access to assign a unique identifier to each record in the table. You will use this key field to join this table to tables that hold related data. From looking at your current vessel table, it looks like you basically have an ID field, a name field and a type field that describe the vessel. The location, since it varies should not be in this table. So restructuring the table, it should look like this:


tblVessels
-pkVesselID primary key, autonumber
-txtVesselName
-fkVesselTypeID foreign key to tblVesselTypes

Just as a note, I use prefixes that tell me what type of field it is, pk denotes primary key, txt denotes a text field, fk denotes a foreign key (related to another table) and is a long number integer field. I also use dte for date fields, lng for long number integer fields (other than a foreign key field), log for logical field (yes/no); sp and dbl for single and double precision number fields respectively.

In your current vessel table, you have the vessel type field and you repeat the same 2 names over and over again. It would be better to put the unique names in a table and then join that table to the vessel table. This will cut down on typographical errors since you will only have the name once in 1 location. I assume that a vessel can only be of 1 particular type.

tblVesselTypes
-pkVesselTypeID primary key, autonumber
-txtVesselTypeName


If you can have many vessels at a location, it would probably be best to have a table that holds the locations (1 record for each location)

tblLocations
-pkLocationID primary key, autonumber
-txtLocationName


Now, if there is a chance that a vessel can be moved from one location to another then that would require a different structure than if a vessel were to remain at the same location for the duration of its life. So I will present the two structures and you will have to choose based on your application.

For vessels that can be moved from one location to another:

tblVesselLocations
-pkVesselLocationID primary key, autonumber
-fkVesselID foreign key to tblVessels
-fkLocationID foreign key to tblLocations
-dteEffective (effective date i.e. the date the vessel came to be at the location)

...If the vessel stays at one location for its lifetime.

tblVessels
-pkVesselID primary key, autonumber
-txtVesselName
-fkVesselTypeID foreign key to tblVesselTypes
-fkLocationID foreign key to tblLocations

I'm a little unclear about the products/product types/policies, but it sounds like each vessel/location combination can have many product types and each has a corresponding policy? Also, I'm not sure where the owners come in. Do the owners "own" the locations? You will have to provide a little more detail on this.

In regards to the lookups that I am using, I read that website and see the issues involved. What are my other options if I do not use lookups? Do I just manually enter that data and then create relationships?

All of the tables and the relationships should be created before data is entered. You would use forms with combo boxes for data entry.

 

[aml5171]

jzwp, thanks so much for this help and guidance.

I am going to make the changes you have mentioned as well as reaching normalization and simplification of the tables and their structures.

As far as the products, types, and policies go let me try and explain this better.

An owner is the company that owns all of the vessels under a given policy number. A single policy number contains multiple vessels all owned by that owner. Under that policy there are different “products” that we offer which are different insurance products. PD for example is property damage insurance, where IV is increased value insurance. So for any given policy, there can be multiple vessels, each vessel can then have a number of different “products” or types of coverage, but a single vessel is only one type of rig, for example a “semi-sub”.

Ultimately we would like to perform searches such as the total “exposure” of all “semi-sub” vessels in Africa. These searches will be constantly changing as they depend on what we are trying to capture at the time.

Hope this gave you a clearer idea of my finished product, and thanks again for your consideration

 

[jzwp22]

A single policy number contains multiple vessels all owned by that owner.

The above describes a one(policy number)-to-many(vessels) relationship.

tblPolicy
-pkPolicyID primary key, autonumber
-PolicyNumber

tblPolicyVessels
-pkPolicyVesselID primary key, autonumber
-fkPolicyID foreign key to tblPolicy
-fkVesselID or fkVesselLocationID (depending on which table structure option from my previous post with which you decide to go)

there can be multiple vessels, each vessel can then have a number of different “products” or types of coverage

Since a vessel can have many products (one-to-many) and a product may apply to many vessels (another one-to-many), you have a many-to-many relationship between products and vessels which requires a junction table.

tblProducts
-pkProductID primary key, autonumber
-txtProductName

tblVesselProducts (the junction table)
-pkVesselProductID primary key, autonumber
-fkVesselID or fkVesselLocationID (depending on which table structure option from my previous post with which you decide to go)
-fkProductID foreign key to tblProducts


Can an owner have many policy numbers?
Can a policy have many owners?

Out of curiosity, the policy table in the database you posted shows the same policy having multiple owners and multiple vessels but only 1 vessel per owner. I assume that is not what you intended.

 

[aml5171]

thanks again that stuff looks great, i am going to start implementing it shortly.

an owner can have multiple policy numbers, as they can own many vessels in different parts of the world which are completely different policies we can write.

a single policy number will only have a single owner. And so will be a 1 to 1 relationship, correct?

you are absolutely right with the multiple owners. i changed the "owner" table to have just a single entry right now which represents pkOwnerID "1", OwnerName "Atwood Oceanics" and then Year. i assumed i needed an ownerid for each vessel and product but that made no sense since they are all owned by the same owner.

 

[jzwp22]

i assumed i needed an ownerid for each vessel and product but that made no sense since they are all owned by the same owner.

Many products are related to the vessel. The owner owns the policy (not the vessels--or at least not directly). The vessels are covered under the policy.

tblOwners
-pkOwnerID primary key, autonumber
-txtOwnerName

(not sure what the year field is for)

Since an owner can have many policies; tblPolicy has to change as follows:

tblPolicy
-pkPolicyID primary key, autonumber
-PolicyNumber
-fkOwnerID foreign key to tblOwners

Out of curiosity, which option were you going to go with relative to the vessel and locations as I describe below?:

For vessels that can be moved from one location to another:

tblVesselLocations
-pkVesselLocationID primary key, autonumber
-fkVesselID foreign key to tblVessels
-fkLocationID foreign key to tblLocations
-dteEffective (effective date i.e. the date the vessel came to be at the location)

...If the vessel stays at one location for its lifetime.

tblVessels
-pkVesselID primary key, autonumber
-txtVesselName
-fkVesselTypeID foreign key to tblVesselTypes
-fkLocationID foreign key to tblLocations

 

[aml5171]

i am making it as if the vessels do not move. we are just trying to track our data from 2010 and forward and most vessels are stationary for 5+ years, and hopefully by then we will have a more polished system to track this, perhaps through a third party.

 

[jzwp22]

If you set it up correctly now, this can be your polished system and you will save the cost of buying something else.

Can a vessel move from one policy to another (same owner)? Can a vessel move from one owner to another?

 

[aml5171]

yes hopefully with all your help, which has been very informative, we will have a polished system that we can use for several years.

a vessel typically will not move from one policy to another or move from one owner to another. things remain relatively stable for the policy period which is normally one year. when the policy period ends however, assuming the "owner" wants to renew their insurance, we will issue a new policy and assuming they haven't changed their vessel names it will be the same vessels and same owner with a new policy number.

i apologize if i am not explaining this clearly

 

[jzwp22]

a vessel typically will not move from one policy to another or move from one owner to another. things remain relatively stable for the policy period which is normally one year. when the policy period ends however, assuming the "owner" wants to renew their insurance, we will issue a new policy and assuming they haven't changed their vessel names it will be the same vessels and same owner with a new policy number.

Yes, but what happens if the owner no longer wants a vessel? Do you return it to some type of inventory?

In this stage of setting up a database, you have to plan for what might happen in the normal course of business over an extended period of time otherwise your database will only be a snapshot and not a dynamic application.

 

[aml5171]

For the purposes of this database, whether or not an owner still technically owns a vessel is not really of concern.

For that particular policy year that we insure them they own the vessel and that is the data we are trying to capture.

I guess in a way we will be using the data to regularly take "snapshots" for analysis and comparison and from a dynamic standpoint the data will only change when we write a new policy and I manually upload the new information.

again all this advice and help is greatly appreciated. thank you again for your continued interest and assistance.

 

[jzwp22]

I guess in a way we will be using the data to regularly take "snapshots" for analysis and comparison and from a dynamic standpoint the data will only change when we write a new policy and I manually upload the new information.

It has been my experience that users will ask more of the you and the database once you give them an initial taste. I would guess that next year someone will ask you how does this year compare to last? Setting things up now will be better than having to redesign the database in the future. Now, I'll get off my soap box...

As we have been discussing this, I have outlined the structure and relationships in the attached database (your original DB, now modified). The only things left to deal with are the policy details and discount tables which just from an initial glance do not look normalized. I am also guessing that with the presence of gross and net fields that one is a calculated value. In general, calculated values are not stored, only those values used in the calculations are stored.

 

[aml5171]

Thank you for the response and the upload.

I have made the adjustments to my database to reflect what you have uploaded. As far as the policy details table and discount table go I made some changes.

Since the discounts are the same across a single policy I have set it up to reflect that with fkPolicyID in that table.

For the policy details table I set it up similarly but now with the fkVesselID to link each set of details to the corresponding vessel.

I have began setting up the relationships but am not sure how to get the one to many, one to one details to show on my relationship lines as in yours.

Also I am not sure what relationships are necessary for the tblPolicyVessel, tblProducts, and tblVesselLocationProducts.

I believe I need to add a foreign key for productID to distinguish what product is one which vessel and the same with pkStatusID in the tblStatus that i created. Perhaps these can go in the tblVessels?

Again thanks for your knowledgeable feedback and I feel we are getting close to the finished product I originally described and wish to have.

Attached is my database with the changes mentioned above. Any additional feedback is of course much appreciated. Thanks again!

 

[jzwp22]

an owner can have multiple policy numbers, as they can own many vessels in different parts of the world which are completely different policies we can write.

Based on the statement above that you made earlier, it implies that the policy on a vessel is not dependent solely on the vessel but also the vessel's location, so that has to be reflected in your table structure. I have made the necessary changes to accommodate this relationship & reattached the modified DB.

As to the policy details and discounts tables, they still do not look correct to me. Could you please explain what the fields in these two tables represent?

 

[aml5171]

Maybe I should have just shown you this from the beginning. Attached is the excel sheet that my coworkers upload their data into. When they issue a new policy they take a blank upload sheet and enter their new data into it then send me that sheet.

The purpose of this database is to contain all of their data that is in the multiple excel worksheets and then be available for analysis and comparison.

The Atwood 2010 tab is the data that I have loaded so far into the database. The next tab represents a second and different policy.

I hope this is helpful for you to see and maybe explains what the policy details and discount tables represent.

 

[jzwp22]

Looking at the spreadsheet & the two worksheets, it looks like the only non-calculated fields that are not otherwise accounted for are the deductible, Insured Amount, BOUM Line Size and Gross Rate. The values vary depending on vessel, location and product so I think they should belong in tblPolicyVesselLocations.

Once you have the structure set up correctly, you would set up forms so that your users can enter their data directly. Then you can do away with the spreadsheets altogether.

Still not sure about the discounts...

 

[aml5171]

Hi jzwp,

I feel that I have the database in a pretty stable structure thanks to all your help and would now like to begin to address my original problem. Summing

I would like to start setting up some queries that my co-workers can use to run useful searches. The first one I would like to be able to do is a simple query that just runs a summing of a single vessel.

For example, I have set the criteria to be " Like [Please enter vessel name] & "*" " so that my co workers can type in a single vessels name and then it sums up all the exposure, insured amounts, gross premium etc. for all the vessels named "Richmond".

When I run my query the way it is set up it is summing way too many fields. By my count Richmond’s total exposure is $766,066. I’m sure there is something stupid that I am doing here that is causing this but am just not sure what that is. Any help is greatly appreciated. Thanks a lot.

 

[jzwp22]

Your table structure is still not ready. As I mentioned yesterday:

Looking at the spreadsheet & the two worksheets, it looks like the only non-calculated fields that are not otherwise accounted for are the deductible, Insured Amount, BOUM Line Size and Gross Rate. The values vary depending on vessel, location and product so I think they should belong in tblPolicyVesselLocations.

Also, we still have to deal with the discount info.

With respect to your query, if you look at your query in design view, there are no join lines shown between the tables you added because you have not included all of the necessary tables. As a result, you get a Cartesian Product and then Access will try to sum whatever data is in that Cartesian Product