2 table query with multiple joins

[Ron_Mowry]

I am having problems trying to figure out how to create the following query. I have 2 tables the first table Employee List is a list of all of the employees that work at my location, the fieldsI will be using in this table are as follows( Manager, Employee Name, Employee # ) The second table March Connected has a list of all sales that have been completed I will be using the following fields from this table(Employee Name, Emp #, and RGU( This area has 4 different types of sales Video, Digital, Data, and Phone)

Some of the employees who have sales in the March Connected table are not employees at this location and they are not listed in the Employee List table but their sales count for this location.
What I am trying to do here is group all like RGU's and convert the text in the RGU field to a count for how many times each employee has sold the specific product. I also want to display all the employees from the employee list no matter if they have a sale or not listed in the March Connected table. Ex: if john smith has 5 entries in the March Connected table 1 video 1 data, 2 phone, and 1 digital mark smith has 4 listed in the same table 2 phone, 2 digital and Becca Smith has 0 sales listed in that table. I want all 3 individuals listed in this query it will show a numerical value for each sale and a 0 if they do not have a sale in that catagory.

Now the last problem I am running into is I need to have the same information listed for the employees who are not located at this location but I need them to display in the same query as the employees who are on my employee list. In my final query I want to display the followin catagories(Manager, Employee Name, Employee #, number of Video Sales, number of Digital Sales, number of Data sales, and number of Phone Sales).
The problem I run into when I try to create this query is Since the employees who are not located in my center are not on the employee list they will not have a manager in the final output and in order for me to get that data I have to use a left join from the the March Connected table to the Employee list table linking by Employee # catching all null values that are returned. This query will display all the employees who have sales that are not located at my center. But in order to display all the employees who are located at my center I have to do a left join from the employee list to the march Connected table by Employee #. This only displays agents with sales it does not display agents without sales I cannot figure out how to display both agents who are located at my canter with or without sales and the employees not located at my center with sales.

 

[Kafrin]

Sounds like you might need to use a Union query (although I won't guarantee that there isn't a better way!).

Build your two queries, one showing all the agents at your center only and the other showing all the agents not at your center only, and save them. Make sure these two queries have the same fields in the same order as each other.

Now start a third query. In SQL view, enter:

SELECT * FROM Query1 ORDERBY Field2
UNION
SELECT * FROM Query2

The ORDERBY clause can only be specified for Query1, so the fields you want to sort on must be shown and have the same names in both queries.

 

[gemma-the-husky]

Some of the employees who have sales in the March Connected table are not employees at this location and they are not listed in the Employee List table but their sales count for this location.

i would be concerned about this, at it is denormalising, and likely to affect your system generally, as you are finding

i would add these non-local employees to your table, and add another yes/no field to indicate non-local employee.

assuming your managers are in another table (or even the same table), you can overcome this with the join you use to pick up the manager

this will probably make your current problem easier to solve - the queries will be a lot easier to write