Relationship between tables

[Marco Simone]

Hi,

I have 4 tables, tblCompany, tblA, tblB and tblC.
tblA, tblB and tblC contain same type of data, they should be in one table,
but since there are many fields, I split it into 3 tables.
Each Company can have many records in tblA, so relationship between
tblCompany and tblA, tblB and tblC are 'one to many'.
tblA, tblB and tblC are in relationship 'one to one'
tblCompany has primary key CompanyID
tblA, tblB and tblC has same primary key which consist of two fields:
CompanyID and Date
1. Is this mistake that I put same names for primary keys in tblA, tblB,
tblC. Should I put like DateA, DateB, DateC
Note that CompanyID is from tblCompany because of relationship 'one to many'
How do I establish relationship:
a) tblA and tblB ('one to one') and tblB and tblC ('one to one') or
b) tblA and tblB ('one to one'), tbl B and tblC ('one to one'), tbl A and
tblC ('one to one') => each table is connected to other

2. also should I connect tblCompany to each table with 'one to many' (tblA,
tblB, tblC), or just with tblA

Please help, as I don't know what is result of this relationhips.

Thanks, Marco

[Pieter Linden]

Before you go and make life obscenely complicated for yourself, is
there a real reason you have 4 1-to-1 tables? Seems kind of
excessive. How many fields do you have in each table? Are your
tables properly normalized? Are all of the 1-to-1 tables required
data or are there some instances where the parent record has no
related record in one or more of the 1-to-1 tables?

The only reason I ask is because if you normalize properly, you
usually don't have this problem. Or, if you really need this kind of
setup, why do you? I would answer that question first before trying
to implement a really difficult scheme. Of course, you could always
relate A-(1,M)-B-(1,1)-(1,1)... You might want to read Rebecca
Riordan's article at www.mvps.org/access on how she did it, just to
make sure you're not making a mistake.

HTH,
Pieter

[Marco Simone]

Thanks Pieter,

I have asked before question on access.gettings tarted and this table
structure or similar (with more tables) is I quess needed.
tblCompany is one to many to others. tblA, tblB, tblC contain numeric
fields. I splited into 3 tables since there are 400 numeric fields which are
all related for same date. So tblA contain around 130 fields, tbl B and tbl
C also.
For example, I need to enter data on 15.01.2003. Fields are connected on
this date, but since there is 400 fields and they belong to one table, I
could split it into 10 or more tables, but then I need to collect data
again, and it is data of same date.

Each Company can have many records in tblA, so relationship between
tblCompany and tblA, tblB and tblC are 'one to many'.
tblA, tblB and tblC are in relationship 'one to one'
tblCompany has primary key CompanyID
tblA, tblB and tblC has same primary key which consist of two fields:
CompanyID and Date
1. Is this mistake that I put same names for primary keys in tblA, tblB,
tblC. Should I put like DateA, DateB, DateC
Note that CompanyID is from tblCompany because of relationship 'one to many'
How do I establish relationship:
a) tblA and tblB ('one to one') and tblB and tblC ('one to one') or
b) tblA and tblB ('one to one'), tbl B and tblC ('one to one'), tbl A and
tblC ('one to one') => each table is connected to other

2. also should I connect tblCompany to each table with 'one to many' (tblA,
tblB, tblC), or just with tblA

Your help is greatly appreciated,
Marco
"Pieter Linden" <pi********@hot mail.com> wrote in message
news:bf******** *************** ***@posting.goo gle.com...

Before you go and make life obscenely complicated for yourself, is
there a real reason you have 4 1-to-1 tables? Seems kind of
excessive. How many fields do you have in each table? Are your
tables properly normalized? Are all of the 1-to-1 tables required
data or are there some instances where the parent record has no
related record in one or more of the 1-to-1 tables?

The only reason I ask is because if you normalize properly, you
usually don't have this problem. Or, if you really need this kind of
setup, why do you? I would answer that question first before trying
to implement a really difficult scheme. Of course, you could always
relate A-(1,M)-B-(1,1)-(1,1)... You might want to read Rebecca
Riordan's article at www.mvps.org/access on how she did it, just to
make sure you're not making a mistake.

HTH,
Pieter