Forum Discussion
Solved
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 foreigne...
- | Class | Tuition_Fee | Registration_Fee |
|---------|---------------|--------------------|
| 1 | 1000 | 50 |
| 2 | 1200 | 60 |
| 3 | 1400 | 70 |
| 4 | 1600 | 80 |
| 5 | 1800 | 90 |
Fee for nationals
| Name | Class | Nationality |
|--------|---------|---------------|
| John | 1 | National |
| Emily | 2 | Foreigner |
| David | 3 | National |
| Maria | 4 | Foreigner |
| Ahmed | 5 | National |
|--------|---------|---------------|
| 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;
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;
 
- Thanks for the help. Tried to implement it in access. But it didn't work out. Is there not a way I could use a formula or a conditional statement to select which fee to make use of such as using the IIF function.
- re:select which fee to make
Do you want an input drop down list or output query with parameters?
