[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

inserting multiple rows in child table auto

i have two tables
1-  students (student_Name ,registration_no(primary key))
2-  fees (registration_no(foreign key) , fees , months , status )

question
         what i want to do is to auto add 10 years worth of months in fees_table automatically for every new student added in students_table
0
Muhammad Ahsan
Asked:
Muhammad Ahsan
  • 4
  • 2
  • 2
  • +2
5 Solutions
 
als315Commented:
Exact result will depend from type of months field in you database.
Common idea: create table with values from 0 to 119 and use it in append query. Do not use join, simply add this table to query. If month is date field, use dateadd("m", ValueFromAddedTable (0-119), StartDate)
0
 
Eric ShermanAccountant/DeveloperCommented:
Do you want to do this each time a new student is added in the Students table from a From Object or is this a one time update???

Also, what type of field is Months in the Fees table???

ET
0
 
SharathData EngineerCommented:
Did you try adding trigger on student table?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
PatHartmanCommented:
Creating empty future records is generally a bad idea.  What if a student only stays for 6 months?  You have 9 years and 6 months worth of records that will never have any meaning.

If this is a billing app, generate the new records each month as part of the billing process so you only generate them for active students.
0
 
Muhammad AhsanAuthor Commented:
Als315 , thanks for help , but can u you be little more descriptive about solution it would really help

etsherman ,(1) yes i want  to do this each time a new student is added in the Students table from a From       Object     (2)  field : Month        data type : text          
                      default value / lookup  :  example ( 01-2000 ...02-2000..... 03-2001... 04-2002.......12-2010 )

Sharath , thanks , but a trigger has to be initiated every single time rather what i would like to do is add a automatic sequence which will trigger this trigger with ever new student entry. can u help me with that.

PatHartman , thanks for advice , if i am to follow your way then i would like to create a code that would add a new month in fees table automatically by the end of previous month and to not add a Month for  un-active student  but suppose a student have returned to institute after a year than a code have to resume adding from that returning month of student and skipping in middle months when student was un active ... so could u help me with code

note :  (1) in month field i only want month and year  (this field would be month for which fees is to be submitted)
 Example :                                                                             Feild               Month                              status                    
                                                                                             *record           03-2007                             paid
                                                                                                   *                 05-2008                             not paid    
 
                         (2) date of submitting fees would have different filed        

                              field : date_of_submission         data type : date/time         formate : general date     default value : now()

Request   :     i have specialty in Python , Mat-lab, lisp and C++ / C languages but not in Ms access so would  really appreciate  tolerate my question and try helping further
0
 
Eric ShermanAccountant/DeveloperCommented:
<<<<what i want to do is to auto add 10 years worth of months in fees_table automatically for every new student added in students_table>>>>

What are you using for the starting month for the 10 years (120 months) worth of months???  In other words, do you start the counter at the month the student was added???

ET
0
 
Muhammad AhsanAuthor Commented:
etsherman , yes , but what would you suggest
0
 
als315Commented:
Look at sample. I prefer to store number instead of text, but in sample text field was used. I've used format of this field like 2016-05, because in this case it could be sorted correctly. Select start date and press button. There is used query qryAppend.
DBStudents.accdb
0
 
PatHartmanCommented:
Muhammad,
Without much more detail, I couldn't make any specific recommendation.  But, the process of adding new records is essentially one line of code that runs an append query.

The append query selects active students and takes a parameter to tell it what month you are creating invoices for and appends an invoice record for each selected student.  Gaps are not relevant since you are doing a month at a time.  If a student is active in January, then an invoice will be added for January.  If a student is not active in February, no invoice is created.

In addition, you are better off using a datetime data type.  If you don't want a specific day, then use the 1st of the month.  That allows you to take advantage of all that date fields do.  Do NOT use a text year-month field.  You will be constantly working around it.
0
 
Muhammad AhsanAuthor Commented:
thank u all for your help great experience  !!!!!!!!!!
0
 
Muhammad AhsanAuthor Commented:
very much thanks als315
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now