I'm assuming your query has multiple tables and a relationship. Double click on the line that forms the relationship and select option 2 or 3 depending on your requirements

6 Tables in fact.  As soon as I change to option 2 or 3 on any of the relationship it refuses to run. See screenshot.  I am trying to calculate the plant loss throughout the growing process, from planting to nursery to growth stage to harvest.

LotNoT and ProductT have a connection to all 4 other tables that are not connected to each other.

Hi Maarten,

You seem to be missing a junction table to connect all 4 tables.  A junction table usually consists of fields that are present in all the tables that need to be joined.  In your case, all 4 tables have 2 fields in common: LotID and ProductsID.  And that's what your junction table should have.  See Figure 1 as an example.

In my old job I avoided junction tables whenever I could.  If I had your job, I would consider combining all 4 tables (see Figure 2).  That way I would directly control which fields to fill in and which to leave blank, and I wouldn't need a junction table.

Hi Kevin,
Thanks a lot for spending the time to help me.
Originally I set Planting, Nursery, Growth and Harvest all up in one table.  In there I had a Stage field indicating whether is was P,N,G or H.  I then changed it to the present tables as several fields were only used in one stage, such as Kg and HarvestPlantLoss (this field reflects the working practice) for the Harvest stage and LotNoDutchGreenery for the Suppliers Lot number.  Also, I thought it would be easier in queries having different tables but that seems to be a mistake (I was at Expert Level 1 or 2 when I started this database, I'm now at Expert 11).  I don't know if I can now change it back easily as there is already a fair amount of data in the database.  See the attached figure for the complete structure.

Thinking about it I presume I could add a stage field to the 4 stage tables, fill in the Stage data in them with an update query and then combine the tables into one new table.  Is that correct? I need the  stage information not just for the quantities but also for growing times versus weights, among other info.

Your last idea sounds great.  I had a similar decision to make when I was working a movie collection database: should I put actors, crew, etc. in separate tables?  I finally put them on one table with a "role" field indicating what role ("actor", "director", "composer", etc.) each person plays.

Also, watch Richard's video on "Too Many Tables."  Sometimes, even two tables are too many.

Hi Kevin,
Thanks, I'm going to try that.  I also realized I can just use Find and Replace to put the correct numbers in the tables first before merging them.