Joining in ms access

Question

I have 3 tables

1. Payment_Schedule (Event_ID, Event_Type, Event_Incharge, Event_Date)
2. Product_AMC (AMC_ID, Customer_ID)
3. Item_Order (Order_ID, Customer_ID)

For a record in Payment_Schedule, Event_ID is either AMC_ID or Order_ID. If Event_ID is a AMC_ID then Event_Type will be "AMC" and if it is a Order_ID the Event_Type will be "Order".

Now my problem is I don't know how to get the Customer_ID along with the all fields of Payment_Schedule.

I'm using MS Access 2003. Please Help.

Answer 1

Show all customers, and all their payment_schedules

select PS.Event_ID, PS.Event_Type, PS.Event_Incharge, PS.Event_Date, PA.Customer_ID
from (Product_AMC PA
inner join Payment_Schedule PS on (PS.Event_Type='AMC' and PS.Event_ID=PA.AMC_ID))
union all
select PS.Event_ID, PS.Event_Type, PS.Event_Incharge, PS.Event_Date, IO.Customer_ID
from (Item_Order IO
inner join Payment_Schedule PS on (PS.Event_Type='Order' and PS.Event_ID=IO.Order_ID))
order by Customer_ID

For a single customer, say 'ABC'

select PS.Event_ID, PS.Event_Type, PS.Event_Incharge, PS.Event_Date, PA.Customer_ID
from (Product_AMC PA
inner join Payment_Schedule PS on (PS.Event_Type='AMC' and PS.Event_ID=PA.AMC_ID))
where PA.Customer_ID = 'ABC'
union all
select PS.Event_ID, PS.Event_Type, PS.Event_Incharge, PS.Event_Date, IO.Customer_ID
from (Item_Order IO
inner join Payment_Schedule PS on (PS.Event_Type='Order' and PS.Event_ID=IO.Order_ID))
where IO.Customer_ID = 'ABC'