Invoice layout in tables

[wirejp]

Relationship

Kindly find attached, the relationships in my database. I tried to create a query called "qryinvoice " and the fields in the query were blank. Is there something wrong with my database design?

[twinnyfo]

A couple things real quick, looking at your relationships.

You have two tables which seem to have odd set ups. tblFrontingAdmi nInvoice and tblPremiumInvoi ce. Both of them have a field called "ID" and then a Primary Key, FrontingAdminFe eID and PremiumFeeID. It appears that you actuallhave two primary keys, the ID field and the actual primary key. Is there a reason why these two tables are set up this way, when all your other tables appear to be set up properly?

Probably, the primary Keys you have identified should be in the place of the ID fields, but htere may be a reason for this.....

In your Table Policy Types, you have added a ClientsID. Why would you add the Client to the Policy Type, as the client has nothing to do with the policy type. Your tblPolicyTypes should be joined to tblClient on the PolicyTypeID (just like you have for the Invoice Type and Regions).

Just as a thought here, in tblPremiumInvoi ce and tblFrontingAdmi nInvoice, you have two fields, FirstS25M and ExcessofS25M (at least I hope those are "S" and not "$"). Just as advice, what would happen if your premiums changed such that the initial value was now set at $30M? Would you then have to change your table field name? I would recommend changing the field names to InitialValue and ExcessValue, and elsewhere in your project designate exactly what that value would be, so you can manage it programatically (we can cover those details later).

Both of htese tables also have a Total field and a ClientID field. There is never a need to have a total field in a table unless that is the only value that matters. Totals can be calcluated during execution. I'm not sure how your Client connection is there.

I am also a little confused on how those two tables are joined to tblRegions, because regions don't have invoices, clients do. And you also have a Region field in these tables. Very confusin how you have these tables set up right now.

Your table tblInsurers (that is your company, right? But I noticed in one of your spreadsheets, which I saw prior to it being pulled, that your company has different HQs around the globe), it has a Client Field. Again, a cllient is not specific to the insurer. Since hte table is already joined to tblClients as the Insurer, there is no need for client to be part of htis table.

[twinnyfo]

Also, keep in mind that I have your spreadsheet, so I can refer to that chart of premiums and fees, while others here will not know what we are talking about.

Do we still want to build a Form for your Regions Table? You really should have Forms for any Table that you will be updating regularly. This will protect your data.

Don't forget to make back up copies of your database constantly while we are in the process of building this project. I don't want all your eggs in one basket.

I just now noticed in you tblClients. You have six fields, three for Attentiona nd three for EmailAddresses. If I could, I would recommend you strip those fields out of your table. First, what happens if you have more than three contacts? Then you have a problem. What happens if you have fewer than three? Then you are wasting resources.

I would create a separate Table, tblClientContac ts. Four fields: ContactID, Contact (this would just be the person's name), EmailAddress and Primary (whihc would be a Yes/No field). You could make it more complex, by having separate fields for last name, first name, etc., but I don't think you need that level of detail if it is just a person you have email contact with. You could add the telephone number, too, if you ever call them. When you build a clients form, the contacts would be a subform that owuld populate and list all contacts for that particular client. This keeps your data nice and tidy.

[wirejp]

Hi Twinnyfo, thank you very much for all of your help. I really appreciate of all your assistance. I agree with your comments above and I have made the necessary adjustments to the Relationships in my database (see attached). Kindly note that I have added an additional table for insurance brokers, because we currently have an insurance broker firm who bill three specific clients, on our behalf, and the clients pay their premiums to the brokers and the brokers in turn pay the premiums to my company. I have also built a broker contact table which is linked to the insurance broker table.

In post 27 above, can show me how to build a Form for the Regions Table?

I have also attached a sample of the invoice which I will like to generate from the database, once the database is built properly and the data has been inputted (I have taken care not include any real data this sample spreadsheet so that the moderators will not erased it).

[twinnyfo]

wirejp,

I know you are eager to get this DB up and running so you can produce invoices. Please, just be patient as we work through this. That will come in time, but I want to get your DB set up, first, then we can build your reports.

Some more notes about your relationships. Please note how you have your relationships built between tblClients, tblRegions and tblRegionMinesi tes. This is a proper way to relate these three tables, as a Client comes from a particular region, and that region may have many minesites. So, you have a Region in tblMinesites, based on the RegionID.

However, for tblInsuranceBro kers, you have a field for BrokerContacts, related to tblBrokerContac ts. It is also many to one, but going the wrong way. Instead, you should set up those two tables similar to tblRegions and tblRegionMinsit es, in which tblBrokerContac ts has a field Broker, which is the foreign key to BrokerID fromtblInsuranc eBrokers. This will allow you to add multiple broker contacts to any broker. Then there is no need for the field BrokerContacts. I hope this makes sense. You have done well, so far.

I noticed that you do not have a table for Countries. Based on the spreadsheet for fees and premiums that I briefly looked at, it appears you often group your total invoices by country. So, you should create a separate table for the countries you will be working with. LIke before, an ID Field, a country code, Country name, and any additional details you might want to add. If there are specific things that always apply to the entire country (e.g. if the Country of Outer Slobovia--regardless of region--always has an anti-terrorism tax), then you may want to add that. If some of your taxes and fees are region specific or invoice specific then keep it off. However, there may be some items you want to include. I don't know your data well enough....

Then, in tblClients and tblRegions, you will want to add a field for Country (the country in which the mine resides). This should be placed right before hte field Region in tblClients, and as the first field after the RegionID in tblRegions. You may also need a country code for the address fields..... (probably).

You may wonder why we have fields for country and region? We need both, because when we determine which country a mine is in, we want to be able to select regions that only reside in that country. Also, when setting up our regions, we want to know which country the region is in. So we need this data in both locations. Once you have this table, add all the countries you work with.

For the Region Form, you should be making two forms:

1. Make a form called fsubMinesites. Record Source is tblRegionMinesi tes. Add controls to that Form for all fields except RegionMinesites ID and Region. To prevent ambiguity, rename your text boxes to "txt" followed by the FieldName (e.g. the text box added for the field RegionMinesites Code should be named "txtRegionMines itesCode"). From a developer standpoint, this is a good practice, because if you name the text box simply the name of the control, there can be some ambiguity with the code behind the form, as the phrase

Me.RegionMinesitesCode

could refer to either the text box or the field in the table beneath the form. In 99.44% of the cases, these are always the same, but there are cases in which you can only refer to one or the other, but not both (experience has figured this out for me :-) ). Also set the format of each of your text boxes: percentages should display as a percentage, with the desired number of decimal places, and fields that have just a two letter character code, for example, should be small in size. You want you for to look professional, as well as be user friendly. You can also do some research on input masks to limit how users add information to your form. Save and close this form.

2. Make a second form called frmRegions. Add all fields except RegionID (keep in mind that I am assuming that all of your ID fields are autonumber, which will auto increment with each new record. This is a good practice, too, as when this is not in place, we have to programatically add a new ID for each record. This can be done, but makes building forms and adding and deleting records a pain. Just like your other form, rename your controls. Since Country will be a combo box (it should be if you have set that field as a look up for the country), its prefix should be "cbo"--just applying my standard of naming conventions.... . Keep this form open in design view.

3. Now, you can either drag and drop fsubMinesites onto your frmRegions or you can use the wizard in the Form design toolbox for a subform. This subform should maintain its name on your form. It should also recognize that RegionID of frmRegions and Region on fsubMinesites are related. If you click on the subform (it may already be highlighted and show the properties, there should be those two values in "Link Master Fields" and "Link Child Fields". If not, click the little "..." on the right hand side of those properties and complete the wizard--it should be self-explanatory.

Whew! Let me know when you get the basics done, then we can work on the logic behind controlling how people enter the data.

[wirejp]

Hi Twinnyfo, thank you for your detailed explanation above. I have made the changes as you have described in the first three paragraphs of post 29.

In regard to your comment,

I noticed that you do not have a table for Countries. Based on the spreadsheet for fees and premiums that I briefly looked at, it appears you often group your total invoices by country. So, you should create a separate table for the countries you will be working with.

the Regions Table actually represents the Countries Table and I apologise for any confusion in my naming convention of this Region Table. The reason why I used term "Region" for the term "Country is because my boss uses the term "Region" to mean the term "Country". It is true that the minesites are located in a specific geographical region, say Western Spain etc, but for my purposes, I do not think that I will need that level of detail to be included on the invoices. The invoices will show the country name, the minesite name and the premium amounts.

I am ready to proceed with the logic behind controlling how people enter the data.

[twinnyfo]

Regarding the Country, will there ever be a time when you will want to aggregate the premiums by country? Such as, will you ever want to know, Spain (with all their regions and minesites) has total premiums of XXX, Canada has YYYY? This is actually an important questions, because backwards engineering this into your db after the fact will become a huge pain.....

[wirejp]

Yes, in fact one of the insurance policy types (i.e. the Environmental policy type) aggregate the premiums by country, for example Spain has a total premium of XXXX, Canada has a total premium of YYYY.

[twinnyfo]

If this is the case, then I would definitely recommend creating that Country Table. It may seem like extra work, but it is small compared to trying to build it in after the fact.

[wirejp]

Hi Twinnyfo,

I completed the setup of the frmRegions form with the embedded fsubMinesites subform. The frmRegions form is working correctly. Just to be clear, when you said the following

Make a form called fsubMinesites. Record Source is tblRegionMinesi tes. Add controls to that Form for all fields except RegionMinesites ID and Region.

I did deleted the text boxes for these fields. Is this correct?

If everything is correct, I am ready for the next step.

[twinnyfo]

A couple quick questoins and thoughts on your relationships, the next post will discuss the Form.

For tblCountries, I have a few questions about the five fields: CountryPremiumF eePercent,
CountryAdminFee TwoDotFivePerce nt, CountryAdminFee FivePercent, TerrorismLevyPe rcent,
LocalRetentionP ercent. First, although Access allows rather long field names (I think they can max out at 255 characters), there is no need to push that limit. It may be wise to shorten those names accordingly so that you still know what they are. When you have really long names, it can become a bear to type in these long names every time you use them in either your code or in queries.

Also, because I don't know how you calculate your initial premiums, I have to ask how you do it? Is there a standard premium percentage, based on the amount insured? Or, as your table implies, is there a different premium fee percentage for each country? Additionally, your two fields, CountryAdminFee TwoDotFivePerce nt and CountryAdminFee TwoDotFivePerce nt, appear to be always 2.5% or 5%. As mentioned before, if you ever change your admin fee schedule, you would have to re engineer your DB. Not to mention, if these are calculated based on another value (and they are always 2.5% and 5%) then there is no need to have these fields, as they can be calculated on run-time as the fess may change over time. Are both of those fields always charged? If they are only charged in certain circumstances, then it might be wise to change the type of field they are to a Yes/No field. Then, if the 2.5% fee is to be charged, then it is true, and you calculate the fee at runtime. Again, I don't know the specifics of how these are calculated, so I must ask.

Also, for the TerrorismLevyPe rcent and LocalRetentionP ercent, are these constant cvalues or values that change by country (or by region)?

For field names, perhaps consider these:

• Premium
• AdminFeeLow
• AdminFeeHigh
• TerrorismLevy
• LocalRetention

But, again, I emphasize that this is yoru DB, and I only make recommendations . If we could sit and chat about your db, this would go much more quickly. I appreciate your patience as we slowly work throug this.

[twinnyfo]

For the Regions Form, the first thing you will want to do is add a few Countries to tblCountries. Ultimately, you will want to build a Form for adding countries, too, but not necessary in these initial stages of testing. What I want to do is introcude to you some basic concepts in data management, that you control via forms. What we will be doing could be done by making certain primary key settings in the Table, such that, for example, Country and RegionName in tblRegions are setas primary keys, which means that there can be no duplicates among them. However, then the uniqueness of the record is determined by data, rather than a record identifier (the autonumber), which not only makes the autonumber field irrelevant, but when using this table for other parts of your db, becomes a bit clumsy.

A digression....

Excuse for for thinking, here, but I just realized that an alternate way for us to do this is to build your countries form, and have the regions form be a subform on it (the Minesites form would still be a subform on the regions form). The concept is simple: You open the Countries Form. You can add a country ro go to an existing country. That country will show your regions, and that region would list all the minesites associated with that region. This would be a bit trickier, but would probably be a better way to manage these items of data, as they are all inherently related to each other.

I have attached an example that I use regularly in my db. You will notice that the main form has certain data that applies only to the Project. This form is in Single Form Mode.

[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=77 57[/IMGNOTHUMB]

At the bottom of the form is a subform which lists the Board IDs associated with that Project. This Form is also in SIngle Form View, and has navigation buttons which allow the user to cycle through Board IDs associated witht he Project. That Subform also has a subform, which is is continuous forms view, which simply displays a list of all the specific boards associated with a particular Board ID. Don't get caught up in the language of my forms, but the same principle would apply for your forms. The only key to make this work is that the first two form/Subforms must be in single form view, and the final subform can be in any view, but should either be in continuous or datasheet--based on the preferences of the DB designer.

But, I digress.....

So, since we want our regions to be unique not only by the autonumber field, which will guarantee a unique record, but also by their data, we will do that using our form. This same principle can be applied throughout your db, wherever you need such requirements.

The next thing you need to determine is which fields absolutely positively need to be unique? Will it be Country and RegionName or Country and RegionCode? Make that decision and build your validation off those rules that you establish. For the purposes of this example, we will use Country and RegionCode.

In the VBA module on your Regions Form, you will want to establish validation code every time the Country or RegionCode are updated. Because each control will be doing essentially the same thing, we will actually be creating a separate function in your form that will validate the fields, and reset the value of the field just updated, so that the user cannot move on until it is correct. Here are the basics of the code:

Private Sub cboCountry_AfterUpdate()
    ValidateData (True)
End Sub

Private Sub txtRegionCode_AfterUpdate()
    ValidateData (False)
End Sub

Private Function ValidateData(fCountry As Boolean)
    Dim lngValidate As Long
    Me.Refresh
    lngValidate = Nz(DlookUp("[RegionID]", _
        "tblRegions", _
        "[Country] = " & Me.cboCountry & _
        " AND [RegionCode] = '" & Me.txtRegionCode & "'"), 0)
    If Not lngValidate = 0 Then
        MsgBox "You already have a Region Code by " & _
            "that name for this Country!  Try again!", _
            vbCritical, "Duplicate Entry!"
        If fCountry Then
            Me.cboCountry = Null
        Else
            Me.txtRegionCode = ""
        Else
        Me.Refresh
    End If
End Function

Here are the basics: The procedure first refreshes the form, to make sure any dirty data is saved, and we can guarantee that we will be looking at valid data. It then looks for the RegionID that has the same criteria for Country and RegionCode the user just entered. If it can't find a duplicate entry, nothing happens and the user can continue.

However, if it does return a value, then it warns the user. You will notice that we have a paramter included in this function, whihc is merely the flag fCountry. This idicates whether the Country was updated or the Region Code. Depending on this flag, the code will reset the recently updated control.

These are the basics--I did not include anything fancy or any error handling (which I always recommend) but I wanted to get you moving in the right direction, first.

Hope this helps!

[wirejp]

Hi Twinnyfo,

To answer your questions in post 35, I do not know how the initial insurance premium amounts are determined, as our insurance broker normally prepare the spreadsheets and send them to us so that we can bill our clients.

Two countries have to pay a local premium tax (denoted by "premium fee") to their local tax authorities. For example, a percentage amount say 1% of the premium amount is paid to the local tax authority, while the client will pay us the net premium amount. . This premium tax is specific to the country and it could vary year by year.

The two fields denoted by "CountryFrontin gFeeTwoDotFiveP ercent" and "CountryFrontin gFeeTwoDotFiveP ercent" represent fronting fee percentages of 2.5% or 5%. These percentages can vary year by year. Some countries use a fronting fee percentage of 2.5% (i.e. 2.5% of the premium amount), other countries use a fronting percentage of 5% (5% of the premium amount), while two countries use a mixture of 2.5% and 5% in their calculation (i.e. Fronting fee calculation is based on the premium amount. The local insurance broker office will act as a "front" on our behalf and collect the insurance premium from the client. The fronting fee is calculated based on teh premium amount where the calculation will be:- fronting fee= premium amount*2.5%)+(p remium amount * 5%). The fronting fee is specific to the country, policy type (e.g. property, marine, business interruption) and premium amount (initial value or excess value).

The Local Retention Percentage only applies to one country in one policy type (e.g. Country A with the Pandemic policy)

The Terrorism Levy only applies to one country and this percentage for each policy type.

I have updated the Relationship table. In this case, I have reversed the direction for the PremiumInvoice table and the FrontingAdminIn voice table.Let me know if this is correct? I created a separate table for the Local Retention and linked it to the Country table and PolicyTypes table. Is this correct?

How should I address the Fronting fees i.e. (2.5%, 5% or mixture of 2.5% and 5%)? Should I put them in a separate table and would the "Yes/No" field type option work?

[twinnyfo]

My friend, my reply will have to wait until tomorrow. I think we are getting closer to where you need to be. The table relationships are looking good.

I am still wondering about the premiums and fees. I still think there may be a way to manage this programmaticall y, using values in a table. It might be complex, but something along the lines of:

Coutnry
Region
Minesite
PolicyType
InitialPremium (determined by broker)
PremiumTax
InitialFronting Fee
AddlFrontingFee
TerrorismTax
RetentionFee

This would put all premiums, fees and taxes into one table, that could be updated easily and calculated easily too. If everything is based off the premium, and calculated, this ma just work. I'm just not sure. There may be some weird calculations. I will also have to study your spreadsheet. Sorry for the delays--I just don't have much time this afternoon.