Solved

Challenge Question

Posted on 2002-03-17
4
161 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Use Multiple Forms 4 49
Write data from SQL to CSV file - position 1 53
MS Date Picker 64 bit 32 bit issue 12 49
How to measure sizes and angles in scanned images ? 3 54
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

867 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

22 Experts available now in Live!

Get 1:1 Help Now