Queries to fill a Report fo a Government Form

[Telesphore]

In a form to be completed by the institution, the Government requests to
insert the total number of tuition fees in a box for the previous (2009)
year for each student.
There are 3 terms: winter, summer and autumn
and
In each term, the student must have participated in >= 7 hours of courses
(or 7 credits)
What would be the queries to create to generate the report which will print
the form?
Thank you very much.

 

[KARL DEWEY]

insert the total number of tuition fees in a box for the previous (2009)
year for each student.
Confused I am. Is it to be the dollar total or the number of payments?

What has terms per year and required credits to do with payments or total
dollars?

 

[Telesphore]

Thanks,


"KARL DEWEY" a écrit

year for each student.
Confused I am. Is it to be the dollar total or the number of payments?

It is the dollar total payments for the year

What has terms per year and required credits to do with payments or total
dollars?

Because in each term or session of studies of Winter 2009, and Summer 2009
and Fall 2008, the number of hours (or credits) of studies has to be equal
or greater than 7 hours. This is considered a full time studies by the
government.

So that on the government form we must write the total sum of those
payments, the student having studyied during 1, 2 or 3 sessions.

 

[KARL DEWEY]

You did not say how your data is stored. Table and field names with datatype.
Post sample data.

 

[Telesphore]

Thank you again,

Here are the data:

tblStudents
StudentID
LastName
FirstName
Street
etc...

tblInscriptions
InscriptionID
StudentID
InscriptionDate
AdmissionFee
AdministrationFee
InscriptionFee
LibraryFee
etc...

tblCourses
CourseID
Credits
etc...

tblInscriptionDetails
InscriptionID
CourseID
CourseTitle
etc...

So for the year 2009
we must sum up what the student has paid for the Winter, Summer and Fall
terms,
after having sum up the fees ($) of Inscription, Admission, Administration,
etc. paid in each term
in which the student had taken at least 7 or more credits (that is a Full
Time)

 

[KARL DEWEY]

Should not tblInscriptions include school year and term fields?

What is --
Start and end of school year 2009
Start and end of Fall term
Start and end of Winter term
Start and end of Summer term

I assume Inscription fee is paid for each Inscription
I assume Admission, Administration, etc. paid once each term

 

[Telesphore]

Thanks,

The year and the term are noted by the secretary in the field
[InscriptionDate] only on the first of January for the Winter term, on the
first of May for the Summer term and on the first of September for the Fall
term.

Every item fee could be paid for each inscription.

I have 3 queries which already works for each term, but now I need to sum
the 3 queries (Winter, Summer and Fall) for the report?

Here is the query for the Fall Term in SQL, the month being "09":

SELECT tblStudents.StudentID, tblStudents.LastName, tblStudents.FirstName,
Format([InscriptionDate],"yyyy") AS InscriptionYear4Numbers,
For-mat(tblInscriptions.InscriptionDate,"mm") AS InscriptionMonth,
Sum(tblCourses.Credits) AS CreditsSum,
[Admis-sion]+[Administration]+[Inscription]+[RegistryAndtServices]+[Scolarity]+[Library]+[SessionsFormation]+[PedagogicalFees]+[DiplomaFee]+[LatePayment]
AS FallFees
FROM tblStudents INNER JOIN (tblCourses INNER JOIN (tblInscriptions INNER
JOIN tblInscriptionDetails ON tblInscriptions.IdInscription =
tblInscrip-tionDetails.idInscription) ON tblCourses.CourseID =
tblInscriptionDe-tails.CourseID) ON tblStudents.StudentID =
tblInscriptions.StudentID
GROUP BY tblStudents.StudentID, tblStudents.LastName, tblStudents.FirstName,
Format([InscriptionDate],"yyyy"),
For-mat(tblInscriptions.InscriptionDate,"mm"),
[Admis-sion]+[Administration]+[Inscription]+[RegistryAndtServices]+[Scolarity]+[Library]+[SessionsFormation]+[PedagogicalFees]+[DiplomaFee]+[LatePayment],
tblInscriptions.InscriptionDate
HAVING (((Format([InscriptionDate],"yyyy"))=Right(Year(Date())-1,4)) AND
((Sum(tblCourses.Credits))>=7) AND
(([Admis-sion]+[Administration]+[Inscription]+[RegistryAndtServices]+[Scolarity]+[Libra-ry]+[SessionsFormation]+[PedagogicalFees]+[DiplomaFee]+[LatePayment])>=786.35)
AND ((Format([tblInscriptions].[InscriptionDate],"mm"))="09"))
ORDER BY tblStudents.LastName;



"KARL DEWEY" <wrote