5 Replies Latest reply on Jun 29, 2011 12:16 PM by philmodjunk

    Relational Reports

    ScottLaird

      Title

      Relational Reports

      Post

      I'm a bit stuck and looking for advice.

      I have a database with Multiple Tables.

      Table 1:  Contains Account Numbers (key) along with fields for account plan names.  (Large list 200k records)

      Table 2:  Contains Account Numbers (key) along with field for Promo Account Names (Small List 1k records)

      Table 3-8 (1 file for every month) Contain Account Numbers (key) along with Account Balance, Service Fees charged, Interest charged.  (Large files 100k records)

      I would like to create a summary report that looks like this that can be filtered by month.

      Plan 1: Average Account Balance Average Fees Charged Average Interest Charged

      Promo A

      Promo B

      Promo C

      Promo D

      No Promo

      Plan 2: Average Account Balance Average Fees Charged Average Interest Charged

      Promo A

      Promo B

      Promo C

      Promo D

      No Promo

      Any suggestions or direction would be much appreciated.

        • 1. Re: Relational Reports
          philmodjunk

          If tables 3-8 were merged into a single table, this would be eaiser to set up. A find could then be performed to just find the records for a specified month or the report can be structured (and the records sorted) to produce a break down like this for every month.

          Given separate tables for each month, I think you'd need to create a separate report layout based on each of Tables 3 - 8 to produce this report.

          Summary fields defined in tables 3-8 can compute your averages. The Plan 1, Plan 2 lines can be sub summary layout parts "when sorted by Table 1::Plan name) with these summary fields added to display the averages for each plan.

          The Promo lines have me confused a bit here. What beside "Promo A", "Promo B", etc. would show on these lines? What is the connection between data in tables 3 - 5 to each "Promo Account Name" in table 2. (Doesn't appear to be account number here.

          In which table, if any, is Account Number the primary key? (A table's primary key uniquely identifies each record in the table.)

          • 2. Re: Relational Reports
            ScottLaird

            Thanks for your reply.

            I haven't used Filemaker in a few years, so I appreciate your help.

            To answer you last question first.   The account number has been used in each table as the key, this may not be the best way to move forward.

            Tables 3-8 use the account number as the key.   So, I'm not sure how I would merge the files together since the account number would be then duplicated.   I assume I could create a key each record then combine it all into one big table.  And then add a field to each record to specify the month.

            Would this work?

            If so, i think the next part is where is gets tricky...

            I'll try to give you a better understanding as to the overall structure.

            The Account Number is a 9 digit number.

            The Plan Name is a text string.

            The Promo Name is a text string.

            Example:

            Table 1:

            Fields: Account_Number, Plan_Name, start_date, Store_ID, Market (

            Table 2: Account_Number, Promo_Name, Promo_start_date

            Table 3-8:  Account_Number, Balance, Service_fee, Interest  (Every month I get a file with updated at Month end)

            Does this give you a better understanding?

            • 3. Re: Relational Reports
              philmodjunk

              ....Would this work?

              Please keep in mind that my "view" of your databse is quite limited. From here, there's no reason why you can't have more than one record in this merged table with the same account number. Each such record can include a date field to identify the month that it represents and you can use this date to control which set of records are referenced from other parts of your database or pulled up in a found set for reports like the current one.

              Where I am having an issue, is that you seem to have several records in table 2, with the same Promo_Name but different account numbers. Or you have several Plan records with the same account number but different plans.

              I'm trying to see how for one Plan, you can have more than one Promo_Name as your report example would appear to indicate is possible. I don't see a common value between tables 1 and 2 that would allow us to make that linkage.

              • 4. Re: Relational Reports
                ScottLaird

                I think I understand what you are suggesting.

                Table 1 contains all the account numbers that have ever existed.  Tables 3 to 8 have list all the account numbers that were active during that monthly period.

                Table 2 contains only account numbers that have a promo_name active.

                1. Here is an Example of an account with data in all tables:

                From Table 1 account 9870001234 with Plan_name of Cheap.

                From Table 2 account 9870001234 has a Promo_name of Special_Disccount

                From Table 3(jan) account 9870001234 Has Balance=350, Service_fee=50, Interest=25

                From Table 4(Feb) account 9870001234 Has Balance=1350, Service_fee=50, Interest=25

                From Table 5(Mar) account 9870001234 Has Balance=650, Service_fee=50, Interest=0

                From Table 6(Apr) account 9870001234 Has Balance=450, Service_fee=50, Interest=25

                2. Here is an Example of an account with no data in table 2:

                From Table 1 account 9000001234 with Plan_name of expensive.

                From Table 3(jan) account 9000001234 Has Balance=6300, Service_fee=350, Interest=25

                From Table 4(Feb) account 9000001234 Has Balance=1350, Service_fee=350, Interest=75

                From Table 5(Mar) account 9000001234 Has Balance=8650, Service_fee=150, Interest=0

                From Table 6(Apr) account 9000001234 Has Balance=4590, Service_fee=850, Interest=0

                A plan can only have 1 promo_name at one time.

                We have 3 possible Plan_name(s).

                Plan_name 1 can have with none or one of 6 promo_names

                Plan_name 2 can have with none or one of 3 promo_names

                Plan_name 3 can have with none or one of 3 promo_names

                I hope I am not confusing you......

                Does this help clarify?

                • 5. Re: Relational Reports
                  philmodjunk

                  In your original post, you gave this example:

                  Plan 1: Average Account Balance Average Fees Charged Average Interest Charged

                  Promo A
                  Promo B
                  Promo C
                  Promo D
                  No Promo

                  Plan 2: Average Account Balance Average Fees Charged Average Interest Charged

                  Promo A
                  Promo B
                  Promo C
                  Promo D
                  No Promo

                  That format implies that more than one Promo name is possible for a given name. If this is not the case, I don't follow what the list of promo names under each plan entry represents.