CODEWITHSUNDEEP
sqlsql-serverms-accessvbams-access-2010
RazorKillBen
RazorKillBen

Reputation: 573

MS Access / SQL Server JOIN to return all values in left table and zero if missing in right

Apologies firstly, as I think the title possibly makes this question sound far more difficult than it needs to be.

Essentially, I have two tables. In basic terms, it's a staff table and a "work completed" table. I'm trying to create a query that returns all the staff and the sum of the work they've done in a summary rows format.

The two tables:

STAFF_TABLE

+------------+-------------+----------------+--------------+
| TM_StaffID | TM_TeamName | TM_TeamManager | TM_StaffName |
+------------+-------------+----------------+--------------+
|          1 | HA11        | David A        | Paul A       |
|          2 | HA11        | David A        | John A       |
|          3 | HA11        | David A        | Simon A      |
|          4 | HA11        | David A        | Peter A      |
+------------+-------------+----------------+--------------+

WORK_TABLE

+-------------+------------+----------+-------------+------------+---------------+-----------+
| PS_TeamName | PS_WorkID  | PS_Staff | PS_WorkType |  PS_Date   | PS_WeekEnding | PS_Points |
+-------------+------------+----------+-------------+------------+---------------+-----------+
| HA11        | 2066944422 |        1 | Dev         | 02/08/2018 | 05/08/2018    |         1 |
| HA11        | 1869145859 |        1 | * Misc.     | 02/08/2018 | 05/08/2018    |      0.75 |
| HA11        | 2063035829 |        3 | Plan        | 01/08/2018 | 05/08/2018    |         1 |
| HA11        | 2036440149 |        3 | * Misc.     | 02/08/2018 | 05/08/2018    |       0.5 |
| HA11        | 2063023753 |        3 | Patching    | 03/08/2018 | 05/08/2018    |      0.25 |
| HA11        | 2012358108 |        4 | Plan        | 03/08/2018 | 05/08/2018    |      0.25 |
| HA11        | 2074311499 |        4 | Dev         | 30/07/2018 | 05/08/2018    |         1 |
| HA11        | 2075770157 |        4 | Patching    | 01/08/2018 | 05/08/2018    |      0.75 |
| HA11        | 2059475039 |        4 | Patching    | 03/08/2018 | 05/08/2018    |      0.75 |
| HA11        | 2062057110 |        4 | Plan        | 30/07/2018 | 05/08/2018    |      0.25 |
| HA11        | 2043715055 |        4 | * Misc.     | 02/08/2018 | 05/08/2018    |      0.25 |
+-------------+------------+----------+-------------+------------+---------------+-----------+

The query I have written that works is here:

SELECT 
    a.TM_StaffName AS [Staff], Nz(SUM(p.PS_Points), 0) AS [Total]
FROM 
    STAFF_TABLE AS a
LEFT JOIN 
    WORK_TABLE AS p ON (((a.TM_StaffID = p.PS_Staff) 
                    AND cdate(ps.PS_Date) >= #2018-07-30#) 
                    AND cdate(ps.PS_Date) <= #2018-08-05#)
WHERE 
    a.TM_TeamName = 'HA11'
GROUP BY 
    a.TM_StaffName;

The problem is, moving to SQL Server, that CDate isn't recognised as a function and fails the query. Removing the CDate brackets then says that the "JOIN function is not supported". I have also tried keeping the # instead of ' around the dates but this returns zero.

It seems to be a strange quirk but removing the CDate from the columns breaks the query as an unsupported join in MS Access.

Essentially, is there a better way to write this query so that ALL the staff names from the left table appear and if there are no matching records in the right table, it displays a zero?

An efficient and easy way of writing this results in the below tables returning 3 names on the left and 3 totals. But PS_Staff '2' should also display and show 0 instead of not appearing at all.

+---------+-------+
|  Staff  | Total |
+---------+-------+
| Paul A  |  1.75 |
| Simon A |  1.75 |
| Peter A |  3.25 |
+---------+-------+

(Need John A - 0 to show in here)

How can I achieve this to work with SQL Server and place the query in MS Access VBA?

Thanks

Upvotes: 2

Views: 272

Answers (2)

JonWay
JonWay

Reputation: 1735

Use the query to create a view in sql with the query then link the view to access db. Thisway the aggregate would have been done

CREATE VIEW dbo.Data
AS
SELECT 
a.TM_TeamName as [Staff], 
a.TM_StaffName,
isnull(Sum(p.PS_Points),0) AS [Total]
FROM @STAFF_TABLE as a
LEFT JOIN @WORK_TABLE as p ON a.TM_StaffID = p.PS_Staff
AND p.PS_Date >= '2018-07-30' AND p.PS_Date <= '2018-08-05'
 WHERE a.TM_TeamName = 'HA11'
GROUP BY a.TM_TeamName ,a.TM_StaffName

Upvotes: 1

JonWay
JonWay

Reputation: 1735

Try this (This is for sql server)

DECLARE @STAFF_TABLE TABLE (TM_StaffID INT, TM_TeamName VARCHAR(100), TM_TeamManager VARCHAR(100), TM_StaffName VARCHAR(100))
INSERT INTO @STAFF_TABLE VALUES
 (1,'HA11','David A' ,'Paul A'),
(2,'HA11','David A' ,'John A'),
(3,'HA11','David A','Simon A'),
(4,'HA11','David A' ,'Peter A');
DECLARE @WORK_TABLE TABLE 
(PS_TeamName VARCHAR(100),PS_WorkID INT, PS_Staff INT,PS_WorkType VARCHAR(100), PS_Date DATE,  PS_WeekEnding DATE, PS_Points DECIMAL(9,2))
INSERT INTO @WORK_TABLE VALUES 
('HA11',2066944422,1,'Dev','2018-08-02',  '2018-08-05',   1),
('HA11',1869145859,1,'* Misc','2018-08-02',  '2018-08-05', 0.75),
('HA11',2063035829,3,'Plan','2018-08-01',  '2018-08-05', 1),
('HA11',2036440149,3,'* Misc','2018-08-02',  '2018-08-05', 0.5),
('HA11',2063023753,3,'Patching','2018-08-03',  '2018-08-05', 0.25),
('HA11',2012358108,4,'Plan','2018-08-03',  '2018-08-05', 0.25),
('HA11',2074311499,4,'Dev','2018-07-30',  '2018-08-05',1),
('HA11',2075770157,4,'Patching','2018-08-01',  '2018-08-05',0.75),
('HA11',2059475039,4,'Patching','2018-08-03',  '2018-08-05',  0.75),
('HA11',2062057110,4,'Plan','2018-07-30',  '2018-08-05',0.25),
('HA11',2043715055,4,'* Misc.','2018-08-02',  '2018-08-05', 0.25);


SELECT 
a.TM_TeamName as [Staff], 
a.TM_StaffName,
isnull(Sum(p.PS_Points),0) AS [Total]
FROM @STAFF_TABLE as a
LEFT JOIN @WORK_TABLE as p ON a.TM_StaffID = p.PS_Staff
AND p.PS_Date >= '2018-07-30' AND p.PS_Date <= '2018-08-05'
 WHERE a.TM_TeamName = 'HA11'
GROUP BY a.TM_TeamName ,a.TM_StaffName

Output

Staff   TM_StaffName    Total
HA11    John A  0.00
HA11    Paul A  1.75
HA11    Peter A 3.25
HA11    Simon A 1.75

Upvotes: 0

Related Questions