getting information needed for payment query

[Guest]

I am a new access user, currently trying to build a database system for my
musicschool. Has been reading quite some books but none can answer some of
my needs.
I am using access to generate receipts for the students. My students pay
their fee on a monthly basis. I have already build a table for
tblCourses(describing the courses available and relevant level with teachers
assigned to each course), tblStudent(students particulars with studentID),
tblLesson(couses students taking, teacherID, starting date, termination date
etc) and Payment (to issue receipt and update payment).
I need a query that will automatically list students that have not paid
fees for a certain month. The students starting date must be earlier or on
that certain month and the termination date must be later than that certain
month or null. I want to use parameter entry, so that I can use the same
query for different months.
How do I do that, since the value that I need for the starting date and
termination date will be determine by the month that I key in.
Help....

 

[Steve]

From your description I suggest you begin by tweaking the design of your
tables:
TblCourse
CourseID
Coursename

TblCourseLevel
CourseLevelID
CourseID
CourseLevelDescription
CourseLevelChargePerHour

TblTeacher
TeacherID
TeacherFirstName
TeacherLastName

TblCourseLevelTaughtByTeacher
CourseLevelTaughtByTeacherID
CourseLevelID
TeacherID

TblStudent
StudentID
StudentFirstName
StudentLastName
<<Other relevant student fields>>

The above assumes that some or all of your courses have different levels
such as beginner, intermediate and advanced. You did not say anything about
your fee structure so I assumed you charge by the hour and that each course
level has a certain charge, If yiu do something different, you need to
modify TblCourseLevel accordingly. If you post back with how you charge I
will help you incorporate your fee structure into your tables. The above
also assumes you have multiple teachers and that a teacher may teach more
than one course level.

Given all of the above, students register for lessons in a course level.and
take one or more sessions on different dates with a teacher that teaches the
course level.

TblStudentLessonRegistration
StudentLessonRegistrationID
StudentID
CourseLevelID
CourseLevelTaughtByTeacherID
StartDate
TerminationDate

TblStudentLesson
StudentLessonID
StudentLessonRegistrationID
StudentLessonDate

Students pay for studentlessons monthly. The amount due depends on how many
studentlessons the student received duing the month.

TblStudentPayment
StudentPaymentID
StudentLessonRegistrationID (StudentID can be found from
TblStudentLessonRegistration)
PaymentYear
PaymentMonth
PaymentDue
PaymentDate

The number of studentlessons for a month is found by counting the
StudentLessonDates that occurred during the month. The amount due is
calculated by (number of studentlessons) X CourseLevelChargePerHour. You can
use two queries for this.

Include in the first query TblStudentLessonRegistration and
TblStudentLesson. Include the fields:
StudentLessonRegistrationID From TblStudentLessonRegistration
CourseLevelID From TblStudentLessonRegistration
StudentLessonDate From TblStudentLesson
Add a calculated field with this expression:
LessonYear:Year(StudentLessonDate)
Add a calculated field with this expression:
LessonMonth:Month(StudentLessonDate)
Set the criteria for LessonYear as:
[Enter The Year Of The Lessons]
Set the criteria for LessonMonth as:
[Enter The Month Of The Lessons]
Now with the query in design view, click on the Sigma (looks like a capital
E) button in the menu at the top of the screen. This converts your query to
a totals query. Under StudentLessonRegistrationID, change GroupBy to Count.
The query will now count the number of lessons for each
StudentLessonRegistrationID for the year and month you enter. When you run
this query, you will be first prompted for the Year of the lessons and then
you will be prompted for the month of the lessons. For the month, enter a
number between 1 and 12.

Include in the second query the first query and TblCourseLevel. Join
CourseLevelID in TblCourseLevel to CourseLevelID in the first query. Include
the fields:
All the fields from the first query
CourseLevelChargePerHour From TblCourseLevel
Add a calculated field with this expression:
AmountDue:[CourseLevelChargePerHour] X [CountOfStudentLessonRegistrationID]
Now with the query in design view, click on the Query Type button in the
menu at the top of the screen. Change the query to an append query and
append to TblStudentPayment.
1. Append StudentLessonRegistrationID in the query to
StudentLessonRegistrationID in TblStudentPayment
2. Append LessonYear in the query to PaymentYear in TblStudentPayment
3. Append LessonMonth in the query to PaymentMonth in TblStudentPayment
4. Append AmountDue in the query to PaymentDue in TblStudentPayment

On the first of every month you need to make sure all lessons for the
previous month were entered in
TblStudentLesson. Then you run the second query to enter all the payment due
records in TblStudentPayment. Then you need a form for entering payments as
students make payment.

Base your query that will automatically list students that have not paid
fees for a certain month on TblStudentPayment. Set the criteria for
PaymentYear to:
[Enter Payment Year]
Set the criteria for PaymentMonth to:
[Enter Payment Month]
Set the criteria for PaymentDate to:
Is Null

You said you are a new Access user. If you need further help with the above
so you can concentrate on providing music lessons, I can help you for a
reasonable fee. Contact me at my email address below.

Good Luck,

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

 

[John Marshall, MVP]

Stevie, you never learn. These newsgroups are for FREE peer to peer help,
not a venue for you to con unsuspecting users out of their money. You have a
long track record of unethical behaviour and have posted many examples of
incompetent advice. Why would anyone want to hire you?

Just go away and take your phony testimonials with you.

John... Visio MVP

 

[Guest]

Steve, thanks for the advise. I tried to build this database myself for
three reasons, firstly, my budget is really tight. It took me quite a while
to purchase the programme. Secondly, I like to take it as a challenge, I
want to learn and hopefully master this useful tools. Thirdly, I want to be
able to do any alternation to the database in the future as needed by myself.
Thanks again for your offer.

The problem is that students come and go at different time. For example,
for the month of August:
A student register to start his/her lesson on September. I keyed in his/her
particulars to block up the time-slot.
Some students terminated their lessons in July. Some will terminate in
September, again, I key in the information so that the time-slots are
freed-up.
Now, if I run the student late payment query for the month of August, the
new students in September will appear and the students who will terminate in
September will dissapear. Obviously this is faulty.

I tried putting the criteria for fldTerminationDate to (is null) or >date()
and fldCommencementDate to =<date(). This query will run well only for the
current month.
What if some students postpone paying their fees until the following month
and I check the record in September? Then the query will give a faulty
answer.

I need the criteria for fldTerminationDate to be (is null) or >(the month
and year that I typed in as the parameter) and the same for
fldCommencementDate. Is there a way to link all of them together instead of
typing in "Aug" and "07" for so many times?

I don't know whether I make sense to any of you. Quite difficult to put
into words... But I do need help. HELP!!

 

[StopThisAdvertising]

You said you are a new Access user. If you need further help with the above
so you can concentrate on providing music lessons, I can help you for a
reasonable fee. Contact me at my email address below.

Who would *ever* want to hire someone like you ???
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3550+ pageloads, 2325+ first-time visitors (these figures are rapidly increasing)

ArnoR

 

[Steve]

Mew, the root problem is the faulty design of your tables. Have you
considered adopting the table structure I gave you? As you expand your
database, you will continue run up against problems.

Good Luck,

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

 

[Keith Wilby]

Mew, the root problem is the faulty design of your tables. Have you
considered adopting the table structure I gave you? As you expand your
database, you will continue run up against problems.

Good Luck,

Translation: there's no money in this for me so I'm out of here.

 

[Guest]

I think I did not make one important point clear in my fee structure. The
fee is charged at a flat rate per month, regardless of how many lessons the
student attended. Even if the student cannot turn up for a particular
lesson, he/she still pay the same amount.

Anyway, will look up more on macro and parameter entries.

Do help me out if you have a solution.