Hello guys

[akame01233]

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 

[#2]

Do you Need sql to retrieve data?
If so,please share some data to work with and your expected result.

[#3]

Is this a school project?

[#4]

Yes. It's actually a school project for registering of students. Both nationals and foreigners.

[#5]

| Name | Class | Nationality |
|--------|---------|---------------|
| John | 1 | National |
| Emily | 2 | Foreigner |
| David | 3 | National |
| Maria | 4 | Foreigner |
| Ahmed | 5 | National |

[#6 · Accepted solution]

| Class | Tuition_Fee | Registration_Fee |
|---------|---------------|--------------------|
| 1 | 1000 | 50 |
| 2 | 1200 | 60 |
| 3 | 1400 | 70 |
| 4 | 1600 | 80 |
| 5 | 1800 | 90 |


Fee for nationals

[#7]

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

[#8]

@akame01233 

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;

https://www.w3schools.com/sql/sql_join.asp

 

[#9]

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.

[#10]

re:select which fee to make

Do you want an input drop down list or output query with parameters?