Hello Everyone,
This is a D#$% wierd problem/glitch. So I have three tables: One with common information for quotes, One with information that is only for new prospective quotes, and one with information that is only for renewal quotes. So when a quote comes in it will always have info in the common table, but will only have related information in EITHER the renewal or prospect table. Finally there will be at most one record for each quote in each table.
So I want to throw these together into one recordset so I say aha! I will use two one-to-many joins in a normal query. The one sides both start on the common tables PK QuoteId and end with the many side on the fk, QuoteId, in both the Prospect and Renewal tables. So it looks like
tblProspects>------tblCommon-------<tblRenewal
FK>------------------PK-------------<FK
Now when I add data in a field belonging to the common table the PK fills in fine. Then when data is entered into a field belonging to either the prospect or renewal table the fk in that table populates fine. But when I add data in a field belonging to the last table the fk in the last table populates but the fk in the second table resets to zero. What's going on.
Why does it do this? Any Ideas... I have tried looking over different relationships and referential integrity settings. Want an EXAMPLE or a better discription of the problem look at my post in the tables forum. Warning it is Access97. http://www.access-programmers.co.uk/forums/showthread.php?t=70891
Thanks in advance for any help and for reading such a darn long post,
Pookatech
This is a D#$% wierd problem/glitch. So I have three tables: One with common information for quotes, One with information that is only for new prospective quotes, and one with information that is only for renewal quotes. So when a quote comes in it will always have info in the common table, but will only have related information in EITHER the renewal or prospect table. Finally there will be at most one record for each quote in each table.
So I want to throw these together into one recordset so I say aha! I will use two one-to-many joins in a normal query. The one sides both start on the common tables PK QuoteId and end with the many side on the fk, QuoteId, in both the Prospect and Renewal tables. So it looks like
tblProspects>------tblCommon-------<tblRenewal
FK>------------------PK-------------<FK
Now when I add data in a field belonging to the common table the PK fills in fine. Then when data is entered into a field belonging to either the prospect or renewal table the fk in that table populates fine. But when I add data in a field belonging to the last table the fk in the last table populates but the fk in the second table resets to zero. What's going on.
Why does it do this? Any Ideas... I have tried looking over different relationships and referential integrity settings. Want an EXAMPLE or a better discription of the problem look at my post in the tables forum. Warning it is Access97. http://www.access-programmers.co.uk/forums/showthread.php?t=70891
Thanks in advance for any help and for reading such a darn long post,
Pookatech