I did SQL 2000 for a couple of years, stuff like this. Couple of questions:

1) Is this actually a SQL database or is it an access database? Access doesn't support SQL very well.

2) If it's SQL, could you post the tables in question with some dummy data?

If it's access I'm afraid I can't help you. When I was hard coding in access for a business I worked for after WSU, I actually got an error once that stated "This SQL function not supported in Access." Hah. It was a right outer join or other specific join, IIRC.