table design nightmare

[sueviolet]

Hello



Could someone tell me what I wrong with my table design ( I have attached a snapshot of the relationship window - not all tables are shown, just ones that are in problematic query)


my problem:


When i add table main, habitat info, stream stream site, and reach data to a query it is blank

(when i add table main, habitat info, stream stream site or table main, habitat info, reach data - no problem)


I want to be able to select certain "gazetted name" from my main table and be able to view related records from both the stream site table and the reach table.



Thanks for your help

 

[glynch]

Are there only two fields in the habitat info table? If so, you don't need that table within the model you sent out.

Regardless of whether that is true or not, the table relationships you show should allow the query result you want. It is just a series of one-to-many relationships all going in one direction.

So when you enter a "gazetted name" value as a criterion, the query should use the Main_ID field to link to the habitat info table, retrieve all of the records with the matching Main_ID, gather the list of Habitat_Info_ID's, and then cascade again to the other two tables and retrieve all of the records from each table containing any of those Habitat_Info_ID's.

Try making the query joins between Habitat Info and the two "child tables" outer joins, retrieving all records from Habitat Info, but only those that match from the other two. Unless you have totally criss-crossed or conflicting data, you should get some records.

HTH

 

[sueviolet]

Thanks for taking the time to answer

I got the query to work by using your suggestion by selecting all records from habitat info table and only those that match from the other two - that got it to work - i can breathe a sigh of relief