Problems with Join

[Guest]

I am doing an inner join from one table and another. The problem is that the
join is not working. By that I mean that the join is acting as if it is
equal, so I am not getting all the records from the first and only those from
the second table which match the join. Anyone else have this problem? Any
ideas why and how to get the join to work?

Here is the join:
FROM qa43srs_tbl_Sections_Status LEFT JOIN dbo_CDL ON
(qa43srs_tbl_Sections_Status.event_date = dbo_CDL.event_date) AND
(qa43srs_tbl_Sections_Status.Key = dbo_CDL.key_id_no);

 

[Brendan Reynolds]

That is not an inner join, Shona. 'LEFT JOIN' is short-hand for 'LEFT OUTER
JOIN'. If you want an inner join, change 'LEFT' to 'INNER'.

 

[Guest]

Sorry for the missed terminology. No I do want an outer join.
Still the primary point is that the join is not working.

 

[Brendan Reynolds]

Do either of those date/time fields contain a time portion? (For that
matter, are they really date/time fields - I'm just assuming that they are
from the names?)

Alternatively, I notice that one of the tables has a 'dbo_' prefix. Is one
of the tables a JET table (or possibly not a table at all but a query?) and
one a SQL Server table?

You might need to try something like ...

Format$(FirstDateField, "mm/dd/yyyy") = Format$(SecondDateField,
"mm/dd/yyyy")

.... to make sure you really are comparing like with like when you compare
those two date fields.

I would not, as a general rule, recommend using expressions such as the
above in a join, as it could have serious performance implications, but you
might want to try it at least temporarily, as a test, to help narrow down
the source of the problem.

 

[Guest]

Yes, the event date is a date field and they have the same format. I have
done that join many times in the past. The two tables - one is a linked table
through an ODBC connection and the other is a table I created in Access from
prior ODBC links. The dbo table that I am linking to is very large and I was
wondering if that could be part of the problem. Thanks.