Forum Discussion
Hello guys
I have a problem with creating a specific query.
You have three tables: one for student information including name, class (ranging from 1 to 5), and nationality (either your a national or a foreigner). The second table shows the tuition fees and registration fee for nationals across the five classes, and the third table does the same for foreigners.
I would like someone to help me on how I can go about with this on accesss, so as to retrieve the appropriate fee information based on a student's nationality and class, whether they are a national or a foreigner. So, if you input "national class 3," Access should display the fee for a national student in class 3, including registration and fee. Similarly, if you input "foreigner class 4," Access should show the fees for a foreign student in class 4. Thanks for the help in advance
- | Class | Tuition_Fee | Registration_Fee |
|---------|---------------|--------------------|
| 1 | 1000 | 50 |
| 2 | 1200 | 60 |
| 3 | 1400 | 70 |
| 4 | 1600 | 80 |
| 5 | 1800 | 90 |
Fee for nationals
- Do you Need sql to retrieve data?
If so,please share some data to work with and your expected result.- | Name | Class | Nationality |
|--------|---------|---------------|
| John | 1 | National |
| Emily | 2 | Foreigner |
| David | 3 | National |
| Maria | 4 | Foreigner |
| Ahmed | 5 | National |source table:
Name Class Nationality
John 1 National Emily 2 Foreigner Class Tuition_Fee Registration_Fee
1 1000 50 2 1200 60 result with join:
Name Class Nationality Tuition_Fee Registration_Fee
John 1 National 1000 50 Emily 2 Foreigner 1200 60 Class is 1:
Name Class Nationality Tuition_Fee Registration_Fee
John 1 National 1000 50 drop table stu;
create table stu (Name,Class,Nationality);
insert into stu values("John",1,"National");
insert into stu values("Emily",2,"Foreigner");
select * from stu;
drop table if exists tuition;
create table tuition (Class,Tuition_Fee long,Registration_Fee long);
insert into tuition values(1,1000,50);
insert into tuition values(2,1200,60);
select * from tuition;
select * from stu join tuition on stu.Class= tuition.Class;
//, if you input "national class 3," Access should display the fee for a national student in class 3, including registration and fee.
select * from stu join tuition on stu.Class= tuition.Class where stu.Class=1;
cli_dump_find;
- | Class | Tuition_Fee | Registration_Fee |
|---------|---------------|--------------------|
| 1 | 1000 | 50 |
| 2 | 1200 | 60 |
| 3 | 1400 | 70 |
| 4 | 1600 | 80 |
| 5 | 1800 | 90 |
Fee for nationals - This is the data for the foreigners
Class is 1 to 5 also
The registration is from 100 to 140. ( in sequence of plus 10) and lastly tuition fee 2000 to 2800.,( sequence of plus 200) for the classes.
Sorry if I am sending this one like this.
This is list I am working with as a starter. I appreciate your reply
- Is this a school project?
- Yes. It's actually a school project for registering of students. Both nationals and foreigners.
