Actually what I like to do is --- just like to select the records of the new students entered in Student table, and they don't exist in the Login table...
e.g.... in the Student table.....I've records like...
CourseID ( PK ) StdCourseID ( PK )
CourseFees StudentID ( FK )
CourseID ( FK )
I just want to calculate the field - FeesRemain- in the Fees table by Substracting the FeesPaid - field value of the Fees table from the CourseFees - field value of the Course table for particular student.
I've tried in the access itself, but it's not getting it right.... may be I m not doing it correctly.
I was trying your query to get the value of the Field Fees Remain and realised some gaps/errors in the database. I m not sure whether u'll be able to help me or not. But if you can, then that will be really great.
At the moment my database consists of tempStd, tempApp, Student, Fees, Course & stdCourse tables.
( 1 ). When any student apply for course his details been saved into the
- tempStd table and some into tempApp table.
( 2 ). When his application been sanctioned - his details in tempApp been updated.
( 3 ). So, when this New Student - come to pay the fees - his details from
tempStd - transferred
to the - Student table and at this stage his ( StudentID )
which is just created in Student
table - inserted in the Fees table along with the value of some other fields like
- FeesPaid,
DateFeesPaid, CardType, CardNo, NameAppearOnCard, etc.
( Note : - At the moment I m not doin any calculation for the FeesRemain field.
)
Continue with the ( 3 ), now it selects the CourseID from the
Course table ( depending on
the common filed - CourseTitle - between the Student & the Course table )
& the StudentID & CourseID fields - been inserted into StdCourse Table.
( So, above all working fine at the moment. But I think it will be good to brief you out before I explain the problem. )
The part of the database which I m bit confused or not having sort of idea - how to deal with it is -
Existing student paying the fees.....for example ......the CourseFees is of - £9,000. Just take it as he was New Student, so first time he paid £2,500 - which been inserted into the database as mentioned above...
But now the actual problem is - what if this student come again to pay the rest of the fees....I mean how can I be able to calculate the
FeesRemain field in the Fees table?
( Ofcourse I can handle the stuff like, if existing student paying the fees, then details should be inserted only in the fees table -
FeesPaid, DateFeesPaid, CardType, CardNo, NameAppearOnCard - in these fields )
My only concern is bout calculating the field - FeesRemain....So when existing student paying the 2nd instalment of Fees £2,500, how could it be display
FeesRemain - £4,000 as he paid ( £5,000 = £2,500 + £2,500 ) out of £9,000?
Wat do you recommend me to do? Will it be good to create another table - so one keeps records of FeesPaid, DateFeesPaid, CardType, CardNo, NameAppearOnCard and the other one keeps records of FeesRemain & TotalFeesPaid. So everytime, when Student pays the
fees, it just updates the records over there?
I hope I've explained it very clear. But if there's something which doesn't make sense to u or not clear to u, then plz let me know....
Actually I am quite aware of this problem since your first post. But you didn't explicitly mentioned it so I thought all students have facility to make only 1 part payment option. Ok.. No problem your data structures are right. No alterations required in it.
Check out following query:
SELECT StdCourse.StudentId, StdCourse.CourseID, Sum(Fees.FeesPaid) AS SumOfFeesPaid, Course.CourseFees, ([Course].[CourseFees]-[SumOfFeesPaid]) AS FeeBalance
FROM (StdCourse INNER JOIN Fees ON StdCourse.StudentId = Fees.StudentId) INNER JOIN Course ON StdCourse.CourseID = Course.CourseId
GROUP BY StdCourse.StudentId, StdCourse.CourseID, Course.CourseFees
Regards
Kuldeep Deokule
MCSD.NET
Blog: http://dkuldeep.blogspot.com
This posting is provided "AS IS" with no warranties, and confers no rights.
vishal_0009
Member
680 Points
136 Posts
General Access - Join Query Probelm
May 02, 2006 06:16 PM|LINK
I m developing a data driven web application as part of my study and using Access Database as backend.
I've few tables.. Login, Student and etc....
My Login Table looks like:
LoginID - Autonumber ( Primary Key )
UserName - Text
Password - Text
Email - Text
StudentID - Number
And Student Table looks like:
StudentID - AutoNumber ( Primary Key )
Surname - Text
FirstName - Text
DateOfBirth - Date/Time
Now, when Student pays the fees, his details transferred from TempStudent to Student table.
The problem is that how can I retrieve the Student Id of the new Students to create their Login...Accounts...?
I write the query which is as below:
Select Student.StudentID, Student.Surname, Student.FirstName, Student.DateOfBirth from Student Inner Join Login On (Student.StudentID = Login.StudentID) where [Login.StudentID] <> [Student.StudentID]
But, the above query is not displaying any results...
Just to let u know that StudentID field in the Login table ( Foreign Key of Student Table ) is inserted when creating Login accounts.
If you require n e further info, then plz let me know...
Many thnx,
vish.
deokule2003
Participant
1786 Points
356 Posts
Re: General Access - Join Query Probelm
May 03, 2006 10:50 AM|LINK
If I understood your problem clearly. Just remove the WHERE part in the query. It will work.
Regards
Kuldeep Deokule
Blog: http://dkuldeep.blogspot.com
This posting is provided "AS IS" with no warranties, and confers no rights.
vishal_0009
Member
680 Points
136 Posts
Re: General Access - Join Query Probelm
May 03, 2006 12:47 PM|LINK
Actually what I like to do is --- just like to select the records of the new students entered in Student table, and they don't exist in the Login table...
e.g.... in the Student table.....I've records like...
StudentID (PK) FirstName Surname DateOFBirth
1 XYZ ASF 01/01/1975
2 ASD PQR 15/05/1980
And in the Login table...
LoginID (PK) UserName Password Email StudentID (FK)
1 XYAS XY1 xy@hotmail.com 1
So, it retrieves the record - with Student ID - 2 fromt the Student table.
I've amend the query which is as follow:
"Select Distinct Student.StudentID, Student.Surname, Student.FirstName, Student.DateOfBirth from Student, Login Where [Student.Student_ID] <> [Login.Student_ID]"
It's working at the moment as per expectation and showing me the correct result.....
So, like to say thnx very much for your help.
Many thnx,
Vish.
deokule2003
Participant
1786 Points
356 Posts
Re: General Access - Join Query Probelm
May 03, 2006 01:43 PM|LINK
Hello,
Here is your required SQL:
SELECT Student.* FROM Student LEFT JOIN Login ON Student.StudentID = Login.StudentID WHERE Login.StudentID Is Null
Regards
Kuldeep Deokule
Blog: http://dkuldeep.blogspot.com
This posting is provided "AS IS" with no warranties, and confers no rights.
vishal_0009
Member
680 Points
136 Posts
Re: General Access - Join Query Probelm
May 03, 2006 02:37 PM|LINK
I've another problem in access - to calculate the filed depending on the value of the field in other table...
for example....have tables like Fees, Course, Student and StdCourse...
Fees - Table Student - Table
FeesID ( PK ) StudentID ( PK )
FeesPaid FirstName
FeesRemain Surname
StudentID ( FK ) CourseApplied
Course - Table StdCourse - Table
CourseID ( PK ) StdCourseID ( PK )
CourseFees StudentID ( FK )
CourseID ( FK )
I just want to calculate the field - FeesRemain - in the Fees table by Substracting the FeesPaid - field value of the Fees table from the CourseFees - field value of the Course table for particular student.
I've tried in the access itself, but it's not getting it right.... may be I m not doing it correctly.
Vish.
deokule2003
Participant
1786 Points
356 Posts
Re: General Access - Join Query Probelm
May 03, 2006 07:23 PM|LINK
Hello,
What is a benefit of taking separate table for "StdCourse". You can easily incorporate "CourseID" field in Fees table.
Regards
Kuldeep Deokule
Blog: http://dkuldeep.blogspot.com
This posting is provided "AS IS" with no warranties, and confers no rights.
vishal_0009
Member
680 Points
136 Posts
Re: General Access - Join Query Probelm
May 03, 2006 08:08 PM|LINK
Regards,
Vish.
deokule2003
Participant
1786 Points
356 Posts
Re: General Access - Join Query Probelm
May 04, 2006 05:35 PM|LINK
Just check out following query:
SELECT StdCourse.StudentId, StdCourse.CourseID, Fees.FeesPaid, [CourseFees]-[FeesPaid] AS FeeBalance FROM (StdCourse INNER JOIN Fees ON StdCourse.StudentId = Fees.StudentId) INNER JOIN Course ON StdCourse.CourseID = Course.CourseId
Regards
Kuldeep Deokule
Blog: http://dkuldeep.blogspot.com
This posting is provided "AS IS" with no warranties, and confers no rights.
vishal_0009
Member
680 Points
136 Posts
Re: General Access - Join Query Probelm
May 05, 2006 10:50 PM|LINK
I was trying your query to get the value of the Field Fees Remain and realised some gaps/errors in the database. I m not sure whether u'll be able to help me or not. But if you can, then that will be really great.
At the moment my database consists of tempStd, tempApp, Student, Fees, Course & stdCourse tables.
( 1 ). When any student apply for course his details been saved into the - tempStd table and some into tempApp table.
( 2 ). When his application been sanctioned - his details in tempApp been updated.
( 3 ). So, when this New Student - come to pay the fees - his details from tempStd - transferred
to the - Student table and at this stage his ( StudentID ) which is just created in Student
table - inserted in the Fees table along with the value of some other fields like - FeesPaid,
DateFeesPaid, CardType, CardNo, NameAppearOnCard, etc.
( Note : - At the moment I m not doin any calculation for the FeesRemain field. )
Continue with the ( 3 ), now it selects the CourseID from the Course table ( depending on
the common filed - CourseTitle - between the Student & the Course table ) & the
StudentID & CourseID fields - been inserted into StdCourse Table.
( So, above all working fine at the moment. But I think it will be good to brief you out before I explain the problem. )
The part of the database which I m bit confused or not having sort of idea - how to deal with it is - Existing student paying the fees.....for example ......the CourseFees is of - £9,000. Just take it as he was New Student, so first time he paid £2,500 - which been inserted into the database as mentioned above...
But now the actual problem is - what if this student come again to pay the rest of the fees....I mean how can I be able to calculate the FeesRemain field in the Fees table?
( Ofcourse I can handle the stuff like, if existing student paying the fees, then details should be inserted only in the fees table - FeesPaid, DateFeesPaid, CardType, CardNo, NameAppearOnCard - in these fields )
My only concern is bout calculating the field - FeesRemain....So when existing student paying the 2nd instalment of Fees £2,500, how could it be display FeesRemain - £4,000 as he paid ( £5,000 = £2,500 + £2,500 ) out of £9,000?
Wat do you recommend me to do? Will it be good to create another table - so one keeps records of FeesPaid, DateFeesPaid, CardType, CardNo, NameAppearOnCard and the other one keeps records of FeesRemain & TotalFeesPaid. So everytime, when Student pays the fees, it just updates the records over there?
I hope I've explained it very clear. But if there's something which doesn't make sense to u or not clear to u, then plz let me know....
I m really looking forward to hear from you.....
many thnx,
vishal.
deokule2003
Participant
1786 Points
356 Posts
Re: General Access - Join Query Probelm
May 06, 2006 06:22 PM|LINK
Actually I am quite aware of this problem since your first post. But you didn't explicitly mentioned it so I thought all students have facility to make only 1 part payment option. Ok.. No problem your data structures are right. No alterations required in it.
Check out following query:
SELECT StdCourse.StudentId, StdCourse.CourseID, Sum(Fees.FeesPaid) AS SumOfFeesPaid, Course.CourseFees, ([Course].[CourseFees]-[SumOfFeesPaid]) AS FeeBalance
FROM (StdCourse INNER JOIN Fees ON StdCourse.StudentId = Fees.StudentId) INNER JOIN Course ON StdCourse.CourseID = Course.CourseId
GROUP BY StdCourse.StudentId, StdCourse.CourseID, Course.CourseFees
Regards
Kuldeep Deokule
Blog: http://dkuldeep.blogspot.com
This posting is provided "AS IS" with no warranties, and confers no rights.