SQL commands on queries

[robinson via AccessMonster.com]

If a family has more than one child studying, then the second and third child
would have a discount of 5%. The discounted fees is for the younger children.
This means Choo SWoo would pay the full fees while Cho SMin and Cho SWon
would only pay the discounted fees (i.e. only 95% of the Tuition Fees). The
discount would be based on the Date of Birth (DOB).

StudentName DOB Class FathersName Fees
Cho SWoo 12/5/1995 Year 7 Yong Joon RM3,000.00
Cho SMin 15/4/1997 Year 5 Yong Joon RM2,000.00
Cho SWon 29/4/1999 Year 4 Yong Joon RM2,000.00

Please make modification to the SQL commands below so that I could obtain the
desired results.

SELECT tblStudent.StudentName, tblStudent.DOB, tblStudent.Class, tblStudent.
FathersName, tblStudent.Fees, [Fees]*IIf(DCount("*","Student ",
"FathersName=""" & [FathersName] & """ AND StudentName <""" & [StudentName] &
"""")=0,1,0.95)
AS Discounted
FROM Student;

Please help. Thank you.
Robinson

 

[Chris O'C via AccessMonster.com]

You need to compare the dates of birth of all those who have the same father,
not the student names to see which comes first when alphabetized.

SELECT StudentName, DOB, Class, FathersName, Fees,
IIF(DOB = DMIN("DOB", "tblStudent", "FathersName = '" & FathersName & "'"),
Fees, Fees * 0.95) AS Discounted
FROM tblStudent;

That said, you're going to have trouble with your table structure because the
data isn't normalized. First and last names should be in separate columns
and father's first and last name isn't enough to uniquely identify fathers.
People can have the same name.

Chris

If a family has more than one child studying, then the second and third child
would have a discount of 5%. The discounted fees is for the younger children.
This means Choo SWoo would pay the full fees while Cho SMin and Cho SWon
would only pay the discounted fees (i.e. only 95% of the Tuition Fees). The
discount would be based on the Date of Birth (DOB).

StudentName DOB Class FathersName Fees
Cho SWoo 12/5/1995 Year 7 Yong Joon RM3,000.00
Cho SMin 15/4/1997 Year 5 Yong Joon RM2,000.00
Cho SWon 29/4/1999 Year 4 Yong Joon RM2,000.00

Please make modification to the SQL commands below so that I could obtain the
desired results.

SELECT tblStudent.StudentName, tblStudent.DOB, tblStudent.Class, tblStudent.
FathersName, tblStudent.Fees, [Fees]*IIf(DCount("*","Student ",
"FathersName=""" & [FathersName] & """ AND StudentName <""" & [StudentName] &
"""")=0,1,0.95)
AS Discounted
FROM Student;

Please help. Thank you.
Robinson

 

[Ms. S]

Hello Chris, my name is Ms. S and I am sseking help as well. I saw in your
posting that your resonse time was quick & thorough. Can I post my question
to you? Or if you can access the other postings, mine is under "Query to
look at certai fields". I am trying to get this completed today. Thanks in
advance.

You need to compare the dates of birth of all those who have the same father,
not the student names to see which comes first when alphabetized.

SELECT StudentName, DOB, Class, FathersName, Fees,
IIF(DOB = DMIN("DOB", "tblStudent", "FathersName = '" & FathersName & "'"),
Fees, Fees * 0.95) AS Discounted
FROM tblStudent;

That said, you're going to have trouble with your table structure because the
data isn't normalized. First and last names should be in separate columns
and father's first and last name isn't enough to uniquely identify fathers.
People can have the same name.

Chris

If a family has more than one child studying, then the second and third child
would have a discount of 5%. The discounted fees is for the younger children.
This means Choo SWoo would pay the full fees while Cho SMin and Cho SWon
would only pay the discounted fees (i.e. only 95% of the Tuition Fees). The
discount would be based on the Date of Birth (DOB).

StudentName DOB Class FathersName Fees
Cho SWoo 12/5/1995 Year 7 Yong Joon RM3,000.00
Cho SMin 15/4/1997 Year 5 Yong Joon RM2,000.00
Cho SWon 29/4/1999 Year 4 Yong Joon RM2,000.00

Please make modification to the SQL commands below so that I could obtain the
desired results.

SELECT tblStudent.StudentName, tblStudent.DOB, tblStudent.Class, tblStudent.
FathersName, tblStudent.Fees, [Fees]*IIf(DCount("*","Student ",
"FathersName=""" & [FathersName] & """ AND StudentName <""" & [StudentName] &
"""")=0,1,0.95)
AS Discounted
FROM Student;

Please help. Thank you.
Robinson

 

[robinson via AccessMonster.com]

Dear Chris O'C,
Thanks a lot. Your prompt and kind assistance is much appreciated.
Appropriate coding.
I would place the first and last names in separate columns and give another
unique
key.
Thanks once again.
Robinson

You need to compare the dates of birth of all those who have the same father,
not the student names to see which comes first when alphabetized.

SELECT StudentName, DOB, Class, FathersName, Fees,
IIF(DOB = DMIN("DOB", "tblStudent", "FathersName = '" & FathersName & "'"),
Fees, Fees * 0.95) AS Discounted
FROM tblStudent;

That said, you're going to have trouble with your table structure because the
data isn't normalized. First and last names should be in separate columns
and father's first and last name isn't enough to uniquely identify fathers.
People can have the same name.

Chris

If a family has more than one child studying, then the second and third child
would have a discount of 5%. The discounted fees is for the younger children.

[quoted text clipped - 19 lines]

Please help. Thank you.
Robinson