AnsweredAssumed Answered

Dynamically create records in a table

Question asked by ClaudiuNemes on Nov 29, 2012

Title

Dynamically create records in a table

Post

     Dynamically create records in a table.

     Hi there,
     I need assistance regarding dynamically creating records in a table.
     I have the following situation.

     ***
     I have a database called Projects with the following tables:

1. Projects
     Prj_ID:
     Prj_Name:
Prj_DurationInYears:  3 (3 years)
     Prj_StartingYear: 2012
     Student_NoOfDisbursementsPerYear: 4
     Mentor_NoOfDisbursementsPerYear: 12

     Etc.

2. Students(a student could be involved in many projects; a student is tutored by one mentor)
     S_ID:
     Prj_ID:
     M_ID
     Prj_Name: (dropdown values list from Projects::Prj_Name)
     FirstName:
     Initials:
     FamilyName:
     Etc.

3. Students_Evaluation
     SE_ID:
     S_ID:
     FirstName: (calculation: Students::FirstName)
     Initials: (calculation: Students::Initials)
     FamilyName: (calculation: Students::FamilyName)

/* from here down I want, If is possible to create dynamically as many records as they are set for the number of years in the Projects Table (for this project only let’s say 3, starting from 2012; so a table like the one bellow) */

… 2012   GPA_SEM_I   GPA_SEM_II   GPA  …
     … 2013   GPA_SEM_I   GPA_SEM_II   GPA  …
     … 2014   GPA_SEM_I   GPA_SEM_II   GPA  …


     Year:
     GPA_SEM_I:
     GPA_SEM_II:
     GPA:
     Total_Absences_SEM_I:
     Unjustified_Absences_SEM_I:
     Justified_Absences_SEM_I:
     Total_Absences_SEM_II:
     Unjustified_Absences_SEM_II:
     Justified_Absences_SEM_II:
     Total_Absences:
     Unjustified_Absences:
     Justified_Absences:
     /* end */

     Etc.

4.Students_Disbursements
     SD_ID:
     S_ID:
     FirstName: (calculation: Students::FirstName)
     Initials: (calculation: Students::Initials)
     FamilyName: (calculation: Students::FamilyName)

/* from here down I want, If is possible to create dynamically as many records as they are set for the number of years in the Projects Table and also as many DisbursementsPerYear are set in the same table for students (for this project only let’s say 3, starting from 2012, with 4 disbursements per year; so a table like the one bellow) */

… 2012   D1   D2   D3   D4   Total …
     … 2013   D1   D2   D3   D4   Total …
     … 2014   D1   D2   D3   D4   Total …


     Year:
     Disbursements: “D”+i
     Total:
     /* end*/

     Etc.

5. Mentors(a mentor could be involved in many projects; a mentor could tutor at least 5 students but no more that 10)
     M_ID:
     Prj_ID:
     S_ID
     Prj_Name: (dropdown values list from Projects::Prj_Name)
     FirstName:
     Initials:
     FamilyName:
     Etc.

6. Mentors_Disbursements
     MD_ID:
     M_ID:
     FirstName: (calculation: Mentors::FirstName)
     Initials: (calculation: Mentors::Initials)
     FamilyName: (calculation: Mentors::FamilyName)

/* from here down I want, If is possible to create dynamically as many records as they are set for the number of years in the Projects Table and also as many DisbursementsPerYear are set in the same table for mentors (for this project only let’s say 3 years, starting from 2012, with 12 disbursements per year; so a table like the one bellow) */

… 2012   D1   D2   D3   D4   D5   D6   D7   D8   D9   D10   D11   D12   Total …
     … 2013   D1   D2   D3   D4   D5   D6   D7   D8   D9   D10   D11   D12   Total …
     … 2014   D1   D2   D3   D4   D5   D6   D7   D8   D9   D10   D11   D12   Total …


     Year:
     Disbursements: “D”+i
     Total:
     /* end*/

     Etc.
     ***

     Because the beneficiary (a nonprofit organization) have to export data quite often in order to report in some standard formats, required by the evaluation entities, I want to reduce the number of database fields by using the table relationships and portals. They also need the export in order to feed other databases, so from this point of view creating each specific field will be a good solution, while I could export the whole database into a single file. But again this will require the creation of hundreds of fields (if not more).

     Using repeating fields could solve the problem but it’s quite hard to export repeating fields.

     Any suggestion will be appreciated. Maybe the structure is wrong or incomplete? Maybe I have to use as many table occurrences as many years I have in each project? Maybe is better to split the database into project files?

     Many thanks for your time,
     Yours,
     Claudiu Nemes

Outcomes