Create new records based on a date

  • Thread starter Thread starter Bobbye R
  • Start date Start date
B

Bobbye R

I've imported an excel spreadsheet that list hundreds of students, the
current date, Degree Program , and the number of classes remaining for each
degree. For example:

StudentID BaseDate Degree Program ClassesRemaining
1001 2/28/09 xxx111 4
1200 2/28/09 xxx111 3
1200 5/31/09 xxx685 16

Classes are monthly. And I'll be updating from excel monthly also.

In the first example student 1001 will have classes for March, April, May
and June. So I need to produce 4 records. One for each month.
The months are continuous. (If the student is in 2 programs, the second
degree program begins the following month after the first. So for student
1200 there will be 3 classes for March, April and May, then 16 classes from
June2009-Sept2010.

I need a query that will create the new records based on the imported data.
Ultimately I want to be able to create a report that projects monthly income
based on the number of classes each student has left to take.

Any help would be appreciated.
 
On Sat, 21 Mar 2009 18:10:01 -0700, Bobbye R

If that's your ultimate goal (I'm glad you added that), adding
individual monthly records is not needed. You can simply calculate the
Sum of classes remaining (using a totals query) times the monthly fee.

-Tom.
Microsoft Access MVP
 
Not exactly.
I want to know how much income to expect for all students taking classes in
any given month for instance, Mar09, Apr09, May09, Jun09, Jul09 etc. Also,
different classes have different fees.
Right now as in the first example for Student 1001 I can expect $1000 for
Mar-Jun. Student 1200 $1000 for Mar-May and $2000 for the following 16
months.
So if they were my only students Mar09=$2000, Apr09=$2000, May09=$2000,
Jun09 =$3000 (this would be student 1001 last class and student 1200 first
class for the new degree), Jul09=$2000.
Is that any clearer?
 
Back
Top