CODEWITHSUNDEEP
sqlms-accessjoin
Mansfield
Mansfield

Reputation: 15170

Access 97 Outer join issue

I have two tables I want to join.

Table A has one column, named "Week", and contains 52 rows: 1,2,3,4,5,6 etc. Table 2 has three columns, named "Name", "Week", and "Total", and contains 10 rows:

'Bob', 1, 1
'Bob', 3, 1
'Joe', 4, 1
'Bob', 6, 1

I want to join these together so that my data looks like:

NAME|WEEK|TOTAL
'Bob', 1, 1
'Bob', 2, 0
'Bob', 3, 1
'Bob', 4, 0
'Bob', 5, 0
'Bob', 6, 1

As you can see, a simple outer join. However, when I try to do this, I'm not getting the expected result, no matter what kind of join I use.

My query below:

SELECT a.WEEK, b.Total
FROM Weeks a LEFT JOIN Totals b ON (a.Week = b.Week and b.Name ='Bob')

The result of this query is

NAME|WEEK|TOTAL
'Bob', 1, 1
'Bob', 3, 1
'Bob', 6, 1

Thanks in advance for the help!

Upvotes: 0

Views: 594

Answers (3)

onedaywhen
onedaywhen

Reputation: 57093

SELECT b.Name, b.Week, b.Total
  FROM Totals AS b 
 WHERE b.Name ='Bob'
UNION
SELECT 'Bob' AS Name, a.Week, 0 AS Total
  FROM Weeks AS a 
 WHERE NOT EXISTS ( SELECT * 
                      FROM Totals AS b 
                     WHERE a.Week = b.Week
                           AND b.Name ='Bob' );

Upvotes: 1

JeffO
JeffO

Reputation: 8053

You were on the right track, but just needed to use a left join. Also the NZ function will put a 0 if total is null.

SELECT Totals.Person, Weeks.WeekNo, Nz(Totals.total, 0) as TotalAmount
FROM Weeks LEFT JOIN Totals 
ON (Weeks.WeekNo = Totals.weekno and Totals.Person = 'Bob');

EDIT: The query you now have won't even give the results you've shown because you left out the Name field (Which is a bad name for a field because it is a reserved word.). You're still not providing all the information. This query works.

*Another Approach: * Create a separate query on the Totals table having a where clause: Name = 'Bob' 

Select Name, WeekNo, Total From Totals Where Name = 'Bob';

and substitute that query for the Totals table in this query.

Select b.Name, w.WeekNo, b.total
from Weeks as w 
LEFT JOIN qryJustBob as b
on .WeekNo = b.WeekNo;

Upvotes: 1

JonH
JonH

Reputation: 33183

I know its access but your join is incorrect. Here we go in sql server..same concept just look at the join condition:

--dont worry about this code im just creating some temp tables

--table to store one column (mainly week number 1,2..52)
CREATE TABLE #Weeks
(
  weeknumber int
)

--insert some test data
--week numbers...I'll insert some for you
INSERT INTO #Weeks(weeknumber) VALUES(1)
INSERT INTO #Weeks(weeknumber) VALUES(2)
INSERT INTO #Weeks(weeknumber) VALUES(3)
INSERT INTO #Weeks(weeknumber) VALUES(4)
INSERT INTO #Weeks(weeknumber) VALUES(5)
INSERT INTO #Weeks(weeknumber) VALUES(6)

--create another table with two columns storing the week # and a total for that week
CREATE TABLE #Table2
 ( 
  weeknumber int,
  total int
 )

--insert some data
INSERT INTO #Table2(weeknumber, total) VALUES(1, 100)
--notice i skipped week 2 on purpose to show you the results
INSERT INTO #Table2(weeknumber, total) VALUES(3, 100)

--here's the magic
SELECT t1.weeknumber as weeknumber, ISNULL(t2.total,0) as total FROM 
#Weeks t1 LEFT JOIN #Table2 t2 ON t1.weeknumber=t2.weeknumber

--get rid of the temp tables
DROP TABLE #table2
DROP TABLE #Weeks

Results:

1   100
2   0
3   100
4   0
5   0
6   0

Take your week number table (the table that has one column:

SELECT t1.weeknumber as weeknumber

Add to it a null check to replace the null value with a 0. I think there is something in access like ISNULL:

ISNULL(t2.total, 0) as total

And start your join from your first table and left join to your second table on the weeknumber field. The result is simple:

SELECT t1.weeknumber as weeknumber, ISNULL(t2.total,0) as total FROM 
#Weeks t1 LEFT JOIN #Table2 t2 ON t1.weeknumber=t2.weeknumber

Do not pay attention to all the other code I have posted, that is only there to create temp tables and insert values into the tables.

Upvotes: 3

Related Questions