Relating tables with month and year

[Agentshevy]

I have two tables Claims and Premium

Records on both tables have dates

I want to do an analysis for both tables on monthly and yearly basis with a calculation field
Analysis (Claims::claims + Premium::premium)

my objective is to be able to gather all the records for each month  and each year on both tables using that analysis. That is my basic task. There are other fields like Remark, and recommendation but they are based from the result I get from the Analysis. For example, Analysis field is > 120 ,  Remark shows Excellent..  

please how do I achieve this?
 

[comment]

If you need to summarize the amounts of both Claims and Premium together, then why aren't they in the same table? Linking them by a relationship will force your report to include all records within the given month in at least one of the tables, without the ability to filter out some of them by performing a find.

In any case, if you define a calculation field (result is Date) as =

YourDatefield - Day ( YourDatefield )  + 1

you can then use it both as the breakfield in the local table and as a matchfield to the other table, to aggregate all records in the same month.

 

Similarly, you can use =

Date ( 1 ; 1 ; Year ( YourDatefield ) )

for the yearly summary.

[Agentshevy]

1 hour ago, comment said:

If you need to summarize the amounts of both Claims and Premium together, then why aren't they in the same table? Linking them by a relationship will force your report to include all records within the given month in at least one of the tables, without the ability to filter out some of them by performing a find.

 

Thanks for prompt response. Actually the premium and Claims are on different tables because the data comes in from different sources and are being handled by different departments. So I was thinking, my Analysis field  would be based on a new table say Analysis? (My thoughts)

So my concern is how to relate the claims and Premium tables with the Analysis table so that I can do the analysis based on month and year ... thanks

 

[comment]

3 hours ago, shevyshevy said:

the premium and Claims are on different tables because the data comes in from different sources and are being handled by different departments.

I don't think those are good reasons to put them in different tables. There may be other reasons to separate them (I don't know anything about them except their names), but generally things that need to summarized together are a single entity.

 

3 hours ago, shevyshevy said:

So I was thinking, my Analysis field  would be based on a new table say Analysis?

What would each record in this table represent?

[Agentshevy]

On the Premium table, I have the following fields

Premium::ID_Premium

Premium::Premium

Premium::date

Premium::Salary

Premium::Admin fees

 

Then on the Claims table, I have the following fields

Claims::ID_Claims

Claims::Date

Claims::Claims

 

My Analysis calculation supposed to be the total Claims for an entire month subtracted from the Total premium for the entire month. That is exactly what I want to achieve. (Should I put both Claims, Premium and Analysis Fields on the same Table?)

From your suggestion, I have tried putting both Premium and Claims on the same table, I created separate layouts for Claims and Premium, but While navigating through the records, most records for the Premium are empty because of empty Premium fields during data entry. This is because when records are entered on that table, Premium Records maybe be entered just once in a month.

 I really need an advice on best way to handle this. Thanks

 

[comment]

3 hours ago, shevyshevy said:

 I really need an advice on best way to handle this.

I don't know what's the "best" way to handle this, because I don't know enough about your solution. I can give you some options, but ultimately the choice must be yours.

 

3 hours ago, shevyshevy said:

My Analysis calculation supposed to be the total Claims for an entire month subtracted from the Total premium for the entire month.

As i said earlier, this is possible - if you want to total all Claims and all Premium for that month. For example, you could define these fields in the Analysis table:

• SerialID - Number, Auto-enter serial number, starting from 1
• gYear - Number, Global
• cMonth  - Calculation (result is Date) = Date ( SerialID ; 1 ; gYear )

Now you can define two relationships matching cMonth to the two calculation fields in the Claims and Premium  tables described earlier. Then define another calculation field = Sum ( Claims::Amount ) - Sum ( Premium::Amount ).

Create 12 records in the Analysis table and you will have a summary for the year you enter in gYear.

[Agentshevy]

Many thanks, So much appreciate this.. let me implement this and see how it goes..

[Agentshevy]

I have tried implementing what you said. But I cant seem to get it working. I don't really know where messed. I have attached the file, if you have time you can go through it. Thanks for your response

ANALYSIS.fmp12

[bruceR]

You placed the gYear field on the layout.

You labeled it Sort Date

You entered not only the wrong year; but a not "year" value at all.

Instead, you entered a strange date: 10/18/3565.

If you set up the fields properly, you get what appears to be a correct result.

[Agentshevy]

Thanks a million.. My head has been heating up preety bad lately, that I tend to forget details..

 The Claims and Premiums are supplied by organizations, using this relationship, can I be able to sort or sumarize the result according to the organizations? such that we actually know which Organization has the Analysis?

If I want to chart my data, should I summarize it based on the cMonth or gYear?

thanks

[comment]

33 minutes ago, shevyshevy said:

can I be able to sort or sumarize the result according to the organizations

You could define a global Organization field in the Analysis table and add it to the relationships, so that only the selected organization's records are summed. To summarize several organizations at the same time, with a sub-summary value for each, you would have to have 12 analysis records for each organization. I don't think that's a good method because adding/deleting an organization should happen in an Organizations table, and not require additional operations in another table.

[Agentshevy]

Thanks a million.. its working perfectly..

I want to have a chart of this layout.... because i want to represent it graphically... How do I go about it? which fields do I really need to pull this off.. I have tried several combinations of ID_Analysis, cMonth and gYear.. not really getting what I want...

Secondly, is it ok to talk about chart here or should my post be moved to the Chart section?

thanks

 

[comment]

What exactly do you want your chart to show?

Please keep in mind that I have made several suggestion, but I don't know which one you have implemented.

[Agentshevy]

On 1/30/2016 at 6:00 PM, BruceR said:

You placed the gYear field on the layout.

You labeled it Sort Date

You entered not only the wrong year; but a not "year" value at all.

Instead, you entered a strange date: 10/18/3565.

If you set up the fields properly, you get what appears to be a correct result.

this is what I have implemented.. and this is it what i want to represent on the chart

 

On 1/30/2016 at 7:38 PM, comment said:

You could define a global Organization field in the Analysis table and add it to the relationships, so that only the selected organization's records are summed.

I have also implemented this as well.. so my analysis layout is Organization specific

What I want to be displayed on the chart is the Analysis amount for each month , with respect to the Organization selected 

[comment]

Okay, so why is this a problem? Make SORT DATE your X axis field and ANALYSIS the Y axis field, and set your chart to use data from current found set.

[Agentshevy]

Ok Thanks.. Its actually a problem when you don't know exactly what you are doing.. Thats why we need guardiance..