Same Table Calculations

[Mortie]

I want to make a comparison calculation on the same field in the same table but on different records in the table.

The specific table is a child table in a set and the links are working as they are supposed to work. The child table has the following fields

Agent_Id (C)
Sales_Year (C)
Sales_Type (C)
Sales_Result (N)

The Sales_Type field is a DDB with a variety of choices including but not limited to these

Auto New Business
Fire New Business
Auto PIF
Fire PIF
LLR
Retention

On a form I want to place two embedded browse. One browse querries the child table for Sales_Year based on a variable Year1 and the second browse queries the same table based on a second variable Year2. Thanks to Tom Cone Jr I have the browses working fine.

What I want to accomplish is show the difference in Sales_Result in browse1 as compared to browse2. These browses would show identical fields from the table but for different years. The Sales_Result information would be in the same order in each browse as well as all the same Sales_Type in each browse. My first thought is these would be separate calculated fields on the form.

Does anyone have any suggestions on how to accomplish this? This may be way over my head since I am a user of Action Scripting versus Xbasic. I also am thinking my setup of the child table is not right for what I want to do. Perhaps another table setup is more appropriate????

If additonal information is need please let me know as I was not sure how to really explain my need.

Thanks

[Tom Cone Jr]

Re: Same Table Calculations

Bob, I think you're going to have to rethink your design. When you run a query against the child table supporting your form it will affect both browses, since each is bound to the child table you just filtered.

An advanced technique that some have used involves linking the same child table to the parent table more than once. I don't have experience doing this, personally. It's my understanding that you might then be able to query each child separately. I caution you against doing this in your data entry form until you have more experience with Alpha Five.

Were you hoping to have the Sales_Result "differences" displayed for each row in one of the browse objects? You might be able to do this by using a single browse and by adding a column to display a calc field that does the computation based on the Year2 value entered by the user. Depending upon the filtering and summing that must be done to generate the yearly totals, figuring the "differences" may run slower than you'd like.

--tom

[Mortie]

Re: Same Table Calculations

When you run a query against the child table supporting your form it will affect both browses, since each is bound to the child table you just filtered.

Thanks Tom, In the back of my mind I believed this would be the case but was hoping there was a work around.

An advanced technique that some have used involves linking the same child table to the parent table more than once.

I have read about this technique on the forum before and invariably almost every A5 Consultant here has advised against it.

Were you hoping to have the Sales_Result "differences" displayed for each row in one of the browse objects?

Yes, that is my ultimate goal.

--tom

I have been thinking on this and was wondering if design-wise, two separate tables, current year and previous YEARS, would work best. I would use each Sales_Type as a field and place the Sales_Results in these fields. At the end of each year I would append the just ended to the previous. The queries should work and I am thinking it would be a matter of subtracting table 2 from table 1 in calculated fields.