field may refer to more table in from clause error

[smile]

I'm building a report from multiple queries and get error that a field may refer to more tables.

I wan't to make a report that gets data like this:


Table1
field1
date1

Table2
field2
date2

Table3
field1

Table4
field1 filled by table1field1 lookup
field2 filled by table2field2 lookup
field3 filled by table3field3 lookup
Date field

The report or query works fine if I fill all fields in table 4. If field 2 is not filled and report or query contains date field from table2 that is not filled. The report has not data.

In other words I want to pull a query and make a report that shows data from all fields above. The problem is that not every record in table4 has all fields filled. If Data was not entered in table2 fields 1 and 2 it was not entered with a lookup in table 4.

Data fill example Table4 Date field
--------------------------------
field1 field2 field3 field4

one two three four 2008-01-01
tree car red 2008-01-02
blue orange yellow 2008-01-03
meat keys 2008-01-041

This is how I need to fill tada to table 4. I filter results using date field in table 4 and get want to get results like this:

Data fill example Table4 Date field
--------------------------------
field1 field2 field3 field4


tree car red 2008-01-02

Then I create report and add date fields form tables 1 and 2 and get no data because for some records they are empty. Same if I create query like this:

Data fill example Table4 Date field Date from table1 Date from table2
---------------------------------------------------------------------------
field1 field2 field3 field4

Then when I pull a query using table 4 as source with additional data form table1 and date form table2 it can't find date from table 2 and gives no results.

I put a textbox and I try link it to table 2 date field I get error above.

How to overcome this, my relationship is one to many without force integrity.