Relationships & Query using several related tables

[lhalderman]

I have a main table of data with 78 locations. In this table I have a field named 'LOT_NO'.


I have several tables of water sample data that are related to the locations and also contain a 'LOT_NO'. Samples are taken bi-annually, so twice a year I want to add the data from each location to the query so it displays in one table across

I am trying to show historical lab results for each location. Of the 78 locations, perhaps only 65 sample at a time.

In the query, I ALWAYS want to show ALL 78 locations whether they sample or not. Sometimes we also have a new location sampling that would not have historical sample data.



Whenever I add a new relationship to the query, the records it returns are kind of random. For instance this reporting period, I had 60 that sampled but only 55 rows are returning. I want to see ALL 78 and then where there are values, the value, and where no value it would be blank.


Any help is appreciated

[ranman256]

Instead of 100 tables,you should have 1 table with all the dates.
its easy to query a single date/month/year.

[June7]

As ranman said, one table for all samples. Need to fix this immediately.

However, forcing all locations to show even when location has no data will not be simple if data is filtered.