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.)
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.
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?
....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.
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?
In your original post, you gave this example:
Plan 1: Average Account Balance Average Fees Charged Average Interest Charged
Plan 2: Average Account Balance Average Fees Charged Average Interest Charged
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.