Combine or Separate Tables to Retrieve data

I am creating a database for our sales management team. We have several locations across the state and would like to be able to assign sales leads from one central data entry point. I have a Lead Table established with all of the detail info regarding that particular lead. Then I have a sales staff table. The problem that I am running into is the sales staff is in different departments. So I could have one lead assigned to a member of the Cabinet Dept, the Lighting Dept and Lumber Dept. But I have all the staff in one table. I seem to have no problem getting the information into the tables, but have not been able to figure out how to retrieve the lead information based on sales member.

For Example:
Lead #1 is assigned to #103 - Cabinets, #210 - Lighting, and #115 Lumber Sales.

What I would like to see is a report that would have the lead information listed by the sales staff:

#103 - Cabinets
Lead #1 - xxxx
Lead #4 - New Home Construction
Lead #25 - Remodel Home

#210 - Lighting
Lead #1 - xxxx
Lead #3 - New Home
Lead #4 - New Home Construction

I had thought about breaking up the staff table to have tables by dept, but then would i be able to run a complete summary of the staff and all the leads that have been assigned to them?
Any ideas or thoughts would be greatly appreciated!

how have you assigned the lead to the multiple staff? Do you have a many to many table that logs the variations? this would make it a lot easier. create a new table with three fields ID, LeadID and StaffID. you can then build a form that allows you add staff to a lead by creating a record of each assignation. you will then be able to do searches on both leads and staff from the same table.

Sorry it has taken so long for me to get back to this. Looking back now I am not sure exactly how I was assigning the leads to the salestaff. I think it was using a simple look up. What I do no is that I think I need some serious help trying to get this thing fixed and finished. Is it possible for the Junction table to put the information into the other tables, and is is possible to have the somewhere the form indicate one person from lighting dept, or.....


Thanks
Nadine