Solved

Challenge Question

Posted on 2002-03-17
4
160 Views
Last Modified: 2010-05-02
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
Comment
Question by:mrajanikrishna
  • 2
  • 2
4 Comments
 

Expert Comment

by:swh062500
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

by:
swh062500 earned 50 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

by:mrajanikrishna
ID: 6876591
Yes, I need the same query

Thanks
0
 

Author Comment

by:mrajanikrishna
ID: 6896047
Hi SWH,

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

What is the problem.  


Give  me reply.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now