and I found it, must be something about writing down the issue here that immediately it helps solve it.
On a recent solution update one of my staff had supplied a new db file, imported all records but forgot to reset the autoenter serial number of the ID field to the top one.
Therefore there were 2 products with the same ID (the user added a new one). this was the relationship that was losing records.
I also noticed that this field wasn't set to unique.
another lesson learned.
one of the things I miss about SQL is the ability to ALTER the db structure via SQL.