Invoice layout in tables

[wirejp]

You are correct. An invoice number relate to a particular country but there may be multiple records for different regions within this country under this invoice number. So I guess that the invoice number is not an unique identifiable index to use. I was thinking about assigning an "PremiumFeeID" or "AdminFeeID" (which will be autonumbers) to each record in the Premium fee subform and Admin fee subform respectively, while the ID number (an autonumber) will be the primary keys. The fields ("PremiumFeeID" and "AdminFeeID") in the subforms will be linked to the main form as the foreign keys while the ID number will be the primary key, when I create the query. Is this a good idea?


[z{This thread was split from: Duplicate records created using two subforms in a main form }]

[twinnyfo]

wirejp,

This may seem like we are going backwards here, but since you are a novice, let's start with the basics:

1. What types of information are you diplaying (or trying to display) on your main form? I don't need all of the field names and such, but rather the general idea of what is on the main form.

2. What types of information are on your subforms? So far, we know that there is a country (or should be, better yet, a country code). And you mention wanting to create another InvoiceFeeID.

3. What is the record source for your subforms? Are they directly related to underlying tables, or are there complex queries generating the data?

The reason I am starting at the basics, is if we start there, we can probably re-build the forms (perhaps even the Tables) in a more structurally sound manner, teaching you some good principles along the way, but more importantly, resolving your problem.

This may take a while, but we'll try to stick with you along the way. I remember building my first database and then learning the hard way about better ways to do things. Most of what I learned about databases has been through experimentation and re-engineering, because there were no forums like this to learn from. Now, much of what I have learned has been reinforced by lots of really smart people who have shared their corporate knowledge (and I've also learned a lot of new things along the way, too).

I would also recommend you take a look at this article: Database Normalization. It may not make any sense at first, but once you start working more with databases, it will become more clear.

Now, let's roll up our sleeves and get to work!

[wirejp]

I am very grateful for all of your help. On the main form, I will like to display the following information: - Insurer Name, Insurer Address, Client Name, Client Address, Country Name and Policy Name and Policy Type, Attention 1, Attention 2, Attention 3, Email Address 1, Email Address 2 and Email Address 3.

On the Premium fee subform, I will like to display the following information in a table as: - Invoice Number, Invoice Date, Invoice Paid, Country Name, Region Name, Effective Date, Expiry Date, Primary Layer Amount, Excess Layer Amount, Total Amount. Outside of the table, there will be a field box to sum the total of the Primary Layer Amount and Excess Layer Amount. Note that for each invoice number, there is one country but a country can be subdivided into different regions. So an invoice number of a country will have multiple records for the different regions.

The same fields will apply for the Admin fee subform. Kindly note that the data will be the same in both of the subforms will be the invoice dates, country, region names, effective date, expiry date. The data which will be different will be the invoice numbers (i.e. an invoice no. in the premium fee subform will be denoted by "A" while an invoice no. in the admin fee subform will be denoted by "A1"), primary layer amount, excess layer amount and the total amount.

The record source for the subforms was the Country Name and the country name is found in the underlying tables. I created a two separate queries to generate the two subforms and I then embedded them in the main form.

I was thinking about creating a PremiumFeeID number and an AdminFeeID number in the subforms but I will hide these fields.

I was thinking about rebuilding a new database from scratch, as I have been deleting blank records, when these problems starting arising. Should I do this or should I just modify the existing the database?

I had previously lumped the client's address in one field but I guess that I should break it out into individual fields for Address 1, Address 2 etc.

[twinnyfo]

I will have to address this later this afternoon, as I must run. I will take a look at this in a while.

[twinnyfo]

wirejp,

As much as it sounds like this is a lot of extra work, I will ecommend that you begin by building your database from scratch. If you are going to build something, do it right the first time.

Wow! Do you mind if we do this one step at a time? I'll try to explain what we are doing and why we are doing it along the way.

First, conceptualize the different tables that you will need. If I read your Post #12 correctly, this is what I see we need (at least):

1. A Table for Insurers
2. A Table for Policy Names/Types
3. A Table for Invoices
4. A Table for Countries
5. A Table for Regions within a Country
6. We must figure out what to do with the Premium Fee and Admin Fees

Before we start building these tables, I need to ask some more questions about your Insurers information. You list Insurer Name and Insurer Address and then, on the same form, Client Name and Client Address. Is it possible that one insurer has multiple Clients? Or, is the Insurer always the same for the Client (i.e. the Client is the person at Insurer Name that you always work with?). I think it is the first scenario, that the Insurer has multiple clients. If this is the case, then we also need a Table for Clients.

It may seem obvious, but what are the Attention 1 - 3 and Email Address 1-3 for? The reason I ask, is that if they mean what I think they might mean, there may be better ways of doing this, and I want to verify before we do anything.

For your Policy Names/Types, will all policies of the same name always be of the same type (and vice versa)? If this is the case, then we need one Table for your Policies. If these can be different, then we will need different Tables, one for Policy Names and one for Policy Types.

For your Invoices, is there always one and only one purchase item or transaction per invoice. For example, does an invoice contain only one policy, or can it contain multiple policies with separate details for each policy? If these are unique records, then one table for invoices will suffice. If not, we need an additional table for Invoice Details, so that one invoice can cover one, two or a million separate transactions.

Your description of the Countries and regions implies that not only does the country affect the cost, but so does hte region of the country. This is why I have included a table for Regions within the Countries. More details will follow. one question I have is how is it determined how the region/Country affect the cost? What is the formula used to arrive at the total cost?

You also say

an invoice number of a country will have multiple records for the different regions.

So, will one invoice, potentially, have multiple aspect that cover multiple regions within that one country, or is each invoice for a distinct region. This could complicate things.

Explain the difference between the Premium Fee and the Admin Fee and how they are calculated. Are these set fees based upon the country/region? Policy type? This will help me understand how to have you build/relate these tables.

This may seem like a lot right now. But it is always wise to sit back and look at your databse strategically first. It's alot easier to begin building from a good blueprint. You may have to slightly move a few walls later or change the color of the paint to suit your style, but at least the main frame of the house will be built properly.

Once I get more details, we will work to start making tables (I hope).

:-)

[wirejp]

There is only one insurer (my company). The insurer has multiple clients. The client name is client's company name, and the Attention 1, Attention 2 etc. and Email 1, Email 2 are contact names and email addresses of certain individuals at the client's company.

Policy Name and Policy Type are two different items. Policy Name is insurance product which may be one of the followin items: property, business interruption, pandemic and marine.

I just realized that Policy Type is wrong name to use. It should be Invoice type and this Invoice type will relate to the type of invoice which the client is being billed for. IT will be one of the following items:- premium invoice, admin invoice, debit memo invoice and credit memo invoice. We will usually be billing for premium fees and admin fees. But if we overbilled/underbilled then we will have to issue a debit memo invoice or credit memo invoice.

One invoice will contain only one policy name.


The Premium Fee is the Primary layer amount + the Excess layer amount. Admin Fee is also the Primary layer amount + the Excess layer amount (but admin fee is smaller amount than the premium fee). There is a spreadsheet which perform complicated mathematical calculations to calculate the premium fees and admin fees. But for my purposes, I just manually input these amounts into the database and let the database sum the total amount of the primary layer and excess layer of each record. It is the grand total amount which the customer is billed for (and this total amount will sit in a field box outside of the subform table).

One invoice is for multiple regions of a country. For example Country A has (i) region A1 which is billed for $A1 for propery premium insurance, region A2 which is billed for $A2 for propery premium insurance and region A3 which is billed for A3 for property premium insurance. The grand total amount will be $A for property premium insurance for Country A ($A1+$A2+$A3=grand total amount).

This is the flow of events:
1 client - 1 invoice no. - 2 invoice types (premium fees and admin fees) - 1 country - multiple regions - each region has an primary and excess layer amounts - primary and excess layer amounts are summed to give a total amount

I just checking to see if these are all of the tables which I have to build so far?

1. A Table for Insurers
2. A Table for Policy Names/Types
3. A Table for Invoices (what fields to use?)
4. A Table for Countries
5. A Table for Regions within a Country
6. A Table for Clients

On the outside of the subforms, I will also like to include two comment boxs: - (i)If there are any queries to contact my company and include my name and email address and (ii) my company's wire instructions to send the invoice payment to us.

[wirejp]

On the outside of the subforms, I will also like to include two comment boxs: - (i)If there are any queries to contact my company and include my name and email address and (ii) my company's wire instructions to send the invoice payment to us.

[wirejp]

In the Table for Clients, should I input the individual companies names or should I also include the company mailing address too?

What fields should I include for the Table for Invoices?

[twinnyfo]

wirejp,

Thanks for your patience. Although it seems like some of us here are watching Bytes 24/7, I am not.

So far, I think we are making progress! We do not need a Table for Insurers, as that is you, and that information should never change. However, if it does, there are easy ways to manage that change without having to completely re-design your db.

This spreadsheet that calculates the fees: depending on how complex the calculations are, you may be able to (or want to) incorporate that functionality into the DB, building a Table based off this Spreadsheet, which you can then manually update as any fees change. OR, you can link that spreadsheet to your db. I don't know how complex the spreadsheet is and whether it has multiple worksheets and things like that. We can disucss in greater detail to come to some conclusions.

If you don't mind, we should work piece by piece, to make sure we get things working right a bit at a time. For the time being, we will use the information in Post #14 and Post #15 as our strategic plan and refer back to that frequently. Keep in mind that this would move much more quickly if we were working together, but the online world will have delays and I will only be able to guide as I have time.

So, here is now what I have as a list of tables (this could change as we discuss more):

1. A Table for Clients
2. A Table for Policy Names (based on above, I would call these Policy Types)
3. A Table for Invoice Types
4. A Table for Invoices
5. A Table for Countries
6. A Table for Regions within a Country
7. Maybe a Table for the Fee Schedule

Let's begin by making three Tables. I will make recommendations for certain things, based on experience, and accepted standards, but ultimately, you will have to decide on how you want to build these tables.


First Table - tblPolicyTypes

For Table Names, I alway recommend the above naming convention. Spaces are an absolute "no-no". I prefer without Underscores, but some programmers love them. The "tbl" prefix indicates that this is a Table, so that throughout your DB, you will always know that any references to "tblPolicyTypes" is to a Table and that Table contains a list of Policy Types.

Your first field should be the Primary Key, which can either be an autonumber field (but not necessary, as you will probably not have thousands of different Policy Types) or an integer (this saves a couple bytes of data for every record).

Your next fields can be at your discretion, but will probably be Text values. I would recommend two of these additional fields. The first would be a short description of the Policy Type: "Property", "Business Interruption", "Pandemic" and "Marine". The second would be a longer description (up to 255 Characters), such as "Property Policies cover blah blah blah". You may also consider having a one- or two-character Policy Type Code that would display on some documents to save space, (e.g. "PR", "BI", "PA", "MA"). This is not required, but I often use such a field in my DB, to very, very briefly display the pertinent information.

You may also want to include any other pertinent information that you know about, that would apply specifically to all policy types (cost would NOT be included, because that varies by country/region). If there is a numerical policy identifier, which applies to all policies of the same type, that would be included.

So, recommendations for field names: You may want to do your own research, but there are almost as many naming conventions out there as there are DBAs. Some good places to start are here and here (Mods, forgive me, but I saw no really good links on Bytes.com. Of course, always avoid any reserved words as your Field Names. For example, even though you want the Client's "Name", "Name" is a reserved word, and although it can be used as a valid field name, Access can get confused sometimes when you use this word. I prefer to use "CamelCase" field namese, without underscores, but that is personal preference.

As an example of your Policy Types Table:

FieldName               Data Type
PolicyTypeID:           Integer
PolicyTypeCode:         Text, 2 Characters
PolicyType:             Text, 25 characters
PolicyTypeDescription:  Text, 255 Characters
Any other pertinent fields

Now, using these same principles, create two more Tables, one for Invoice Types and one for Countries.

You may wonder why we are starting with these Tables first, instead of clients, but there is an important reason, which we will discuss later.

Let me know how you come along with these Tables, and also provide discussion on the Spreadsheet information.

Talk to you soon!

[wirejp]

I have finished creating the tables for PolicyTypes, InvoiceTypes and Countries. However, I have not entered any data in the fields. Should I go ahead?

For a specific policy type (say Pandemic insurance), there is a spreadsheet has a master worksheet which show the breakdown of the premium fee amounts and admin fee amounts for the primary layer (the basic insurance amounts) and the premium fee amounts and admin fee amounts for the excess layer (the additonal layer of insurance covered by the reinsurers) for each region of each country. So the primary layer calculations are on the left side and excess layer calculations are on the right side. I will now delve in the spreadsheet calculation: - The premium fee is some notational amount while the admin fee amount is 1.5% of the premium fee amount. This a simple calculation for some countries. However for some countries, taxes are deducted from the premium amounts, and their premium amounts have gross-up for the taxes (3% of the premium amount).

From this master worksheet, I just create my insurance invoice for each country in different worksheets using a simple template where i just manually input the summary total amounts of the primary layer fees and excess layer fees in a spreadsheet (I could link these summary totals from the master worksheet, but a creatur of habit, I just manually input the data). I then convert the invoic spreadsheets into pdf format and email the invoices to our clients.

[twinnyfo]

Yes, add all of the data points for your three Tables now.

Now, I want you to create another Table: tblRegions. This table should have at least three Fields.

The first is the Primary Key for the Table itself (RegionID). Since you could, conceivably, keep adding and deleting countries and regions in the future, it will be OK to use an autonumber for this field. It is just useful to always have a separate unique identifier for every record in every Table (it may not be feasible in all situations, but always recommended, especially if you have lots of data and do frequent sorting and searching).

The second field should be for country--you mention you are a novice to Access, so I will ask, do you know how to reference the values of another Table? When you are in Table Design mode, Access makes it very easy. Under the Data Type drop down, select "Lookup Wizard...". Just follow the prompts through the wizard, and you will want to use your tblCountries. Be sure to include the Primay Key as one of the seleted fields (usually first). Then, interestingly enough, you can choose any or all of the other fields to add. Some advice: If you have, for example, a two-character code for your countries, e.g. "US" for United States, "RU" for Russia, "AF" for Afghanistan, etc. (you may or may not have added this to your table, but I will show a good reason why), then it may make sense to shoose just the two-character code in addition to the primary key. In the next screen of the wizard, you can choose how to sort the records--this is important, because lets say you add countries to your Table in the following order:

PK Country
1 United States
2 Russia
3 Afghanistan

And then you choose to sort your records by the PK. They will always be in that order, no matter what--just like you enterd them. However, if you sort by the country name, or the two-character code--or whatever, they will be listed alphabetically when the user tries to select a country.

In the next screen, click "Hide key column". Then click Next and Finish.

The third Field will be your Region. This could either be a region code, as simple as 1, 2, 3, etc., or a combination of the country code and a number US1, US2, RU3, RU4, etc., or however you want it to be. Set the data type and properties appropriately.

You may also want some additional fields to either describe the Region (i.e. US1, could be "Eastern half of the US", or list the states, or something to that effect. You will want to add fields that will be specific to the region only.

Importantly, in Post #19 above, you mention that some countriees (or regions?) have a tax which affects the premium. If this tax is specific to the country only, then which table should it go in? The Countries Table. If this tax variees by region, then it should go in the Regions Table. See how all these things are connected together??

Now, add all of the Records for this table. This may take a while depending on how many countries and regions we have. We could also build a form do help you add these records, and if you prefer to do it that way, we can do that. Doing this, we could also build in a validation which would prevent you from adding two identical regions for the same country. This is your choice.

I realize that your fee spreadsheet may be proprietary, but I would really like to see how it works, because I think we can automate this much better using the tables we are currently building. Would it be possible for you to post a copy of that spreadsheet? Please change any information that would cause you to reveal any company secrets.

I sincerely wish we were in a classroom setting, because we could work through this much more quickly. I hope I am not holding you up at your work or keeping you from project completion.

[wirejp]

You are a great help and I am learning a lot from your teaching. You are not holding me up at my work or keeping me from the project completion. The spreadsheet sameple is attached.

[wirejp]

In Post #20, above, you mention about building a form to help me add records

Twinnyfo:
We could also build a form do help you add these records, and if you prefer to do it that way, we can do that. Doing this, we could also build in a validation which would prevent you from adding two identical regions for the same country. This is your choice.

I would be glad if you can show me how to do this.

[twinnyfo]

wirejp,

I may have to wait a short while for this. I am away from work, with family visiting. But I may have some free time in the mornings. Hold on tight....

[wirejp]

Hi twinnyfo, thank you for the update and I hope you enjoy your time with your family. I will keep an eye for any new posts from you. In post# 15, I said that one invoice has one policy name. However, I will like to make a correction to this comment: one policy type (for example property) can have two invoice numbers - there is an invoice number for the premium fee and the other invoice number is for the admin fee.

[wirejp]

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. tblFrontingAdminInvoice and tblPremiumInvoice. Both of them have a field called "ID" and then a Primary Key, FrontingAdminFeeID 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 tblPremiumInvoice and tblFrontingAdminInvoice, 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, tblClientContacts. 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 tblRegionMinesites. 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 tblInsuranceBrokers, you have a field for BrokerContacts, related to tblBrokerContacts. It is also many to one, but going the wrong way. Instead, you should set up those two tables similar to tblRegions and tblRegionMinsites, in which tblBrokerContacts has a field Broker, which is the foreign key to BrokerID fromtblInsuranceBrokers. 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 tblRegionMinesites. Add controls to that Form for all fields except RegionMinesitesID and Region. To prevent ambiguity, rename your text boxes to "txt" followed by the FieldName (e.g. the text box added for the field RegionMinesitesCode should be named "txtRegionMinesitesCode"). 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 tblRegionMinesites. Add controls to that Form for all fields except RegionMinesitesID 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: CountryPremiumFeePercent,
CountryAdminFeeTwoDotFivePercent, CountryAdminFeeFivePercent, TerrorismLevyPercent,
LocalRetentionPercent. 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, CountryAdminFeeTwoDotFivePercent and CountryAdminFeeTwoDotFivePercent, 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 TerrorismLevyPercent and LocalRetentionPercent, 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.

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 "CountryFrontingFeeTwoDotFivePercent" and "CountryFrontingFeeTwoDotFivePercent" 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%)+(premium 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 FrontingAdminInvoice 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 programmatically, using values in a table. It might be complex, but something along the lines of:

Coutnry
Region
Minesite
PolicyType
InitialPremium (determined by broker)
PremiumTax
InitialFrontingFee
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.