How do I show all records from all tables by date 1

[R00K]

Hi all,

I have a 3 tables that each contain a date field. Each table has only the dates it needs(ie. Table 1 has 4 dates because it contains 4 records). Other tables have more records and therefore more dates - almost daily. All tables are related by date. When I run my query, I get 155 records out of a possible 265 records containing data. No table has 155 records and I have tried editing the relationships so that the joins were different each time. The only change was negative.

Here is a sample using 3 tables (names have been changed to protect the innocent).

SELECT ControlDates.ControlDate, Table1.Field1, Table2.Field1, Table1.Field2, Table1.Field3, Table1.Field4
FROM (ControlDates INNER JOIN Table1 ON ControlDates.BalanceDate = Table1.T1Date) INNER JOIN Table2 ON ControlDates.BalanceDate = Table2.T2Date;

ControlDates has 490 records, table1 has 191, and Table2 has 265. The query results 155 records .

I'm guessing it is the relationships/JOINS. How do I resolve this.

Thank you,

David

 

[nicsin]

David,

why does it surprise you that you get 155 records? INNER JOIN returns records that match all the tables. So I assume 155 records have matching dates in all three tables.

 

[R00K]

I used the wizard and the relationship manager for that formula. Do you have a recommendation? OUTER JOIN? What should I do differently?

 

[nicsin]

It depends on what you want to do. You could use LEFT or RIGHT or even insert a WHERE clause. As I said in my previous post, 155 records meet the join criteria so these are returned by the query. For the time being you are asking for the records that have matching dates in the three tables. Is there something else that you want?

 

[R00K]

I just tried a Left Join and got all the records I'm after. OUTER JOIN yielded an error. Thank you.

 

[nicsin]

oh ok! just for the record, LEFT JOIN returns all the records from the first table and the matching records from the other.

 

[nicsin]

...and thnx for the star!