A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Relationship design problem with multiple tables



 
 
Thread Tools Display Modes
  #1  
Old November 24th, 2004, 04:20 AM
Don
external usenet poster
 
Posts: n/a
Default Relationship design problem with multiple tables

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



  #2  
Old November 24th, 2004, 12:19 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

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.

--
Wayne Morgan
MS Access MVP


"Don" wrote in message
...
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





  #3  
Old November 24th, 2004, 02:50 PM
Don
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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.

--
Wayne Morgan
MS Access MVP


"Don" wrote in message
...
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







  #4  
Old November 24th, 2004, 07:27 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

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/downl...s/default.aspx

--
Wayne Morgan
Microsoft Access MVP


"Don" wrote in message
...
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" wrote in
message ...
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.

--
Wayne Morgan
MS Access MVP


"Don" wrote in message
...
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









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Table Design & Relationship problem... Niko Database Design 7 October 23rd, 2004 02:10 PM
Design for multiple Unions Dkline Database Design 6 October 21st, 2004 02:20 PM
Multiple tables on to one form LMB New Users 4 May 23rd, 2004 03:35 AM
Multiple Many-To-Many Tables Tom Database Design 7 May 15th, 2004 03:47 AM


All times are GMT +1. The time now is 11:47 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.