Query Duplicating Data (1 Viewer)

[Nero]

Hi,
I posted a topic a few days ago but I didn't explain it very well.
The problem is that I have a query based on 4 tables. Each table has 14 records.
When the query is run I would expect it to show the data I need in 14 records in the query.Instead it is showing 38016 records in the query.
Can anyone please help??

Thanks,

 

[Pat Hartman]

The join is improperly defined and so the query is returning a cartesian product -
14*14*14*14. This is the number of rows in tbl1 times the number of rows in tbl2 times the number of rows in tbl3 times the number of rows in tbl4.

Open the query in QBE view and draw join lines to connect the related field table to table.

 

[Nero]

Thanks Pat,
That has done it.
The only problem I have now is that these 4 tables are deleted once the query has run.The next day 4 more tables are imported and the process continues.Once the tables have been deleted the relationship cuts.
Is there a way of automatically restablishing the relationships?
Thanks again for your reply.

 

[Pat Hartman]

The only reason to create permanent relationships via the relationship window is to enforce referential integrity. Since you are recreating these tables daily rather than maintaining them in your application, I see no reason to create permanent relationships. Drawing the join lines in a query tells Access how to relate the tables for purposes of the query and that is sufficient.

 

[Nero]

Thanks for your help Pat.
Problem solved!!