Monthly query

[Guest]

I am running a music school. The students pay their fees at a monthly basis,
fixed rate, depending on the course and level. I need to find a way to run a
query showing students whom haven't pay their fees for each month.

 

[John W. Vinson]

I am running a music school. The students pay their fees at a monthly basis,
fixed rate, depending on the course and level. I need to find a way to run a
query showing students whom haven't pay their fees for each month.

What's the structure of your tables? How do you currently record fees and
payments? Can your students pay in advance: i.e. if someone pays for three
months' fees in August, you wouldn't want to see them turn up as owing in
September and October. Are there partial month payments?

This can be done... but it can also be complicated!

John W. Vinson [MVP]

 

[Steve]

Mew,

The tables I suggested to you in the Queries newsgroup will make this easy.
I showed you how to do it. The only change since you updated your post with
your fee structure is that when you run the update query for AmountDue you
just update to the monthly flat fee.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[StopThisAdvertising]

Mew,

The tables I suggested to you in the Queries newsgroup will make this easy.
I showed you how to do it. The only change since you updated your post with
your fee structure is that when you run the update query for AmountDue you
just update to the monthly flat fee.

Did it occur to you there that maybe the OP does *not want* your payed help !!
Nobody wants that in the groups !!!!!!! (enough exclamations ??)

Go away Steve !! Get lost !!
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3650+ pageloads, 2350+ first-time visitors (these figures are rapidly increasing)

ArnoR

 

[John Marshall, MVP]

Surprising as it is, he is actually talking about the users problem rather
than getting paid. The key give away was that he did not use the word
"reasonable".

John... Visio MVP

Mew,

The tables I suggested to you in the Queries newsgroup will make this
easy.
I showed you how to do it. The only change since you updated your post
with
your fee structure is that when you run the update query for AmountDue you
just update to the monthly flat fee.

Did it occur to you there that maybe the OP does *not want* your payed help
!!
Nobody wants that in the groups !!!!!!! (enough exclamations ??)

Go away Steve !! Get lost !!
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3650+ pageloads, 2350+ first-time visitors (these figures are
rapidly increasing)

ArnoR

 

[Guest]

I'll list down the tables and fields that I think is relevant, there are
quite a lot.

tblStudentsParticular
StudentsID, Students contact etc

tblCourseParticular
CourseID, Courses, DurationOfCourse, FeeOfCourse(per month, flat)

tblLessonsInfo
StudentsID, CourseID, DayOfLesson, TimeOfLesson, Studio, TecherID,
CommencementDate, TerminationDate

tblReceipt
ReceiptNumber, Date

tblFeePayment
Description(can be or can be not Course since students pay for registration
fee as well), StudentID, Amount

I need the monthly query to show the students' name, fee due for each course
they take, day and time of their lesson.

Thanks for your time, Mr Vinson.

 

[Guest]

Relax, ArnoR.
Making people aware of "Steve" is sufficient.
In this case, he is playing by the rules.
You would better serve the community if you leave him alone when he is
behaving and hammer him unmercifully as soon as he offers services for a
"reasonable" fee.

 

[John W. Vinson]

I'll list down the tables and fields that I think is relevant, there are
quite a lot.

tblStudentsParticular
StudentsID, Students contact etc

tblCourseParticular
CourseID, Courses, DurationOfCourse, FeeOfCourse(per month, flat)

tblLessonsInfo
StudentsID, CourseID, DayOfLesson, TimeOfLesson, Studio, TecherID,
CommencementDate, TerminationDate

tblReceipt
ReceiptNumber, Date

tblFeePayment
Description(can be or can be not Course since students pay for registration
fee as well), StudentID, Amount

I need the monthly query to show the students' name, fee due for each course
they take, day and time of their lesson.

Thanks for your time, Mr Vinson.

You may have a problem then... it appears that tblFeePayment doesn't record
the date of payment! Without that information, how can you tell which monthly
payment the fee applies to? And again: can a payment cover more than one
month? or can you have partial payments? If payments cover things other than
course fees, is there any way to tell other than trying to parse the
human-readable (but cryptic to computers) Description field? Is there any
relationship between tblReceipt and tblFeePayment?


John W. Vinson [MVP]

 

[StopThisAdvertising]

Relax, ArnoR.
Making people aware of "Steve" is sufficient.
In this case, he is playing by the rules.
You would better serve the community if you leave him alone when he is
behaving and hammer him unmercifully as soon as he offers services for a
"reasonable" fee.

I can see your point Dave, but I respectfully disagree.
We have done this long enough. We played *his* game long enough ...

So no matter what he posts here, we will go after him.
He knows this, and he has been warned often enough.

As long as he does not behave we don't need him, we don't want him.
So I will 'hammer him unmercifully' as long as he advertises.

I don't see *any* sign of him that he is willing to stop his blatantly advertising.
So please killfile StopThisAdvertising if you don't want to see these messages.


Btw: In the particular thread that Steve mentioned (ms.public.access.queries) he *was* advertising.

Arno R

 

[Guest]

You may have a problem then... it appears that tblFeePayment doesn't record
the date of payment! Without that information, how can you tell which monthly
payment the fee applies to?

I am so sorry, I forgot to insert some important fields in tblFeePayment:
Description, MonthYear(mmm/yy of fees paid for), ReceiptNumber, StudentID,
Amount.

And again: can a payment cover more than one month?

Yes, the payment can cover more than one month. Some parents pay for 3
months one shot.

or can you have partial payments?

If the students cannot come for one whole month but want to reserve the time
slot, then they will pay 1/2 monthly fee. Other than that, the fees are
always collected in full.

If payments cover things other than course fees, is there any way to tell other than trying to parse the human-readable (but cryptic to computers) Description field?

I don't have anything on that. Other than the fees, the other two most
common payments are, Registration Fee ($20, one time payment) and refundable
deposit, which is always equivalent to the fee. I will see what i can do.
Thank for reminding me.

Is there any relationship between tblReceipt and tblFeePayment?

Yes, I forgot to include the fields. ReceiptNumber is the foreign key in
tblFeePayment.

 

[Guest]

Please don't abandon me.... I still have no solution to my problem.

I have listed the missed out fields on 23/8. Maybe you didn't read it since
it was above the other two response.

 

[StopThisAdvertising]

Please don't abandon me.... I still have no solution to my problem.

I have listed the missed out fields on 23/8. Maybe you didn't read it since
it was above the other two response.

Mew,
I guess this is meant for John Vinson (since he is the one trying to answer you) ??
Please post to the 'right' message in the thread.

Arno R

 

[John W. Vinson]

Please don't abandon me.... I still have no solution to my problem.

I have listed the missed out fields on 23/8. Maybe you didn't read it since
it was above the other two response.

sorry... I've been tied up; will replay again later today.

John W. Vinson [MVP]

 

[John W. Vinson]

I am so sorry, I forgot to insert some important fields in tblFeePayment:
Description, MonthYear(mmm/yy of fees paid for), ReceiptNumber, StudentID,
Amount.

Ouch. Is MonthYear a Text field like "Apr/07"?

Yes, the payment can cover more than one month. Some parents pay for 3
months one shot.

What would be stored in MonthYear in tblFeePayment in such a case?

If the students cannot come for one whole month but want to reserve the time
slot, then they will pay 1/2 monthly fee. Other than that, the fees are
always collected in full.

What would be stored in tblFeePayment in such a case?

I don't have anything on that. Other than the fees, the other two most
common payments are, Registration Fee ($20, one time payment) and refundable
deposit, which is always equivalent to the fee. I will see what i can do.
Thank for reminding me.

What would be stored in MonthYear in *this* case?

Yes, I forgot to include the fields. ReceiptNumber is the foreign key in
tblFeePayment.

So each fee payment gets a separate receipt? Surely the relationship should be
the other direction: if someone pays three students for three months in
advance, wouldn't they want just one receipt with nine detail rows?

John W. Vinson [MVP]

 

[Guest]

I am so sorry, I forgot to insert some important fields in tblFeePayment:

Ouch. Is MonthYear a Text field like "Apr/07"? Yes.

What would be stored in MonthYear in tblFeePayment in such a case?

If they pay for Jan, Feb and Mar, then 3 payments will be reflected in the
tblFeePayment.
Each line of course will have MonthYear: Jan/07, Feb/07 and Mar/07
respectively.

What would be stored in tblFeePayment in such a case?

Still have not figure out how to do this yet. I have a tblHalfMonth, or
should it be a qryHalfMonth? Am thinking of a calculate field, when a half
month payment button is pressed on frmPayment, the calculation will be 1/2 of
the full fee and reflected at the amount. Have not gone to there yet. One
step at a time.... Struggling to stay afloat here.

What would be stored in MonthYear in *this* case?

I will just leave the MonthYear column empty since it is a one time payment
when they first register or upgrade to another level.

So each fee payment gets a separate receipt? Surely the relationship should be
the other direction: if someone pays three students for three months in
advance, wouldn't they want just one receipt with nine detail rows?

fldReceiptNumber is primary key in tblReceipt. It is a one to many
relationship to fldReceiptNumber in tblFeePayment. Am I doint this right?
Trying very hard to think straight.

Thanks for your time and help, I am not a very clear minded person, obviously.
Mew

 

[Guest]

Is it possible to generate a report that will list out all students' courses
at the beginning of every month. Then the report will leave out students
when month reaches the Termination month? or else it will continue.
Then I can enter AUG/07 for my parameter query within the above criteria.
Please help me.