Normalization/Master Detail Question

I have a table of Account Executives (salespeople):

AccountExecutives:
Identifier                Longint
CommissionRate    Float
Name, etc...

Now, I find out that commission rates can _change_ over time. I've faced
this problem before, so I change the structure to:

AccountExecutives:
Identifier                Longint
Name, etc...

AECommissionRates:
AccountExecutiveFK    Longint
StartingDate                DateTime
EndingDate                 DateTime
CommissionRate        Float

How, in a single SQL statement, do I retrieve, for any particular date (not
just "Today"!!!) the result set:

AccountExecutives.Identifier
AECommissionRates.CommissionRate

* Assume that the input logic will prohibit invalid date ranges (no
overlapping, no gaps, only one per AE with an open last date)
* I can change the structure of either table if necessary.

TIA,
Bob Kaufman

Please cc: your replies to me at gutterboy_no_s...@mindless.com, without the
"_no_spam". Thank you!