Join Problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

I have an access app. that links via ODBC to DB2 tables. My query takes
so long that it ends up timing out. I think I can speed it up by
optimizing the joins.

I'm trying to get all the records in table1 and all matching records in
table2, table 3, and table 4. When I set up the joins in the Design
View, then look at the SQL, it looks like it reads table1 at least
twice. Here is the code.

FROM (TABLE3 RIGHT JOIN (TABLE2 RIGHT JOIN TABLE1 ON TABLE2.A_SSN =
TABLE1.M_SSN) ON TABLE3.F_SSN = TABLE1.M_SSN) LEFT JOIN TABLE4 ON
TABLE1.M_SSN = TABLE4.R_SSN

It seems like I should be able to do something like:

FROM TABLE1
LEFT JOIN TABLE2 ON TABLE2.A_SSN = TABLE1.M_SSN
LEFT JOIN TABLE3 ON TABLE3.F_SSN = TABLE1.M_SSN
LEFT JOIN TABLE4 ON TABLE4.R_SSN = TABLE1.M_SSN

But that errors on me.

Any suggestions are appreciated. Thanks,

-Bill

.

Relevant Pages

  • Re: Find similar records across two tables
    ... An inner join based on a non-equi joining clause might work for you. ... If you wanted to set this up as a delete query, ... > addresses that are to be purged from Table1. ... > searches on Table 2 using a wildcard to find matching records in Table1. ...
    (microsoft.public.access.queries)
  • Re: Join Problem
    ... The query takes about 30 to 45 seconds now. ... I would put the ON clause in the same order ... FROM ((TABLE1 ... I'm trying to get all the records in table1 and all matching records in ...
    (microsoft.public.access.queries)
  • Re: Displaying row no/Record Counting
    ... EnteredOn Date/Time when the record was added. ... You create a query that contains Table1. ... On each row of your query, you need to count the number of records in Table1 ... In> order to generate an alphabetical listing of these people,> I have to analyze the report in Excel and sort the> spreadsheet based on the client's name. ...
    (microsoft.public.access.queries)
  • Re: How to add to a new field based on information in existing field?
    ... a query to join the two tables. ... For example, Table1 includes fields TestID ... Table2 includes fields Description and DescriptionFr. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Non-updateable query issue
    ... You have an unnormalized table (of course, as it came from a flat spreadsheet instead of a relational source), and is it has 100 columns. ... joined to the master, showing all the records, the query is not updateable. ... > Say Fred deletes record 79 from Table1, and then Betty changes the> phone ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)