MS Access 2003 Query commands

[robinson]

I have a student table with 4 fields namely StudentName, FatherName, Fees and
DiscountedFees.
A father e.g. Ahmad Ismail in field 2, has three children namely Lamis,
Balqis and Hasnah. The fees for the term is$2000.00. How do I ,through a
Query calculate automatically the Discounted fees for the 2nd and 3 rd child.
Please help. Please give some details on what I should do. Thanks.

Robinson from Malaysia

 

[Duane Hookom]

You could use a subquery in the query to determine the number of the child
within the family and if it is greater than 1, apply the discount. You would
need to specify how you determine which child is 1st, 2nd, and 3rd. Is this
alphabetical?

 

[robinson via AccessMonster.com]

You could use a subquery in the query to determine the number of the child
within the family and if it is greater than 1, apply the discount. You would
need to specify how you determine which child is 1st, 2nd, and 3rd. Is this
alphabetical?

I have a student table with 4 fields namely StudentName, FatherName, Fees and
DiscountedFees.

[quoted text clipped - 4 lines]

Robinson from Malaysia

Dear Mr Duane Hookam,
I wish to thank you very much for your reply. Could I furnish more details.
The second and third
child could be determined alphabetically. I am attaching a sample table for
your reference.
StudentName Class FatherName Fees DiscountedFees
Harish Moorthy Year 4 Moorthy RM2,020.00
Sruti Moorthy Year 5 Moorthy RM2,620.00
Rishi Moorthy Year 6 Moorthy RM2,700.00

My SQL Query is given below
SELECT StudentName, Class, FatherName, Fees, DiscountedFees
FROM Student
WHERE FatherName >1
THEN DiscountedFees for second and third FatherName=95%*Fees
ORDER BY FatherName;

When I execute multiple errors appear.
Could you please give some help.Robinson

 

[Duane Hookom]

I don't see numeric fields for fees or discounted fees. What is "RM2,202.00"?
--
Duane Hookom
Microsoft Access MVP

You could use a subquery in the query to determine the number of the child
within the family and if it is greater than 1, apply the discount. You would
need to specify how you determine which child is 1st, 2nd, and 3rd. Is this
alphabetical?

I have a student table with 4 fields namely StudentName, FatherName, Fees and
DiscountedFees.

[quoted text clipped - 4 lines]

Robinson from Malaysia

Dear Mr Duane Hookam,
I wish to thank you very much for your reply. Could I furnish more details.
The second and third
child could be determined alphabetically. I am attaching a sample table for
your reference.
StudentName Class FatherName Fees DiscountedFees
Harish Moorthy Year 4 Moorthy RM2,020.00
Sruti Moorthy Year 5 Moorthy RM2,620.00
Rishi Moorthy Year 6 Moorthy RM2,700.00

My SQL Query is given below
SELECT StudentName, Class, FatherName, Fees, DiscountedFees
FROM Student
WHERE FatherName >1
THEN DiscountedFees for second and third FatherName=95%*Fees
ORDER BY FatherName;

When I execute multiple errors appear.
Could you please give some help.Robinson

 

[John W. Vinson]

I don't see numeric fields for fees or discounted fees. What is "RM2,202.00"?

I'm guessing that it's a Currency field and that RM is the currency symbol...
is that right, Robinson??

 

[robinson via AccessMonster.com]

I'm guessing that it's a Currency field and that RM is the currency symbol...
is that right, Robinson??

yes, sir.
RM is the currency symbol for Malaysia.
Thanks
Robinson

 

[Duane Hookom]

If I understand, you could use a query with SQL like:
SELECT tblRobinson.StudentName, tblRobinson.Class, tblRobinson.FatherName,
tblRobinson.Fees, [Fees]*IIf(DCount("*","tblRobinson","FatherName=""" &
[FatherName] & """ AND StudentName <""" & [studentName] & """")=0,1,0.95) AS
Discounted
FROM tblRobinson;

 

[robinson via AccessMonster.com]

Dear Duane Hookom,
Thank you very much.
Your SQL commands have worked like magic. Amazing indeed.
One more thing. How do I format the Discounted in Currency by auto generating
RM (Currency symbol) for Malaysia. In a table I could format the cells but
when it in a query output I can't.
Thanks and Cheerio!
Robinson

If I understand, you could use a query with SQL like:
SELECT tblRobinson.StudentName, tblRobinson.Class, tblRobinson.FatherName,
tblRobinson.Fees, [Fees]*IIf(DCount("*","tblRobinson","FatherName=""" &
[FatherName] & """ AND StudentName <""" & [studentName] & """")=0,1,0.95) AS
Discounted
FROM tblRobinson;
[quoted text clipped - 5 lines]

Thanks
Robinson

 

[Duane Hookom]

Use the format property of the text box in the form or report where
displaying the results.

--
Duane Hookom
Microsoft Access MVP

Dear Duane Hookom,
Thank you very much.
Your SQL commands have worked like magic. Amazing indeed.
One more thing. How do I format the Discounted in Currency by auto generating
RM (Currency symbol) for Malaysia. In a table I could format the cells but
when it in a query output I can't.
Thanks and Cheerio!
Robinson

If I understand, you could use a query with SQL like:
SELECT tblRobinson.StudentName, tblRobinson.Class, tblRobinson.FatherName,
tblRobinson.Fees, [Fees]*IIf(DCount("*","tblRobinson","FatherName=""" &
[FatherName] & """ AND StudentName <""" & [studentName] & """")=0,1,0.95) AS
Discounted
FROM tblRobinson;

I don't see numeric fields for fees or discounted fees. What is "RM2,202.00"?

[quoted text clipped - 5 lines]

Thanks
Robinson

 

[MGFoster]

How do I format the Discounted in Currency by auto generating
RM (Currency symbol) for Malaysia. In a table I could format the cells but
when it in a query output I can't.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If your Access is set up to use Malaysian currency symbols you can use
the Format() function:

Format(Fees]*IIf(DCount("*","tblRobinson","FatherName=""" &
[FatherName] & """ AND StudentName <""" & [studentName] &
"""")=0,1,0.95), "Currency") AS Discounted

The "Currency" indicator will add the "RM" to the discount amount.

If you are using a form to show the query's output set the Discounted
control's Format property to Currency, to get the same results.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSU8uooechKqOuFEgEQJnXQCg+W53gHzFfjhfqD+F53TDB1yPwuEAmwff
vL/I8ubYj+8b6H1ntEHV7lpu
=UVBr
-----END PGP SIGNATURE-----

 

[robinson via AccessMonster.com]

Thanks a lot
Robinson

Use the format property of the text box in the form or report where
displaying the results.

Dear Duane Hookom,
Thank you very much.

[quoted text clipped - 17 lines]

 

[robinson via AccessMonster.com]

Thanks a lot
Robinson

How do I format the Discounted in Currency by auto generating
RM (Currency symbol) for Malaysia. In a table I could format the cells but
when it in a query output I can't.

If your Access is set up to use Malaysian currency symbols you can use
the Format() function:

Format(Fees]*IIf(DCount("*","tblRobinson","FatherName=""" &
[FatherName] & """ AND StudentName <""" & [studentName] &
"""")=0,1,0.95), "Currency") AS Discounted

The "Currency" indicator will add the "RM" to the discount amount.

If you are using a form to show the query's output set the Discounted
control's Format property to Currency, to get the same results.