Inner Join SQL Question

[NotSoWise_Sage]

Okay, I'll be the first to admit, I'm not Access expert, less so a SQL expert. In fact, I mostly stick to my bailiwick of Excel - however I've been given the task of redesigning from the ground up, an old database that has been a major part of our reporting.

A bit of background, since there may be other questions: We've recently had teams merged, the clashing experiences, skill sets, and management styles, have brought to light some pretty heavy questions over the reports, and reporting processes.

Previously, another team has had an Access database, which basically was just linked tables to a SQL server database, and a set of queries that run from with in it. This would be fine, but the accurracy of this Access database is heavily called into question, the SQL server database is not owned, or controlled by anyone in our department, never mind team - and as it transpires, it's some tagged on tables to a database that belongs to the call centre team (we're telesales - doesn't sound much different, but it's significant politically, and data wise).

I have been tasked with taking the data from this SQL server, and storing it in a database I've to create, revise the whole process so we upload into our database, and not the contact centre one, build in new areas needed for reporting, rather than relying on dozens of cut and paste jobs in excel to produce reports.

It's a big task, and I've got to do it.

The actual problem: Having given you the background, I'll jump straight into the problem. I'm afraid this may sound silly, or simple - but you can't know everything, and my Google-fu has failed me.

I'm trying to do an INNER JOIN as part of a query. Unfortunately, the only data I have to match on is a username, which can be duplicated. This is from a sales table (which is imported from other systems and can not be changed), and a staff table we keep populated. The staff table does have a unique ID, which we call RP_ID, it's new, but we've had to create it to get round duplicates. What I want to do is stamp the sales table, automatically on upload, and going back historically with the RP_ID. To establish what the RP_ID is, as I mentioned we would match on the username, however the duplicates arise due to team changes.

In the staff table, I have the date joined, and date left team.

In the sales table, I have the sale date.

What I want to do is create a query that brings back the RP_ID, using the adviser name, where the sale date is greater than staff joined, but less than the staff left date.

At the moment I have:

SELECT MAXDD.ID, MAXDD.UID, MAXDD.saleDate, MAXDD.advisor, Staff2.RP_ID, Staff2.maxName, Staff2.startDate, Staff2.leaveDate
FROM Staff2 INNER JOIN MAXDD ON Staff2.maxName = MAXDD.advisor;

Any help/advice would be appreciated - my description may simple in an overcomplicated way, I'm willing to try and clarify, if someone can point me in the right direction of what they need.

 

[xenou]

Is it possible you only need to add the filter criteria? It's seems you need to do some cleanup on the names/IDS which is underway.

HTH

SELECT MAXDD.ID, MAXDD.UID, MAXDD.saleDate, MAXDD.advisor, Staff2.RP_ID, Staff2.maxName, Staff2.startDate, Staff2.leaveDate
FROM Staff2 INNER JOIN MAXDD ON Staff2.maxName = MAXDD.advisor
[B][COLOR="Red"]WHERE MAXDD.saleDate > Staff2.startDate AND MaxDD.saleDate < Staff2.leaveDate[/COLOR][/B];