SOLVED

# Hello guys

Copper Contributor

# 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

9 Replies

# Re: Hello guys

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

# Re: Hello guys

Is this a school project?

# Re: Hello guys

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

# Re: Hello guys

| Name | Class | Nationality |
|--------|---------|---------------|
| John | 1 | National |
| Emily | 2 | Foreigner |
| David | 3 | National |
| Maria | 4 | Foreigner |
| Ahmed | 5 | National |
best response confirmed by akame01233 (Copper Contributor)
Solution

# Re: Hello guys

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

Fee for nationals

# Re: Hello guys

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

# Re: Hello guys

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;

# Re: Hello guys

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: Hello guys

re:select which fee to make

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

Accepted Solutions
best response confirmed by akame01233 (Copper Contributor)
Solution

# Re: Hello guys

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

Fee for nationals