0
komentar
"How to Make Inner Join In 3 tables with SQL Language in Ms.Access 2003"
1. Open your Ms.Access and create database: Petshop.mdb
2. Create the 1st table with SQL language in SQL Query: Pet
Select Query below The Tables
Double click and right click then select SQL VIEW
3. Then You can write the SQL language to make the pet table
GENERAL STATEMENT :
Create Table table_name
(Field1 Type_data(size)Index,
Field2 Type_data(size),
Fieldn Type_datan(size))
Create Table Pet
(Pet_code text(5)Primary Key,
name text(30),
price number)
4. Fill the Table with this
GENERAL STATEMENT :
INSERT INTO table_name
VALUES(F1D1,F2D1,F3D1,FnDn)
*Nb: F1 = field1
D1 = data1
*Number Of query values and destination Must be same
INSERT INTO PET
VALUES("KCPER","Kucing Anggora",100000)
Then click Yes.
Repeat for the 2nd record
5. Create the 2nd table : Transaction
GENERAL STATEMENT :
Create Table table_name
(Field1 Type_data(size)Index,
Field2 Type_data(size),
Fieldn Type_data(size))
Create Table Transaction
(Transaction_number text(4)Primary Key,
Date date,
pet_code text(5),
Quantity number)
6. Fill the Table with this
GENERAL STATEMENT :
INSERT INTO table_name
VALUES(F1D1,F2D1,F3D1,FnDn)
*Nb: F1 = field1
D1 = data1
*Number Of query values and destination Must be same
INSERT INTO Transaction
VALUES("A001","18/04/2011","KCPER",2)
Then click Yes.
Repeat for the 2nd record
7. Create the 3th table : Costumer
GENERAL STATEMENT :
Create Table table_name
(Field1 Type_data(size)Index,
Field2 Type_data(size))
The SQL Statement Is:
Create Table Costumer
(Costumer_Id text(4)Primary Key,
costumer_name text(30))
8. Then Add Primary Key For Costumer_Id In costumer table
GENERAL STATEMENT :
ALTER TABLE table_name
ADD Primary Key(Field)
The SQL Statement Is:
ALTER TABLE costumer
Add Primary Key(Costumer_Id)
9.Fill the Table with this
GENERAL STATEMENT :
INSERT INTO table_name
VALUES(F1D1,F2D1)
*Nb: F1 = field1
D1 = data1
*Number Of query values and destination Must be same
INSERT INTO Costumer
VALUES("P001","Harris")
Then click Yes.
Repeat for the 2nd record
10. Then add 1 field in table Transaction :
GENERAL STATEMENT :
ALTER TABLE table_name
ADD Field type_data(size)
The SQL Statement Is:
ALTER TABLE transaction
Add Costumer_Id Text(4)
11. Update all the Costumer_Id with ="P001" in transaction table like this
General Statement for Update:
UPDATE Table_name
set field="The Fill"
The SQL Statement Is:
UPDATE Transaction
set Costumer_Id="P001"
12. The Last, You Can Make Query Inner Join 3 tables
Instruction :
a. Total_price = Price*Quantity
b. Terms Of discount
if quantity >=4 then discount=10%*total_Price
if quantity >=2 then discount=5%*total_price
else
discount=0
c. Total_Paid=Total_Price-Discount
The SQL Statement Is
select transaction_number,date,
transaction.costumer_id,
transaction.pet_code,
price,quantity,
(price*quantity)as total_price,
iif(quantity>=4,0.1*total_price,iif(quantity>=2,0.5*total_price,0)) as discount,
(total_price-discount)as total_paid
FROM (transaction INNER JOIN pet ON transaction.pet_code=pet.pet_code) INNER JOIN Costumer ON transaction.Costumer_id=costumer.costumer_id
The Result :
