Suppose that I have 3 tables:

A) Table UsrHeader
   -----------------
   UsrID  |  UsrName
   -----------------
   1      |  Abc
   2      |  Bcd

B) Table UsrDetail
   -------------------------------
   UsrID  |  UsrLoc  | Date
   -------------------------------
   1      |  LocA    | 10 Aug 2012
   1      |  LocB    | 15 Aug 2012
   2      |  LocA    | 10 Aug 2012

C) Table Trx
   -----------------------------
   TrxID  |  TrxDate     | UsrID
   -----------------------------
   1      |  10 Aug 2012 | 1
   2      |  16 Aug 2012 | 1
   3      |  11 Aug 2012 | 2

What I want to do is to show the table like:

  ---------------------------------------
  TrxID   |  TrxDate     | UsrID | UsrLoc
  ---------------------------------------
  1       |  10 Aug 2012 | 1     | LocA
  2       |  16 Aug 2012 | 1     | LocB
  3       |  11 Aug 2012 | 2     | LocA

Notice that there is one user but different location. That's based on the UsrDetail table that the user on a specified date has moved to another location. So, it should be show the user specific location on that date on every transaction.

I have try this code but it is no luck:

SELECT trx.TrxID, trx.TrxDate, trx.UsrID, User.UsrName, User.UsrLoc
FROM trx
INNER JOIN
( SELECT UsrHeader.UsrID, UsrHeader.UsrName, UserDetail.UsrLoc
  FROM UsrHeader
  INNER JOIN
  ( SELECT UsrDetail.UsrID, UsrDetail.UsrLoc, UsrDetail.Date
    FROM UsrDetail
  ) AS UserDetail ON UserDetail.UsrID = UsrHeader.UsrID
) AS User ON User.UsrID = trx.UsrID
             AND trx.TrxDate >= User.Date

How to do that? Thanks..