Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Challenge Question

Posted on 2002-03-17
Medium Priority
168 Views
Hi Friends,
I have three tables.

FeeDetails:

classcode
term1amt
term2amt
term3amt
term1duedate
term2duedate
term3duedate

Students:

studentno
name
concession

Receipts:

receiptno
studno
termno
amount
rtdate

I want a query which shows all the students who have not payed their amount till the term1duedate based on ther termno.

Some students may have concession. The Query should calculate amount for a student who have the concession and that should check with total amount payed by that student(receipts) till date in that term(1/2/3) is less than the actual amount to be payed in that term(from feedetails).

Pls give me the query.
Any suggession is greatly appreciated.
Thanking you.
0
Question by:mrajanikrishna
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

Expert Comment

ID: 6874661
I don't see a clear connection between the Receipts table and the FeeDetails table.  FeeDetails.ClassCode doesn't seem to connect to either of the 2 tables.
Also, howmany receipts per TermNo will one StudNo have?  1 or many?  I am wondering if per student, the receipts need to be summed, or if one record is enough.
0

Accepted Solution

swh062500 earned 150 total points
ID: 6874687
I think this is close to what you want.  I'm not sure about how FeeDetails.ClassCode is being used.

The code below is using a LEFT in case there are no receipts for a particular student.  You will need to

select s.studentNo, s.Name
from (students as S left join receipts as R
on s.studentNo = r.studno) , FeeDetails as FD

HAVING sum(amount) + concession <=  FD.Term1Amt
WHERE r.termno = 1
AND r.rtdate <  FD.Term1DueDate
AND FD.ClassCode = ???
GROUP BY s.studentNo, R.termo
0

Author Comment

ID: 6876591
Yes, I need the same query

Thanks
0

Author Comment

ID: 6896047
Hi SWH,

I am not getting the O/P. Error is   Join not supported.

What is the problem.

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
###### Suggested Courses
Course of the Month10 days, 12 hours left to enroll