By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,646 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,646 IT Pros & Developers. It's quick & easy.

Can't figure out how to write QUery and Report

P: n/a
Hi -
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

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]!

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
Any and all help is apprecitated.


Aug 19 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.