Hoping someone can help with Query approach

[sara]

Hi -

I posted this earlier and didn't get any response. Hopefully someone
can help??

I have a table keeping track of employees' jobs in a location. So,
Store #1 Manager #353, AsstMgr #556
Store #2 Manager #776, AsstMgr #132
etc.

The table is
Date StoreNum MgrNum AsstMgrNum ...
Each time a "line up" is recorded, we put in the new date (as people
come and go, for example)
I am trying to figure out how to write a query to get the "Line Up"
for all the stores.

I want the user to enter a date, and have the query return ONE record
per store, and the date on that ONE record is equal to the date the
user enters on the form, OR the nearest date prior to the date the
user enters.
So, the user could enter "1/1/2008" and get the most recent record
for
each location.
I can't get a query to do this! I have records for store 2 for 9/9/06
and 8/17/07 and I just want the 8/17/07 record and I don't have it.
Probably easy, but I'm stumped. I've tried "Max Records" but that
doesn't return the max by store.
SELECT tblLineUp.LineUpDate, tblLineUp.StoreNum
FROM tblLineUp
WHERE (((tblLineUp.LineUpDate)<=[Forms]![frmLineUpAndPhone]!
[txtLineUpDate]));
THEN I want to print these results on a report (I'll look up the
numbers and get the name). I have NO idea how to do that. What I
want is:
Store 1 Store 2.....
Mgr Joe Ken
Asst Mgr Sue John

Do I need a crosstab, or a crosstab for each "job" (manager,
assistant,
Sporting Goods, Clothing, etc.?) to get the person in the job in each
location?

Any and all help is apprecitated.
Sara