Relationship problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tflett - 23 Jul 2007 16:22 GMT

I have a DB with 6 tables
tbl 1 (Person's Info)
ClientNum (PK)
FName
LName
+ all person info
tbl 2 (Employment info)
EmpID (PK)
ClientNum
EmpCodeNum
EmpStatus
TrainingRelated
StartDate
EndDate
tbl 3 (Employer Codes)
EmpCodeNum (PK)
EmployerName
tbl 4 (Trades)
TradeNum (PK)
TradeName
tbl 5 (Pri-Training)
PriTraID (PK)
ClientNum
TradeNum
tbl 6 (Sec-Training)
SecTraID (PK)
ClientNum
TradeNum

The problem i'm have is that when i go to create a form to enter all the
clients data
the details part of the form doesn't show in design view. And I also get
control source errors. If any can help me solve this prob That will be
great. The relationships are all one-to-many except for employercodes to
employment (1-to-1).
Tbl1 to tbl2 is 1-to-many, tbl1 to tbl5 and 6 is 1-to-many, tbl4 to tble5
and 6 is 1-to-many.

Reply to this Message

tflett - 23 Jul 2007 17:04 GMT

I need help on this problem ASAP!!

> I have a DB with 6 tables
> tbl 1 (Person's Info)
[quoted text clipped - 33 lines]
> Tbl1 to tbl2 is 1-to-many, tbl1 to tbl5 and 6 is 1-to-many, tbl4 to tble5
> and 6 is 1-to-many.

Reply to this Message

scubadiver - 23 Jul 2007 18:26 GMT

Is there any reason why "Employment" and "Employer codes" have to be in
different tables even though they are a 1-to-1 relationship? It would be more
sensible to have it all in the same table.

If you open a blank form in design view do you get the "details" section?

Signature

www.ae911truth.org

> I need help on this problem ASAP!!
>
[quoted text clipped - 35 lines]
> > Tbl1 to tbl2 is 1-to-many, tbl1 to tbl5 and 6 is 1-to-many, tbl4 to tble5
> > and 6 is 1-to-many.

Reply to this Message

tflett - 23 Jul 2007 20:44 GMT

yes

> Is there any reason why "Employment" and "Employer codes" have to be in
> different tables even though they are a 1-to-1 relationship? It would be more
[quoted text clipped - 41 lines]
> > > Tbl1 to tbl2 is 1-to-many, tbl1 to tbl5 and 6 is 1-to-many, tbl4 to tble5
> > > and 6 is 1-to-many.

Reply to this Message

John W. Vinson - 23 Jul 2007 18:59 GMT

>I need help on this problem ASAP!!

I'm sorry that the response (under 45 minutes) does not meet your
requirements. Let me know your address and I'll refund your fee for contacting
the newsgroup, together with my month's salary for answering.

On second thought, don't bother with the address... both sums are zero so
there's nothing to send.

John W. Vinson [MVP]

Reply to this Message

mray29 - 23 Jul 2007 18:38 GMT

Are you using subforms for each of the many tables in your main form?

> I have a DB with 6 tables
> tbl 1 (Person's Info)
[quoted text clipped - 33 lines]
> Tbl1 to tbl2 is 1-to-many, tbl1 to tbl5 and 6 is 1-to-many, tbl4 to tble5
> and 6 is 1-to-many.

Reply to this Message

John W. Vinson - 23 Jul 2007 19:01 GMT

>I have a DB with 6 tables
>tbl 1 (Person's Info)
[quoted text clipped - 33 lines]
>Tbl1 to tbl2 is 1-to-many, tbl1 to tbl5 and 6 is 1-to-many, tbl4 to tble5
>and 6 is 1-to-many.

Then use a Form based on tbl1; three subforms based on tbl2, tbl5 and tbl6;
and two separate maintenance forms based on tbl3 and tbl4. Meaningful
tablenames would be a good idea.

If you're trying to create One Grand Master Query to update everything -
don't, it won't work and it's not the right approach anyway.

John W. Vinson [MVP]

Reply to this Message