> Help.  I am using ADO and  have an access 97 database with 4 tables.  I
need
> to select all of the records from all 4 tables which match a specified
piece
> of data. It is set up like this:
>   table1 contains unikey,  somefield, and other fields
>   table2 thru 4 contain unique_id and other fields
> in table 1 the unikey is unique (no duplicates), but in the other tables I
> can have 0 or more records  where the unikey from table 1 matches a value
in
> the unique_id field.

> I get the somefield value from a list in a file - I then issue the
following
> select statement

> SELECT * FROM table1 left JOIN (table2 left JOIN (table3 left JOIN table4
ON
> table4.unique_id=table3.unique_id) ON table3.unique_id=table2.unique_id)
ON
> table2.unique_id=table1.unikey
> WHERE (table1.somefield='somevalue')
> ORDER BY (table1.transaction_date_yr & table1.transaction_date_mo &
> table1.transaction_date_day) DESC

> I don't get all of my records from the other tables.  For instance, if I
> have a matching record in table4, but none in table3, I don't get the ones
> from table4.  If I have matches in both, then I get both.  I tried inner
> join and right join, but they return nothing if any of the tables are
> missing a match.

> I'm assuming your Table1 is the Primary table where you know that the data
> exists already.

> You should be joining by using
> unique_id=table1.unikey
> for all of the parts.

> > Help.  I am using ADO and  have an access 97 database with 4 tables.  I
> need
> > to select all of the records from all 4 tables which match a specified
> piece
> > of data. It is set up like this:
> >   table1 contains unikey,  somefield, and other fields
> >   table2 thru 4 contain unique_id and other fields
> > in table 1 the unikey is unique (no duplicates), but in the other tables
I
> > can have 0 or more records  where the unikey from table 1 matches a
value
> in
> > the unique_id field.

> > I get the somefield value from a list in a file - I then issue the
> following
> > select statement

> > SELECT * FROM table1 left JOIN (table2 left JOIN (table3 left JOIN
table4
> ON
> > table4.unique_id=table3.unique_id) ON table3.unique_id=table2.unique_id)
> ON
> > table2.unique_id=table1.unikey
> > WHERE (table1.somefield='somevalue')
> > ORDER BY (table1.transaction_date_yr & table1.transaction_date_mo &
> > table1.transaction_date_day) DESC

> > I don't get all of my records from the other tables.  For instance, if I
> > have a matching record in table4, but none in table3, I don't get the
ones
> > from table4.  If I have matches in both, then I get both.  I tried inner
> > join and right join, but they return nothing if any of the tables are
> > missing a match.

> Ok, I have refined it further. But still cannot figure out what to do.  I
> need to get a result of 4 records since there are 4 records in table 4
that
> match.  Any ideas?

> Table1 contains the primary, unique key (unikey) and does contain a key of
> 00004613ZZ
> Table2 contains 2 matching in unique_id
> Table3 contains 0 matches
> Table4 contains 4 matches
> Table5 contains 0 matches

> this returns 2 records
> SELECT * FROM table1 LEFT JOIN table2 on table2.unique_id=table1.unikey
> WHERE (table1.unikey = "00004613ZZ");

> this returns 2 records
> SELECT * FROM table1 LEFT JOIN (table2 left join table3 on
> table3.unique_id=table2.unique_id) ON table2.unique_id=table1.unikey
> WHERE (table1.unikey = "00004613ZZ");

> this returns 2 records
> SELECT * FROM table1 LEFT JOIN (table2 LEFT JOIN (table3 left join table4
on
> table4.unique_id=table3.unique_id) ON table3.unique_id=table2.unique_id)
ON
> table2.unique_id=table1.unikey WHERE (table1.unikey = "00004613ZZ");

> this gives an error of too many fields defined
> SELECT *FROM table1 LEFT JOIN (table2 LEFT JOIN (table3 LEFT JOIN (table4
> left join table5 on table5.unique_id=table4.unique_id) ON
> table4.unique_id=table3.unique_id) ON table3.unique_id=table2.unique_id)
ON
> table2.unique_id=table1.unikey
> WHERE (table1.unikey = "00004613ZZ");