Table relationship problems

[Don]

Firstly, my apologies for cross posting with Access.getting started. I
found this group a little later and it seems more appropriate to post here.

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

 

[Duane Hookom]

In the words of fellow MVP Jeff Boyce "you are committing spreadsheet".
Multiple date and/or boolean fields should generally be normalized into
multiple records, not multiple similar fields. I wouldn't spend any
additional time attempting to make this work without re-evaluating your
table structure.

 

[Don]

Duane,

Its not that I disagree with you but I am hoping to find an answer here
rather than a flippant comment. From your past posts it is obvious that you
are expert in this product but many of us are not. Hence we ask for help.

This is a financial database and one of the requiremnts is that data is not
disjointed unnecessarily. If you have any constructive ideas they would be
most welcome, as in my opinion this is not an isolated problem with those
those of us less talented than you.

I have read numerous books including your ebook on database design and so
far have not found an answer.

Don

 

[Guest]

I disagree that it was a flippant answer. I understand your frustration, and
perhaps the humor was not to your taste, but there are some valuable
suggestions in that short answer. tblProgress seems to have multiple date
fields, which could be a problem if you want to add another item. Duane is
saying (I think) that it would generally be better to link to a
tblProgressDate (or whatever), where each record contains a date, the
associated event (credit check start, etc.), and whatever else is needed.
Later, if you want to add "credit check on hold" you can more easily do so.
For my own part, is client to supplier really one to many, or can each
supplier be associated with many clients?

 

[Don]

Bruce,

tblProgress does have many date fields as the loan is required to be tracked
through each stage of application to completion. There can only be (ever) 1
loan per client as this is an interest free Goverment initiative and various
EU regulations exclude additional loans.
To my mind this is a perfect example of a 1 to 1 link but I am willing to
stand corrected if it can be configured in a more sensible way, indeed I
would welcome any other constructive ideas.
I want to be able to construct tables in a more orderly fashion, but
currently lack the knowledge to do so, hence the reasons to ask questions in
newsgroups such as this.
Client to Supplier is 1 to many and requires a few words of explanation.
The loans are given for a specific project repayable over a fixed term. The
project may have 1 or more suppliers and the loan is (part) paid on receipt
of a copy invoice. It is not important which supplier provides the invoice.
The only purpose of this table is to identify that more than 1 supplier is
involved and no other details of the supplier are required. No loan starts
repayment until all invoices are in and complete. This part is a manual
process.

Client to Contacts is 1 to many for self evident reasons (I think)

In summary the whole process from conception to final repayment of the loan
cannot step outside of the boundaries as dictated by the fields required.
The point of splitting the (Main) Client table into 4 was to separate the 4
tabs on the form into logical groups and linking on a 1 to 1 basis to
reassemble the data.

Because many financial databases including this one start of as an Excel
workbook I have always found it difficult to identify the separate entities.
Some are obvious but ususally the tables are still well over 20 or so
fields.
I have the luxury of not having to import existing data as it is small
enough to be retyped, so I was hoping to construct this as a more sensible
data structure.
Again the nature of this transaction is that the terms (and fields) cannot
be varied.

I hope the background helps to explain some of the odd characteristics of
the structure as I have given and again I would welcome a more detailed
direction in which to go.
Many thanks

Don

 

[Duane Hookom]

I appologize if I seemed flippant. I was very serious about your table
structure. I created a loan processing application for a client that was
more normalized. They "stages" of the process were not hard-coded into
fields. There was a table of stages
tblStages
StageID
StageTitle
StageDescription
StageSequence

Each loan was included in a table of loans
tblLoans
LoanID
BorFirstName
BorLastName
....
Each stage of a particular loan was stored in a single record in a
"junction" table:
tblLoanStages
LoanStageID
LoanID relates to tblLoans.LoanID
StageID relates to tblStages.StageID
StatusID relates to tblStatus.StatusID
StatusDate ....

This system allowed my client to add or delete stages per loan and provided
the required flexibility.

 

[Guest]

Don,

The part about relational database I had a hard time getting my mind around
is that Loan Stages (to use Duane's name for the table) do not need to be in
the same table as the Loan ID. In fact, they almost certainly should not be.
Each Loan Stage (credit check, etc.) is a separate record in tblLoanStages.
The connection to tblLoans is LoanID, which appears in both tables. It needs
to be the same data type in both tables, or if the primary key is Autonumber,
it needs to be Number in the other table. By relating the tables to each
other, each LoanID will be associated with any number of records in
tblLoanStages that contain the corresponding value in the LoanID filed in
tblLoanStages. If LoanID in tblLoans is 99, every related record
in tblLoanStages will also contain 99 in LoanID. Those records, and only
those, will be associated with Loan #99. That is what the relationship does
for you. If you set up tblLoans as a main form and tblLoanStages as a
subform you will be able to work with this relationship quite smoothly. If
each client can have many contacts, and each contact can be associated with
many different clients, I think you are looking at many-to-many. That is
where the junction table comes into play: to resolve many-to-many
relationships.
There are lots of details, and what you are attempting is not simple, but
with patience and lots of questions you can get it sorted out, and you will
be very glad you did. Most of what I learned about Access I learned right
here.

 

[Don]

Gentlemen, Thank you both for a very comprehensive steer in the right
direction. I'll spend a couple of days putting this into action and see how
it goes. I think the time spent learning this new approach could be very
productive.

I very much apprciate your help.

Don