Function, array, query What do I need?

[Bobbye R]

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

StudentID BaseDate Degree Program ClassesRemaining Tuition
1001 2/28/09 xxx111 4
1000
1200 2/28/09 xxx377 3
1500
1200 5/31/09 xxx685 16
2300
1302 3/31/09 xxx377 42
1500
Classes are monthly. And I'll be updating from excel monthly for students
who drop out or new students, etc

In the first example student 1001 will have classes for March, April, May
and June. So I think I need to produce 4 records. One for each month.
(Unless there is some way to use the basedate and the number 4 in a report
that shows me how much income to expect for instance for the month of June)
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.

What do I do to 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 taken for in any month. So for the month of
May I can expect to receive $4000. (1001-$1000, 1200-$1500, 1302-$1500) But
not 1200 for class 685 because it starts in June.

Do I need to create a new record for every month a student takes a class?
If so, how would I do that using the date and the number of classes
remaining? I don't mind making a new table for this and then being able to
append future records. Even though I've been using access for years I still
feel like a beginner so please be very specific as to what to do and where to
do it.

Any help would be appreciated.

 

[Marshall Barton]

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

StudentID BaseDate Degree Program ClassesRemaining Tuition
1001 2/28/09 xxx111 4
1000
1200 2/28/09 xxx377 3
1500
1200 5/31/09 xxx685 16
2300
1302 3/31/09 xxx377 42
1500
Classes are monthly. And I'll be updating from excel monthly for students
who drop out or new students, etc

In the first example student 1001 will have classes for March, April, May
and June. So I think I need to produce 4 records. One for each month.
(Unless there is some way to use the basedate and the number 4 in a report
that shows me how much income to expect for instance for the month of June)
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.

What do I do to 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 taken for in any month. So for the month of
May I can expect to receive $4000. (1001-$1000, 1200-$1500, 1302-$1500) But
not 1200 for class 685 because it starts in June.

Do I need to create a new record for every month a student takes a class?
If so, how would I do that using the date and the number of classes
remaining? I don't mind making a new table for this and then being able to
append future records. Even though I've been using access for years I still
feel like a beginner so please be very specific as to what to do and where to
do it.

I agree with Tom from your earlier post of this question,
you do not need to create records in a table. You can "make
up" the records by using a query.

First, create a utility table (named Numbers) with one field
(named Num) and populate it with values 0, 1, 2, ... , 99.
Then create a query that looks something like:

SELECT StudentID,
DateAdd( "m", Num, BaseDate) As classdate,
Degree, ClassesRemaining, Tuition
FROM yourtable, Numbers
WHERE Num <= ClassesRemaining

You can then use that query as the basis for whatever you
need to do to run the report (maybe a crosstab query to get
the months in separate columns?)