Help me with employee of the month query please

HowI can do a query using the design view to show me the employee with higher sales in pesos per branch if I have 3 tables like开发者_Python百科 the following?

 Branches
 - Branch
 - Id branch

 Employees
 - EmployeeID
 - Employee Name
 - Assigned_branch

 Sales
 - Ticket Id
 - Employee Id
 - Public price

How can I get the Name of the employe who has the max sum of public price in all sales one per branch in only one query. I cannot use SQL in this and I really not use access frecuently. Please Help me!

I supose that your [Assigned_branch] field is a Foreign Key to [id branch] in table [Branches].

In design editor, drop the three tables into. Create a relation between the created field [Assigned_branch] from table [Employees] and [id branch] field in table [Branches]. Create another relation between table [Sales] field [Employee ID] and table [Employees] field [Employee ID].

On grid beneath, drop the fields [Employee ID] and [Employee Name] from table [Employees]. From table [Branches], drop all fields. From table [Sales] drop only the field [Public price].

Turn the query mode to "Totals", clicking in the "E" (sum) button. Group by all the columns on grid. In the column [Public price] from table [Sales], select the "Sum" totalization option. Order the colum "descending".

Now you have the rank of the better sales of your enterprise. If you want to retrieve only the bigger saler, you should do it quickly creating another query wich uses this one. Add the fields and, on column Sum_of_Sales created, select the "Max" option in the grid. Believe me, its easier than doing all things in just one query (getting totals and retrieving the Max value).

I hope I've helped. Please respond me if you have any doubt.

Open a new query, select the option to enter/view SQL code for the query, then paste the following and study the diagram that's created:

SELECT TOP 1 employeeid, [employee name], branch, sum(price) as total_emp_sales
FROM
    employees AS e
    INNER JOIN branches AS b ON e.assigned_branch = b.[id branch]
    INNER JOIN sales AS s on e.employeeid = s.[employee id]
GROUP BY [employeeid], [employee name], branch
ORDER BY total_emp_sales DESC

Also, for future reference, it's not practical for any of us to try and describe how to use the designer to build the exact query you want. My advice is to get an intro book on SQL, to at least get a working knowledge of the language. Then you'll have a much better idea of how to use the designer.