New to relational databases
I have raw data from a relational database that I have loaded into FileMaker and indexed. The data is drawn from annual reports submitted for approximately 13,500 locations. Each report represents about 20 pages of text. I have report data for the last 12 years, so all together there are 162,000 reports.
The data is organized in 4 tables.
Table 1 - Report Table - 162,000 Records (one for each of the reports) - With a unique KeyField for each report (one record per report) along with select data for each record.
F2 Data1 - Location ID
F3 Data2 (Report Beginning Date)
F4 Data3 (Report End Date)
Table 2 - Alpha Table - In this table the KeyField repeats itself about 100 times for each alpha data field in the report, so there are about 16,200,000 records in this table.
- F1 KeyField
- F2 Contains the page the data is on
- F3 Contains the row the data is in
- F4 Contains the column the data is found. In.
- F5 contains the data itself.
Table 3 - Numeric Table -Set up in the same fashion as the Alpha table, however in this table the KeyField repeats itself over 600 times for each numeric data field so there are over 100,000,000 records in this table.
Table 4 - Rollup Table - I don't understand the logic of this table. This table has additional numeric data that it appears could have just been added to the numeric table. This table only has three columns. This table has about 20,000,000 records.
- F1 - Key Field (repeated as in Tables 2 and 3)
- F2 - A code which delineates the page, row and column of the number all in the same field.
- F3 - The numeric data.
I would like to accomplish 3 objectives with this data:
1 - Use Table 2 (Alpha table) to isolate certain unique KeyFields (reports)
2 - Display a list of the unique KeyFields (reports) and their corresponding information in Table 1 (Report Table) so that one or more or the reports can be manually selected based on information in the Report Table.
3 - Create two different reports
i - After isolating the KeyField (report) merge all the data for a single report back into an excel file or word document template. Essentially recreating the report electronically from the raw data.
ii - After selecting multiple reports use them to create a simple trend report comparing select data from the selected locations over the course of the 12 years.
Initially I will perform the finds in FileMaker myself, but eventually I would like to make a web based version.
I am told there are a number of ways to do this, but here is one example using my logic...
I typically want to isolate all the reports for a certain county. I accomplished this manually by doing a find with two requests. One that has that has the page, row and column (in F2, F3 and F4) where the state is inputted on the report with the state I am looking for in F5; and a second request with the page, row and column that the county is located in with the county I am looking for in F5. I then sort the records by the KeyField and extract the KeyField numbers that are repeated (I can't just search for the county because county names are repeated in different states). I take the numbers that are repeated and perform a search in the Report table and then use the information on the report table to constrain the KeyField further. I have not gotten to the step of manually creating the reports.
I am looking for some guidance on how I should set this up initially to make the process most efficient, quick and easy. Keeping in mind that eventually I want to make the process web based. I also need help in the next step of actually creating the reports.
Thanks in advance for any help you can provide.