CODEWITHSUNDEEP
sqlms-access
jyorge maccaline
jyorge maccaline

Reputation: 87

Combine three table using access database

I have three tables:

1. user    => user_id[Pk], user_name
2. payment => payment_id[PK], user_id[FK], amount
3. receive => receive_id[PK], user_id[FK], amount

Now I want to fetch record like:

username, paymentamount,receivableamount

Thanks in advance..

I have read this, but it does not work for me.

Suppose, In user table

user_id   user_name
1             ABC
2             XYZ

In payment table

payment_id   user_id   amount
1                   1            1000
2                   1            200
3                   2           150

In receive table :
receive_id  user_id  amount
1                2            100
2                1            200

Now I want record like

user_name  paymentamount  receivableamount
ABC            1000                    200
ABC            200
XYZ            150                      100

Upvotes: 0

Views: 126

Answers (5)

Jim
Jim

Reputation: 3510

Use the built in query designer. Drag the three tables into it, link up the keys (drag and drop the fields onto each other), double click the fields you want to add them to the field list at the bottom, and then run the query. Choose aggregate expressions if needed.

Upvotes: 1

Mostaniruzzaman
Mostaniruzzaman

Reputation: 1

select t1.user_name, t2.amount payementamount, t3.amount receiveableamount from 
User t1,
payment t2,
receive t3
where
t1.user_id=t2.user_id and
t1.user_id=t3.user_id

You can try this out....

Upvotes: 0

Geordee Naliyath
Geordee Naliyath

Reputation: 1859

You might want to sum up amounts by user_id.

SELECT
    user.user_id,
    SUM(payment.amount) AS payment_amount,
    SUM(receive.amount) AS receive_amount
FROM (receive INNER JOIN [user] ON receive.user_id = user.user_id)
              INNER JOIN payment ON user.user_id = payment.user_id
GROUP BY user.user_id;

Upvotes: 2

dave
dave

Reputation: 12806

select 
    user.user_name, payment.amount, receive.amount 
from user 
    join payment on user.user_id == payment.user_id 
    join receive on user.user_id = receive.user_id;

Upvotes: 0

Vikdor
Vikdor

Reputation: 24124

You may need to adapt this to MSAccess syntax but the idea is to perform a LEFT JOIN of payment and receive tables with user as that is the parent table and the other two tables may or may not have a record for this user.

SELECT 
    u.user_name, 
    ISNULL(p.amount, 0) as paymentamount,
    ISNULL(r.amount, 0) as receivableamount
FROM 
    user u
    LEFT JOIN payment p
    ON u.user_id = p.user_id
    LEFT JOIN receive r
    ON u.user_id = r.user_id

Upvotes: 0

Related Questions