Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

inserting multiple rows in child table auto

Posted on 2016-09-24
11
Medium Priority
?
80 Views
Last Modified: 2016-09-26
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
Comment
Question by:Muhammad Ahsan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 40

Assisted Solution

by:als315
als315 earned 1000 total points
ID: 41813467
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
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 500 total points
ID: 41813639
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
 
LVL 41

Expert Comment

by:Sharath
ID: 41814082
Did you try adding trigger on student table?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 41814304
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
 

Author Comment

by:Muhammad Ahsan
ID: 41814818
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41814837
<<<<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
 

Author Comment

by:Muhammad Ahsan
ID: 41814841
etsherman , yes , but what would you suggest
0
 
LVL 40

Accepted Solution

by:
als315 earned 1000 total points
ID: 41815063
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
 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 41815314
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
 

Author Closing Comment

by:Muhammad Ahsan
ID: 41815816
thank u all for your help great experience  !!!!!!!!!!
0
 

Author Comment

by:Muhammad Ahsan
ID: 41815820
very much thanks als315
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question