Jump to content

Relating tables with month and year


Agentshevy
 Share

This topic is 2335 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?
 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

gYear.png

gYear_data.png

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

gYear_fixed.png

gYear_result.png

  • Like 2
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

gYear.png

gYear_data.png

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

gYear_fixed.png

gYear_result.pngthis 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 

Link to comment
Share on other sites

This topic is 2335 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.