Transcript Report

[suzeg]

Hi -
I am creating a Transcript report that will group courses taken by Semester.
I am having trouble figuring out how to work with the dates.
Semesters StartTerm EndTerm
Spring 1/2/2013 4/19/2013
Summer 4/29/2013 8/16/2013
Fall 8/26/2013 12/17/2013

I was attempting to use Month(datestart) "datestart is the date the course starts" as my logic but if a course started before April 19 then it would fall in the Summer semester when it should be in the Spring.

I was creating the logic in the report grouping.

I would guess that VBA is the way to go but I am clueless as to how to approach this problem.

Any help would be greatly appreciated.

 

[way2bord]

Personally I would consolidate in a query, then run a report off of the query.

2 tables:
1. tblSem holds
Semesters StartTerm EndTerm
Spring 1/2/2013 4/19/2013
Summer 4/29/2013 8/16/2013
Fall 8/26/2013 12/17/2013

2. tblCourses holds
Course DateStart
course1 1/3/2013
course2 10/3/2013
course3 4/15/2013
...etc

1 query:
copy and paste into SQL VIEW

 SELECT tblSem.Semesters, tblCourses.Course, tblCourses.DateStart
FROM tblCourses, tblSem
WHERE (((tblCourses.DateStart)>[tblSem].[StartTerm] And (tblCourses.DateStart)<[tblSem].[EndTerm]));

Then build the report off query result:
Semester / Course / DateStart

 

[suzeg]

Great! - I will give that a shot later today and let you know how it turns out. I did put the semester dates in table but I did not know where to go from there. I did not know if I needed some kind of relationship btw the 2 tables to make it work.
I will keep you posted -

 

[suzeg]

This worked great for the Semester Year 2013 however the query eliminates the previous year 2012. I added the 2012 semesters to the semester table but not sure how to edit the query to catch the year of datestart.
I created a field in the query called semyear:Year(datestart) to get the year but what next and will that work?
Thank you again for your help.

 

[suzeg]

My mistake - I think it is working fine. Thank you so very much!!!

 

[suzeg]

Unfortunately I am still having problems with the query. I have to include the student records in the query and I end up with a cartesean query. I think I need to link the Semester Table with a key field but I am not sure how this would work. Any suggestions?