How To Reduce Four in Three With Switch and Sum

  • Thread starter Thread starter Telesphore
  • Start date Start date
T

Telesphore

SELECT
[Lastname] & " " & [Firstname] AS StudentName,
tblStudents.Street,
tblStudents.City,
..
Format([InscriptionDate],"mm") AS InscriptionMonth,

Switch(
Month([InscriptionDate])=1,"04",
Month([InscriptionDate])=5,"06",
Month([InscriptionDate])=9,"12") AS EndTermMonth,

Switch(
Month([InscriptionDate])=1,"4",
Month([InscriptionDate])=5,"2",
Month([InscriptionDate])=9,"4") AS TotalOfMonths,

[Admission]+[Inscription]+[Schooling] AS Fees,

....

In this query for tax returns, we need only three (3) periods: 1 for the
January-April term, 5 for the May-June term and 9 for the September-December
term.

It happens sometimes that a student is registered twice during the same
term, for example in the September-December term.

So 1) how can we integrate these two (2) inscriptions in the two (2)
"Switches" and 2) how can we totalize the sum in the "Fees"?

Thank you in advance.
 
Telesphore,

I am sure it is possible to do what you ask. However, I think we would
need to know more details about your tables and query before any
definitive help could be given.
 
Thank you Steve,

Our school must give the students a certificate for tuition fees paid by the
students for the 3 session periods of winter, summer and fall (year and
months with 2 digits) and amounts (Admission, Inscription, Schooling)

We need 2 tables, 2 queries, 1 report (rptTuitionCertificate) and 2
sub-reports (srptTuitionFeePeriods and srptTuitionFeeTotal).

2 tables:
tblStudents for [StudentName], [Street], [City] .
tblInscriptions for [InscriptionDate], [Admission], [Inscription],
[Schooling],

2 queries:
For the rptTuitionCertificate we need the qryStudentName: [StudentName],
[Street], [City], [InscriptionYear]

For the 2 sub-reports we need the qryTuitionFee, that I fist sent here.

Hope this helps to understand the situation.

"Steve Schapel" wrote
Telesphore,

I am sure it is possible to do what you ask. However, I think we would
need to know more details about your tables and query before any
definitive help could be given.

--
Steve Schapel, Microsoft Access MVP

SELECT
[Lastname] & " " & [Firstname] AS StudentName,
tblStudents.Street,
tblStudents.City,
.
Format([InscriptionDate],"mm") AS InscriptionMonth,

Switch(
Month([InscriptionDate])=1,"04",
Month([InscriptionDate])=5,"06",
Month([InscriptionDate])=9,"12") AS EndTermMonth,

Switch(
Month([InscriptionDate])=1,"4",
Month([InscriptionDate])=5,"2",
Month([InscriptionDate])=9,"4") AS TotalOfMonths,

[Admission]+[Inscription]+[Schooling] AS Fees,

...

In this query for tax returns, we need only three (3) periods: 1 for the
January-April term, 5 for the May-June term and 9 for the
September-December term.

It happens sometimes that a student is registered twice during the same
term, for example in the September-December term.

So 1) how can we integrate these two (2) inscriptions in the two (2)
"Switches" and 2) how can we totalize the sum in the "Fees"?

Thank you in advance.
Click to expand...
Click to expand...
 
Telesphore,

If I understand you correctly, you will need a field in the
tblInscriptions table as a foreign key to the tblStudents table, in
order to indicate which stident each Inscription record belongs to.
 
Steve,

To make the text shorter here, I forgot to tell that the two tables are
linked by the field [StudentID].

Steve Schapel said:
Telesphore,

If I understand you correctly, you will need a field in the
tblInscriptions table as a foreign key to the tblStudents table, in order
to indicate which stident each Inscription record belongs to.

--
Steve Schapel, Microsoft Access MVP

Thank you Steve,

Our school must give the students a certificate for tuition fees paid by
the students for the 3 session periods of winter, summer and fall (year
and months with 2 digits) and amounts (Admission, Inscription, Schooling)

We need 2 tables, 2 queries, 1 report (rptTuitionCertificate) and 2
sub-reports (srptTuitionFeePeriods and srptTuitionFeeTotal).

2 tables:
tblStudents for [StudentName], [Street], [City] .
tblInscriptions for [InscriptionDate], [Admission], [Inscription],
[Schooling],

2 queries:
For the rptTuitionCertificate we need the qryStudentName: [StudentName],
[Street], [City], [InscriptionYear]

For the 2 sub-reports we need the qryTuitionFee, that I fist sent here.

Hope this helps to understand the situation.
Click to expand...
Click to expand...
 
Telesphore,

Ok, thanks for the further explanation.

As I now understand it, you need to handle the situation of any given
student with more than one Inscription during the same term. Is that
right? You can do this with a Totals Query, something like this...

SELECT StudentID, Month([InscriptionDate]),
Sum([Admission]+[Inscription]+[Schooling])
FROM tblInscriptions
GROUP BY StudentID, Month([InscriptionDate])
 
Thank you Steve

"Steve Schapel"
As I now understand it, you need to handle the situation of any given
student with more than one Inscription during the same term. Is that
right?
Click to expand...

That's right

You can do this with a Totals Query, something like this...
SELECT StudentID, Month([InscriptionDate]),
Sum([Admission]+[Inscription]+[Schooling])
FROM tblInscriptions
GROUP BY StudentID, Month([InscriptionDate])
Click to expand...

It works! My problem was that I was grouping instead of suming the
calculated field :
[Admission]+[Inscription]+[Schooling] AS Fees.

Many thanks again.

--
Steve Schapel, Microsoft Access MVP

Steve,

To make the text shorter here, I forgot to tell that the two tables are
linked by the field [StudentID].
Click to expand...
Click to expand...
 
Back
Top