0

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.

Vikdor
  • 23,934
  • 10
  • 61
  • 84
Krishanu Dey
  • 6,326
  • 7
  • 51
  • 69

1 Answers1

2

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'
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262