i m trying to solve a problem for last couple of days and would appreciate any help...

i have a transction table...in which i record transactions from one location to another location...

example

TransID FromLocationID ToLocationID

there are other fields too but i m mentioning this important one.

There are two types of Locations one is hospital and the other is warehouse. Details of each is kept in their respective tables..

The FRomLocationID, ToLocationID comes from the following table

LocationID, LocationType (hospital or warehouse) ,ActualID

in the above i would assign a unique LocationID but the ActualID would either come from the hospital table or from the warehouse table...The problem is that how can i join it from the transaction table..like if the location if of type hospital then i get the value from hospital table..and vice versa...I have to create a view for all this..also i have to create reports..

thanx