Relationship design problem with multiple tables

[Don]

I have 5 tables all on a 1 to 1 with the link being the PK on all plus 2
additional tables 1 to many with the link being PK to FK_Id on both.
I then created a qryMain to hold all of the fields and this is used as the
source for the form, the 2 1 to many tables are sub forms on the main form.

The five 1 to 1 tables are all 1 record with no repetition and are as
normalised as I can make them, an alternative would be one table with 90
plus fields and two 1 to many.

Problem: Using this as described, either I can enter no data at all or by
removing 1 of the 1 to many tables enter data in part and then it complains
that a record cannot be found in tblContacts which is one of the 1 to many
tables. I am missing something fundamental here, but surely the record isnt
created until I save the form by going to the next record.
I need to get this sorted quickly as it is stopping me from completing the
work by the end of this week.
Any help in the design of the relationships would be gratefully recieved.

A simplified schema is below:

tblClient:
ID_Client PK Autonumber linked 1 to 1 to tblProgress PK Number

tblClient:
ID_Client PK Autonumber linked 1 to 1 to tblProject PK Number
and so on for two additional tables

tblClient:
ID_Client PK Autonumber linked 1 to many to tblContacts FK_ID_Client

tblClient PK Autonumber linked 1 to many to tblSupplier FK_ID_Client

The relationships have been created in the main relationship window and then
recreated in the qryMain used to supply the form.
I have ensured that no records exist in any tables (causing violation).
The sql for the qryMain is below.

SELECT tblClient.ID_Client, tblClient.txt_Cnc, tblClient.txt_Company,
tblClient.txt_Acc_Name, tblClient.txt_Acc_Num, tblClient.txt_SortCode,
tblClient.opt_Stage_Reject, tblClient.Notes, tblClient.txt_Assessor,
tblClient.txt_Country, tblClient.dte_Loan_Agg_Sent,
tblClient.dte_DD_Debit_Rec, tblProgress.dte_First_Contact,
tblProgress.dte_Elig_Complete, tblProgress.dte_Cat, tblProgress.dte_Reject,
tblProgress.dte_Reject_Letter, tblProgress.dte_Pat_Sent,
tblProgress.dte_Pat_Rec, tblProgress.dte_quote_Rec,
tblProgress.dte_Cons_Appoint, tblProgress.dte_Cons_Verify,
tblProgress.dte_Comp_Acc_Ordered, tblProgress.int_Number_Accounts,
tblProgress.dte_Comp_Acc_Rec, tblProgress.txt_Cashlflow_req,
tblProgress.dte_Credit_Check_Start, tblProgress.int_Delay_Period,
tblProgress.dte_Int_Credit_Comp, tblProgress.dte_Ext_Credit_Check,
tblProgress.dte_Ext_Credit_Check_Rec, tblProgress.dte_Rec_Sent_Ct,
tblProgress.dte_Appro_Rec, tblProgress.txt_Approver,
tblProgress.txt_Pcg_Required, tblProgress.dte_Client_Notify,
tblProgress.dte_Loan_Agg_Rec, tblProgress.dte_ProForma,
tblProgress.dte_Pcg_Rec, tblProgress.dte_Deminimus_Rec,
tblProgress.dte_DD_Debit_Bank, tblProgress.bool_Bank_Object,
tblProgress.dte_Advance_Notice, tblProgress.dte_AllDocs_Received,
tblProject.curr_Request_Loan, tblProject.curr_Proj_Cost,
tblProject.txt_Consultant, tblProject.opt_Reccomend,
tblProject.curr_Ann_Energy_Saving, tblProject.lng_Co2Tonnes,
tblProject.num_Score, tblValidate.txt_Employee_Num,
tblValidate.num_Trading_history, tblValidate.txt_Status,
tblValidate.bool_Turnover, tblValidate.bool_Assets,
tblValidate.bool_Non_Sme, tblValidate.bool_Clear_Idea,
tblValidate.bool_Exact_Project, tblValidate.bool_Project_Cat,
tblValidate.bool_Tel_Advice, tblValidate.bool_Supp_Contacted,
tblValidate.bool_Supp_Chosen, tblValidate.bool_No_Assist,
tblValidate.opt_Project_Cat, tblValidate.txt_Category,
tblValidate.dte_AnticipateAppDate, tblValidate.txt_Market_Code,
tblValidate.int_ClientCategory, tblValidate.curr_Est_Energy_Saving,
tblValidate.txt_Origin_App, tblValidate.txt_App_Source,
tblValidate.txt_Bus_Sector, tblValidate.opt_Org_Type,
tblValidate.curr_Est_Project_Cost, tblValidate.curr_Ave_Ann_Energy,
tblLoan.curr_Loan_Amount, tblLoan.int_Term, tblLoan.int_Installment,
tblLoan.dte_First_Inv_Rec, tblLoan.dte_Pay_First_Inv, tblLoan.int_Inv_Rec,
tblLoan.int_Payment_Made, tblLoan.dte_Repay_Sched, tblContacts.Fk_ID_Client,
tblContacts.txt_Contact, tblContacts.txt_Position, tblContacts.txt_Address1,
tblContacts.txt_Address2, tblContacts.txt_Address3, tblContacts.txt_Town,
tblContacts.txt_County, tblContacts.txt_Post_Code, tblContacts.txt_Phone,
tblContacts.txt_Fax, tblContacts.txt_Email, tblValidate.ID_Validate,
tblLoan.ID_Loan, tblContacts.Id_Contacts, tblProgress.ID_Progress,
tblProject.ID_Project, tblSupplierChosen.Supplier
FROM (((((tblClient LEFT JOIN tblContacts ON tblClient.ID_Client =
tblContacts.Fk_ID_Client) LEFT JOIN tblLoan ON tblClient.ID_Client =
tblLoan.ID_Loan) LEFT JOIN tblProgress ON tblClient.ID_Client =
tblProgress.ID_Progress) LEFT JOIN tblProject ON tblClient.ID_Client =
tblProject.ID_Project) LEFT JOIN tblValidate ON tblClient.ID_Client =
tblValidate.ID_Validate) LEFT JOIN tblSupplierChosen ON tblClient.ID_Client
= tblSupplierChosen.Fk_ID_Client;


Many thanks for taking the trouble to wade through this problem so far.

Don

 

[Wayne Morgan]

First, there are only a few times that you should need one-to-one tables.
Five of them is definitely unusual. It is usually better to include the
fields in a single table. Next, to get around the error, I've seen a couple
of options. 1) (not 100% sure about this one, but it shouldn't hurt
anything) Enforce referential integrity between the one-to-one tables and
set it to Cascade Updates. 2) Have a default value set for one of the fields
(and for any Required fields) in each of the secondary tables to create a
record in them for each record you create in the main table.

 

[Don]

Thanks Wayne, I did set referential integrity to cascade as you suggested
but I will try option 2 as you suggested.
I am rapidly coming to the conclusion that splitting the table is a bad idea
and it was the preconcieved idea that 90 plus fields was a lot in 1 table
that started me off in this direction.

Many thanks for your ideas.

Don

 

[Wayne Morgan]

Don,

I've also run into this problem with some of the older versions of Jet 4.
Make sure you have the current version of Jet.

http://msdn.microsoft.com/data/downloads/updates/default.aspx