1 2 Previous Next 17 Replies Latest reply on Jun 19, 2014 11:51 AM by philmodjunk

    Show summary of a field

    JacksonLi

      Title

      Show summary of a field

      Post

           I have two tables: A and B. I created a layout based on table B. I have a field a1 on table A and I want to put the sum of this field a1 as a total number into the layout. The two tables have a relationship by ID number. How should I do it? I tried to create a summary field of a1 from table A and use field picker to add to the layout, but seems not working. Can anyone help pls? Urgent!

        • 1. Re: Show summary of a field
          philmodjunk

               Can you describe the relationship in more detail? Does the ID number uniquely identify records in table B or Table A?

          • 2. Re: Show summary of a field
            JacksonLi

                 table A and B have different fields, they only have the ID common field: for example;

                 table A has fields ID_number, a1,a2,a3,   and table B has fields ID_number, b1,b2,b3. their ID are identical

            • 3. Re: Show summary of a field
              philmodjunk

                   That makes no sense and thus does not answer my question.

                   a1, a2, a3 and b1, b2, b3 do NOT describe identical ID numbers.

                   In order to link Table A to Table B, you need to describe a unique ID number field in one, not both tables. Thus, Table A::ID might be defined as an auto-entered serial number or Table B::ID might be defined as an auto-entered serial number. but if you are setting up this relationship:

                   Table A::ID = Table B::ID

                   They cannot both be auto-entered serial numbers. In which table is ID set up as a unique serial number?

                   This is important because if Table A::ID is set up as an auto-entered serial number, then one record in Table A can match to many records in Table B. But if you set this up so that Table B::ID is the auto-entered serial number field, then a Table B record can match to many records in Table A.

                   Which type of relationship that you have here will be a critical detail in determining how any kind of summary value would be computed and displayed.

              • 4. Re: Show summary of a field
                JacksonLi

                     I did not set up the serial number. what I did is just make a link by their id number, looks like the picture.

                • 5. Re: Show summary of a field
                  philmodjunk

                       Which is not a good design decision on your part. From the table names, I must assume that you can have many records in the Claims Table linked to one record in the Premium Table. But that could be an incorrect assumption. I am also assuming that the value in "Insured Number" is different in every record in Premium Table.

                       If those guesses are correct, then you have a one to many relationship from Premium Table to Claims Table.

                       On which table (occurrence) is your layout based? Premium or Claims?

                       In which table is the value for which you need to show a summary total?

                  • 6. Re: Show summary of a field
                    JacksonLi

                         insured number in premium table are all different and more records than claim table. I need to make relationship between the two tables using insured number as key and  create a layout based on premium table. and the layout need to show "insured number, insured name, policy effective date, premium" (from table premium) and total amount (in claim table) with matching insured number

                    • 7. Re: Show summary of a field
                      JacksonLi

                           for example:

                           table A                                                                    table B

                           insured num     premium                             insured num                 total

                           1                                a                                              1                                5

                           2                                 b                                             2                                6

                           3                                c                                               2                                7

                           4                               d                                               3                                 8

                           5                               f

                           the result layout should look like:

                           insured num             premium              total

                           1                                       a                           5

                           2                                       b                           6

                           2                                       b                           6

                           3                                       c                           7

                            

                      • 8. Re: Show summary of a field
                        JacksonLi

                             and need show premium total on this layout , e (for example, e=a+b+b+c)

                        • 9. Re: Show summary of a field
                          philmodjunk

                               Define a field of type calculation in the Premium Table as:

                               Sum ( Claims Table::Amount )

                               This will show the total claims amount from each set of Claims Records linked to a given Premium Table record.

                               A summary field defined in the Premium table can be used to calculate the total premium amount.

                               And instead of linking the records by an externally produced Insured Number, use an auto entered serial number set up like this:

                               Premium Table::__pkPremiumID = Claims Table::_fkPremiumID

                               __pkPremiumID would be an auto-entered serial number and _fkPrmiumID woudl be a number field.

                               Keep Insured Number as a data field in Premium Table if this is a value that you need to use that comes from a source outside of your database.

                               Here's why:

                               If you use an ID value from outside of your database, there's a small chance that you may have to change a value after it has been entered into your database and used to link records in the Claims Table to a record in the Premium Table. This might be due to a data entry error with your database or an error that takes place in the data delivered to you for entry/importing into your database. The source of this number might even decide to change this value at some point in the future.

                               Should the value need to be changed after you have entered it into your database in a Premium Table record and then used it to link to several Claim Table records, editing this field to make the change, disconnects your claims table records from the premium table record to which they were linked. This potentially serious data integrity issue is avoided when you link records with an internally generated ID that is never ever changed after being assigned to a Premium Table record.

                          • 10. Re: Show summary of a field
                            JacksonLi

                                 I did created the pk and fk ID and set pk as auto-entered serial number, then I linked a relationship between pk and fk. Which fields should I add to the new layout to show the expected result?

                            • 11. Re: Show summary of a field
                              JacksonLi

                                   and I was asked to "create a relationship between the premium table and claim table using field insured number as key", how should I make the link?

                              • 12. Re: Show summary of a field
                                philmodjunk

                                     Why would there be a new layout?

                                     Whether using a new layout or a previously existing layout, it is important to use a layout that specifies Premium Table in Layout Setup | Show Records From.

                                • 13. Re: Show summary of a field
                                  JacksonLi

                                       Can I send you the instruction ? I am new to this software, maybe it will be more clear if you see it.

                                  • 14. Re: Show summary of a field
                                    philmodjunk

                                         I do not see the need.

                                         Please explain this:

                                         

                                              and I was asked to "create a relationship between the premium table and claim table using field insured number as key", how should I make the link?

                                         Asked by whom and why?

                                    1 2 Previous Next