PW wrote:
I've created an outer join query, but for some reason the "Property_Def"
column is not aligning with the "ESPN" column. They should be aligned on
the ESPI common columns.
Heres the SQL of the query ...
SELECT QTags.ESCI, Approved.ESCI, QTags.attribseq, Approved.Seq, QTags.ESPI,
Approved.ESPI, Approved.Property_Def, QTags.ESPN
FROM Approved RIGHT JOIN QTags ON (Approved.ESCI = QTags.ESCI) AND
(Approved.Seq = QTags.attribseq)
WHERE (((QTags.ESCI)="005349"));
Is one of the columns you are using in the JOIN (ESCI or Seq/attribseq)
nullable?
It could be that for your query a null value in the table on the left
(the 'unpreserved table') is indistinguishable from a null resulting
from the outer join.
I'd suggest you use a derived table or VIEW ('stored query') to parse
out the null values for join purposes.
Here's an example which uses a derived table:
Sub TestApproved()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"
With .ActiveConnection
' Create stripped-down versions of OP's tables
.Execute _
"CREATE TABLE QTags ( ESCI CHAR(6) NOT NULL," & _
" CHECK (ESCI LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')," & _
" attribseq INTEGER, CHECK (attribseq > 0)," & _
" UNIQUE (ESCI, attribseq) ) ;"
.Execute _
"CREATE TABLE Approved (ESCI CHAR(6) NOT" & _
" NULL, Seq INTEGER, UNIQUE (ESCI, Seq)," & _
" FOREIGN KEY (ESCI, Seq) REFERENCES QTags" & _
" (ESCI, attribseq) ON DELETE NO ACTION ON" & _
" UPDATE NO ACTION, always_1 INTEGER DEFAULT" & _
" 1 NOT NULL, CHECK (always_1 = 1));"
' Create dummy table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"
' Create test data
.Execute _
"INSERT INTO QTags (ESCI, attribseq) SELECT" & _
" DT1.ESCI, DT1.attribseq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS attribseq FROM DropMe UNION ALL" & _
" SELECT '001234', 2 FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', 2 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', 2 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe )" & _
" AS DT1;"
.Execute _
"INSERT INTO Approved (ESCI, Seq) SELECT" & _
" DT1.ESCI, DT1.Seq FROM (" & _
" SELECT '001234' AS ESCI," & _
" 1 AS Seq FROM DropMe UNION ALL" & _
" SELECT '001234', NULL FROM DropMe UNION ALL" & _
" SELECT '005349', 1 FROM DropMe UNION ALL" & _
" SELECT '005349', NULL FROM DropMe UNION ALL" & _
" SELECT '006789', 1 FROM DropMe UNION ALL" & _
" SELECT '006789', NULL FROM DropMe )" & _
" AS DT1 ;"
Dim rs As Object
' OP's query (watch the 'always_1' column,
' being third from the left)
Set rs = .Execute( _
"SELECT * FROM Approved RIGHT JOIN QTags" & _
" ON (Approved.ESCI = QTags.ESCI) AND (Approved.Seq" & _
" = QTags.attribseq) WHERE (((QTags.ESCI)='005349'));")
MsgBox rs.GetString
rs.Close
' Suggested revised query (watch the 'always_1' column,
' being forth from the left)
Set rs = .Execute( _
"SELECT * FROM ( SELECT IIF(Seq IS NULL," & _
" -99, Seq) AS seq_amended, * FROM Approved" & _
" ) AS DT1 RIGHT JOIN QTags ON DT1.ESCI =" & _
" QTags.ESCI AND DT1.seq_amended = IIF(QTags.attribseq" & _
" IS NULL, -99, QTags.attribseq)" & _
" WHERE QTags.ESCI = '005349'")
MsgBox rs.GetString
rs.Close
End With
Set .ActiveConnection = Nothing
End With
End Sub
You may be better off opening the db created above in the the Access
GUI and running the queries therein.
However, if the columns in the JOIN are NOT NULL then ignore this post.
Jamie.
--
