Multiple one to many's FK reseting to 0

PookaTech

Registered User.
Local time
Today, 11:00
Joined
Jun 10, 2003
Messages
53
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
 
You can use a main form with two sub-forms for data input.

See forms in database.
.
 

Attachments

[Sigh]

Thanks for the reply.

I am actualy pretty skilled with access and was just wondering why this problem occurred and if anyone knew how to stop it from occurring. I will think about the subform idea but I am leaning towards just having two seperate forms.

Subforms are great for one to many's but this is more like a one-to-possibly-one thing and I am not sure that a subform adds much value here.

Again thanks for the reply,
Pookatech
 
Jet tries very hard to make queries updatable but in some situations you can update records with a query that joins 2 or more tables but not add records.

Change the default for the foreign keys in the tables to be null rather than zero. Then remove the foreign keys from the query. You only need the primary key since all three fields - pk, fk1, fk2 will contain the value that is in pk. If the pk is an autonumber, you don't need that either.

Work with the query rather than the form. It is easier to see what is happening.

Also, make sure that you have properly defined the fk in the two child tables as the primary key. Then when you define the relationship, Jet will interpret it as 1-1. If you haven't already done this, this is the most likely problem.
 
Thanks Pat

Hey Pat,

Thanks for the reply. I had tried defining the fk's as Pk's and the relationship was being seen as a one to one. I will try setting the defaults to nulls and removing the fields from the queries though.

The query was adding the records to the appropriate tables it was just doing it with the wrong key.

I have been working with the query and I know what you mean when you say its easier to see what's going on.

I will write you back when I have had a chance to try everything out.

Thanks again,
Pookatech
 
0 as Default

Hey Pat,

The defaults were the problem. The default zero in the prospect table would override the Renewal's key that had been automatically entered; this would happen at the time the prospect info was entered. Of course the opposite of this would happen if the prospect info had been entered first and the renewal info second.

So I guess the take away lesson is don't let PKs default to 0.

Thanks for all the help.
Pookatech
 

Users who are viewing this thread

Back
Top Bottom