Add "ALL" in combobox, with counting of total records

[Luthius]

Well I have the SQL statement below and I want to add the word "ALL" on top, counting all records and Keeping EmployeeID to LastName and "*" for "ALL".

How can I do that?

SELECT LastName, Count(Orders.EmployeeID) AS ContarDeEmployeeID
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.LastName;

Luthius

 

[revans]

SELECT '<All>' As ContarDeEmployeeID FROM Employees
UNION
SELECT LastName, Count(Orders.EmployeeID) AS ContarDeEmployeeID
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.LastName;

Then in your After Update event of the combobox you'll need to trap for the <All> so you can show all the records.

hth,

Rich

 

[Luthius]

It's not working

The query gives an error:
"The number of columns in the two selected tables or queries of a union query do not match"

I tried to change SELECT '' to put SELECT "*", and nothing... just error.

It's not working

I'm using Northwind database to get there

 

[Luthius]

Why is It not working?

 

[james_lankford]

the union isn't working becuase in a union both queries have to have the same number of and same type of columns

Well I have the SQL statement below and I want to add the word "ALL" on top, counting all records and Keeping EmployeeID to LastName and "*" for "ALL"'.

I don't understand what you're asking
but I think you're asking this

SELECT 
  LastName, 
  count(*)
FROM 
  your_table_name 

where 
  iif 
  (  
    [param] = 'All',
    1 = 1, 
    Orders.EmployeeID is not null
  )

group 
   by LastName

does that make sense ?

 

[Luthius]

Check it out:

SELECT Employees.LastName, Count(Orders.EmployeeID) AS [Total of Sales]
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.LastName;

Results:

Last Name Total of Sales
Buchanan 42
Callahan 104
Davolio 123
Dodsworth 43
Fuller 96
King 72
Leverling 127
Peacock 156
Suyama 67

What I want is:



Last Name Total of sales Employee ID
ALL 830 *
Buchanan 42 5
Callahan 104 8
Davolio 123 1
Dodsworth 43 9
Fuller 96 2
King 72 7
Leverling 127 3
Peacock 156 4
Suyama 67 6

 

[Luthius]

I fixed table to show the result as I wish

Last Name	       Total of sales	Employee ID
ALL            	        830           	*
Buchanan                42              5
Callahan     	       104              8
Davolio       	       123              1
Dodsworth          	43            	9
Fuller                 	96          	2
King                  	72        	7
Leverling             	127       	3
Peacock             	156     	4
Suyama              	67        	6

I'm using Northwind database.

 

[james_lankford]

maybe this ?
but it puts null instead of *

SELECT 
  Orders.EmployeeID as [Employee ID], 
  LastName as [Last Name], 
  Count(Orders.EmployeeID) AS [Total of Sales]
FROM 
  Employees 
    INNER JOIN 
  Orders 
    ON 
      Employees.EmployeeID = Orders.EmployeeID
GROUP BY 
  Orders.EmployeeID, 
  Employees.LastName 

union all 

select 
  null as [Employee ID], 
  "All" as [Last Name], 
  Count(Orders.EmployeeID) AS [Total of Sales]  
FROM 
  Employees 
    INNER JOIN 
  Orders 
    ON 
      Employees.EmployeeID = Orders.EmployeeID

 

[Luthius]

Thanks, It's working....
Great job.