LEFT Join Help

[EAB1977]

I am hoping someome here can help.

I have a table that has data of complaints of a certain type based on
a plant and a date. There are only 3 types of complaints that it can
have. I have sucessfully created a join that brings in all my plants
and only the ones that matched (this was created via a right join).

SELECT DISTINCTROW ProjectedTable.Code, ProjectedTable.SourceNum,
Format$(ProjectedTable.EnteredDate,'yyyy') AS [EnteredDate By Year],
Sum(ProjectedTable.NumInspected) AS [Sum Of NumInspected],
Sum(ProjectedTable.NumWetInk) AS [Sum Of NumWetInk]
FROM ProjectedTable
GROUP BY ProjectedTable.Code, ProjectedTable.SourceNum, Format$
(ProjectedTable.EnteredDate,'yyyy'), Year(ProjectedTable.EnteredDate)
HAVING (((Year([ProjectedTable].[EnteredDate]))=2007))
ORDER BY ProjectedTable.Code;

then I did this one to create the join:

SELECT dbo_Plant.Code, Query1.SourceNum, Query1.[EnteredDate By Year],
Query1.[Sum Of NumInspected], Query1.[Sum Of NumWetInk]
FROM Query1 RIGHT JOIN dbo_Plant ON Query1.Code = dbo_Plant.Code;

Code SourceNum EnteredDate By Year Sum Of NumInspected Sum Of
NumWetInk
2
3
4
5 3 2007 94 3
9
10 3 2007 49 12
11
15 1 2007 1 1
15 3 2007 14 4
19
22
25 1 2007 1 1
25 3 2007 3 1
39
52
55
71
72
73
75
76
77
78

Now what I want is for each plant to have all complaints display, like
the following:

2 1 2007
2 2 2007
2 3 2007
3 1 2007
3 2 2007
3 3 2007
etc...

Every join I have created seems not to work...any ideas???

 

[Jamie Collins]

what I want is for each plant to have all complaints display, like
the following:

2 1 2007
2 2 2007
2 3 2007
3 1 2007
3 2 2007
3 3 2007
etc...

Every join I have created seems not to work...any ideas???

I think the JOIN type you require is CROSS JOIN (informally 'Cartesian
product'). While Access/Jet SQL syntax does not explicitly support the
syntax, you can use the 'old style' (pre- SQL-92) join syntax,
omitting a WHERE clause.

I have standard issue auxiliary tables Calendar (one row for every day
I'll every need with a column 'Y' for year) and Sequence (unique
integers 'seq'). I recommend you also consider not hard coding the
assumption that there will always be three complaint types e.g.

SELECT DISTINCT P1.Code, S1.seq, C1.Y
FROM dbo_Plant AS P1, Calendar AS C1, Sequence AS S1
WHERE S1.seq BETWEEN 1 AND (SELECT COUNT(*) FROM ComplaintTypes);

Jamie.

--

 

[EAB1977]

But there always will be three complaints, no matter what.

 

[Jamie Collins]

But there always will be three complaints, no matter what.

If you say so:

SELECT DISTINCT P1.Code, S1.seq, C1.Y
FROM dbo_Plant AS P1, Calendar AS C1, Sequence AS S1
WHERE S1.seq BETWEEN 1 AND 3;

Jamie.

--