Creating new data based on date

[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.

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll need to use a month-end calendar table. Just fill a one column
(named "month_end") table (named "Calendar_MonthEnds") with the month
end date for each month you'll be using. Then use a query like this to
get the new rows (records):

SELECT P.student_id, C.month_end, P.degree_program
FROM StudentPrograms AS P, Calendar_MonthEnds AS C
WHERE C.month_end Between DateAdd("m",2,[base_date]-Day([base_date]))
And DateAdd("m",[remaining_classes]+1,[base_date]-Day([base_date]))
ORDER BY P.student_id, C.month_end;

Change the table/column names to suit your set up.

The criteria's DateAdd() formula calculates the needed dates in the
calendar table based on the base_date and the number of
remaining_classes from the Excel s/s.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBScZy/4echKqOuFEgEQKcWgCg3WvGPzC9tgtC/svs3GT9ycF/ffAAoKEv
zsd2EQuE525JxO5MED+x8sha
=CdlP
-----END PGP SIGNATURE-----