summing trouble

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.

In regards to the above, for the purpose of what I am currently trying to deliver all calculations, and data entry is done on the excel sheet. Due to my novice understanding of access we are not aiming that high yet as to eliminate the excel entry sheets for co-workers.

I would just like to be able to enter their submitted data into this database and have working queries. I did some reading on Cartesian Products and understand the concept and the problems that they can cause.

I added a few tables that were not there before, (tblPolicyVessel, tblVesselProducts) and made some adjustments to relationships between the tables.

For what I am trying to accomplish, ie. run a query on a single vessel name and sum all exposures, can this be done with the database's current structure. If not what adjustments need to be made to reach that point.
 

Attachments

I believe I have created a new query with the proper tables and relationships necessary. However when I run the query the only "vessel name" is seems to be pulling up data for is "Vicksburg". It is summing correctly and giving the right number but if i try any other vessel name that I know has values in it nothing shows up. If anyone can take a look at my database, and take a look at query "sum of insuredAMT" and possibly identify the problem that would be great.

I would like to be able to type in the name of any vessel and get the same result as the one for Vicksburg which seems to be working fine.
 

Attachments

For what I am trying to accomplish, ie. run a query on a single vessel name and sum all exposures, can this be done with the database's current structure.

No

I went ahead and finished the structure as I saw it (may not be correct). I also migrated the data from the two worksheet tabs from the spreadsheet you provided into the database. I created qryMain to show the beginnings of your spreadsheet. I then created a query qryExposureByVessel that gives the sum of the exposure by vessel which I am guessing is what you were after.

Items still that need to be addressed

1. discounts table
2. Gross rate: In one of your worksheets, the value was calculated based on the type of vessel (that is what I used in the attached), and in the other the gross rate was a manually entered value. You will have to resolve this one way or another.

Per your most recent post, you can use the second query to get the information you want via your form. It would be easier for your users to just select the vessel from a combo box rather than making them type in the name (avoids those frustrating spelling errors).
 

Attachments

Wow, so I really was way off huh. Thanks for the restructuring.

For our comparison and analysis the discounts are already applied to the gross and net numbers so really will not need to be evaluated or extracted through the database.

As far as you "migrating" the data from the spreadsheets to the database, how do I go about doing that myself accurately other than manually. There are about 50 more spreadsheets of similar size data so doing it by hand will be time consuming but if its the only accurate way then thats fine.
 
I took all of the data from the two worksheets and put them into one worksheet. I then cleaned that up a bit and then imported the data (only the non-calculated values) into a new table in Access. From there, I used about 8 append queries to pull the data from the imported table and append it into the correct tables. The queries got progressively more complex as you go deeper into the relationship hierarchy. I typically create the query and then run it but I do not save the queries because running them a second time would duplicate records. The queries have to be run in a specific order.
 
Sounds like some stuff that is way out of my league. So if I wanted to use the table structures you have created and manually upload the data and values from the multiple spreadsheets I have would that be possible. Or would I need to recreate your cleaned up version of our upload sheet in order to use the database.

I have continued using your qryMain to create a lot of very useful queries that are exactly like what we want but the data is now limited to just those two spreadsheets. I was wondering if there is a way for me to begin to import this data into these tables?
 
I went through and recreated the append queries I used (9 queries). I also included the table called sheet1 which holds the data imported from your spreadsheet after my cleanup (you'll see how I organized the data in sheet1). I then constructed and ran each append query in a specific order. I have indicated that order with a number in the query name. The database is attached. The database has no data in any of the tables except sheet1 (and your discount table which is not used in any of the queries). You can run the queries in order and see how the data is populated. For the queries to work, the field names and the table name itself (sheet1) must be the same as what is shown in sheet1.

The database should give you the steps necessary to import all of your Excel data. I would recommend that you combine all of your spreadsheets into 1 worksheet and import that entire worksheet and then run the queries indicated. If you do it with more than 1 imported set of data, you will have to modify the queries such that duplicates are not created. Depending on how much data is in your 50 spreadsheets, I do not know if you will hit any Excel size limits.
 

Attachments

I am currently working on compiling all of my worksheets into a single sheet, "sheet 1" format.

Once I have completed that and put it into the database as sheet 1, do I simply run each of your append queries in the labeled order?

Also would I build my qryMain the same as the way you did previously in v33?
 
I am currently working on compiling all of my worksheets into a single sheet, "sheet 1" format.

Once I have completed that and put it into the database as sheet 1, do I simply run each of your append queries in the labeled order?

It has to be sheet1 not sheet 1

And yes the procedure will be as you described, but you MUST take care of all remaining structural issues FIRST.

ISSUE 1: The Gross rate issue I identified (see below) MUST be resolved because how you resolve it might impact the table structure and thus the append queries

2. Gross rate: In one of your worksheets, the value was calculated based on the type of vessel (that is what I used in the attached), and in the other the gross rate was a manually entered value. You will have to resolve this one way or another.

ISSUE 2: the discount table (again)

ISSUE 3: You had some records (approximately 10) that had the same owner, policy, vessel, vessel location, vessel type and product but different values for the financial fields. This suggests one of 2 things:
1. You have incorrect data in your spreadsheet
2. You have another relationship that was never mentioned that will impact the table structure and thus the append queries.

That is why it is so important to get the table structure set up first!

Also would I build my qryMain the same as the way you did previously in v33?

Again it depends on how you resolve the issues above.
 
Issue 1: I went through most of the spreadsheets of data that we have and I believe that that one sheet I happened to be using as my first sheet to enter is the only one with a calculated gross rate dependent of the vessel type. All of the other sheets have manually entered gross rates. I am not exactly sure how this affects the table structure/queries but I assume it does.

Issue 2: I think we are going to remove the discount table entirely as it has no real added value per our analysis and comparisons. The discounts are already reflected in the financials.

Issue 3: I am going through the data and not finding these instances, must be overlooking something. However, I am assuming that this is due to an error by the underwriter who uploaded the data.

I hope that I am addressing these issues correctly and look forward to your feedback.
 
Issue 1: I went through most of the spreadsheets of data that we have and I believe that that one sheet I happened to be using as my first sheet to enter is the only one with a calculated gross rate dependent of the vessel type. All of the other sheets have manually entered gross rates. I am not exactly sure how this affects the table structure/queries but I assume it does.

I noticed the dependence of the gross rate on the vessel type due to some values in the spreadsheet showing up as FALSE (due to an error in the formula used in Excel). These "FALSE" values will not import correctly so I had to clean up the spreadsheet before I imported it. You will have to look for this before doing an import from your other spreadsheets.

Now as to the impact on the table structure...
In the databases I posted yesterday, the gross rate field is in table tblVesselTypes (since the rate was dependent on the vessel type), you will need to delete that field and add it to tblPolicyVesselLocationProducts. You will then have to adjust qry9AppendPolicyVesselLocationProductDetail to include the gross rate field that comes from sheet1 and its corresponding equivalent field that you create in tblPolicyVesselLocationProducts to which the data will be appended.

Issue 2: I think we are going to remove the discount table entirely as it has no real added value per our analysis and comparisons. The discounts are already reflected in the financials.

You would know best on this.

Issue 3: I am going through the data and not finding these instances, must be overlooking something. However, I am assuming that this is due to an error by the underwriter who uploaded the data.

I've attached an Excel file (sorted sheet1 in Access & dumped to Excel) with the rows that have duplicate data (except for the financials) highlighted. This is something for which you will have to watch as it will skew your data.
 

Attachments

I made the necessary adjustments to the tables regarding the gross rates as well as the changes to the qry9AppendPolicyVesselLocationProductDetail to include the gross rate field coming from sheet1 and its corresponding field.

In regards to the duplicate data that you highlighted, I contacted the underwriter who submitted the information and he mislabeled the product type so it was a mistake on his part and a great catch by you.

I am now going to work on compiling the many worksheets I've been submitted dating back to 2010 to present into a master "sheet1" that I will then import into the database to replace the current "sheet1"

Assuming I do all this correctly I then run each append query in the numbered order and then can use newly created queries on qryMain?
 
Assuming I do all this correctly I then run each append query in the numbered order and then can use newly created queries on qryMain?

You will have to adjust qryMain to that you pull the gross rate field from the correct table based on the structure changes you have made. From there you should be set to move forward with whatever else you want to do.
 
Great, I can't thank you enough for all your help along the way. Have a great weekend.
 
You're welcome and have a good weekend as well.
 
hi jzwp,

hope all is going well.

I have made some adjustments to the format of your "sheet1" which is what I am using to import my data from excel to the database. Per my bosses request I have added net premium and net rates to "sheet1". Also all rates will be manually entered and are not dependent on vessel type or product.

I am not sure what changes need to be made to the append queries so that the database will run properly.

After importing my "sheet1" with about 9 worksheets worth of data into the database, and running the append queries it is generating a large amount of data, which I remember you mentioning as being a potential issue.

Attached below is the database as I have it as well as my "sheet1"

Any help is much appreciated. Thanks in advance!
 

Attachments

net premium and net rates

"Net" implies that you have calculated the values from the corresponding gross values. If that is the case, then neither net value should be stored in a table. You would just create a query that does the calculation for you.
 
understood, but these calculations are already performed within the excel sheets which i am "cleaning" up and using as my upload sheet. I assumed it would be easier to tweak the appends to incorporate these added values rather then run the formulas again through access.
 
The problem with doing that is if someone changes one of the values that is used to calculate the net rate/premium, the net rate/premium would not be updated accordingly in the table. This will cause a data integrity issue. That is why calculated values are not stored. There are only a couple of instances where calculated values would be stored; yours is not one of them. In fact, I have never run into a time where I had a valid reason to store a calculated value. If your boss wants to see the net rate/premium, you would just show it via a query or in your form, report etc.
 
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.

I beleive I need to add dblGrossPremium, and dblNetPremium to tblPolicyVesselLocationProducts.

For net rates I would want to use an expression similiar to this one that you used for Exposure?

"Exposure: (tblPolicyVesselLocationProducts.currInsuredAMT+tblPolicyVesselLocationProducts.currROW+tblPolicyVesselLocationProducts.currSL)*tblPolicyVesselLocationProducts.LineSize"

but something like:

Net Rate:
(tblVesselTypes.dblGrossRate/tblPolicyVesselLocationProducts.dblGrossPremium)*tblPolicyVesselLocationProducts.NetPremium

Let me know what you think of this idea if you can. Thanks
 

Users who are viewing this thread

Back
Top Bottom