Solved

inserting multiple rows in child table auto

Posted on 2016-09-24
11
51 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
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 39

Assisted Solution

by:als315
als315 earned 250 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 125 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 40

Expert Comment

by:Sharath
ID: 41814082
Did you try adding trigger on student table?
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 39

Accepted Solution

by:
als315 earned 250 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 34

Assisted Solution

by:PatHartman
PatHartman earned 125 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now