473,322 Members | 1,307 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Relationship between tables

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
Nov 12 '05 #1
2 2020
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
Nov 12 '05 #2
Thanks Pieter,

I have asked before question on access.gettingstarted 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********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
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

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

44
by: Mariusz Jedrzejewski | last post by:
Hi, I'll be very grateful if somebody can explain me why my Opera 7.23 (runing under linux) doesn't show me inner tables. Using below code I can see only "inner table 1". There is no problem with...
8
by: Andante.in.Blue | last post by:
Hello, I have just inherited a legacy Access 97 database. While going through it, I noticed something strange... its Relationships window (the one accessed by Tools --> Relationships) is almost...
5
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the...
1
by: Gt394 | last post by:
tbl_Quote PK QuoteNo tbl_QuoteDetails - Junction table between tbl_Quote and tbl_Product tbl_Product PK ProductID tbl_Thickness PK ThicknessID tbl_PanelType PK PanelID...
4
by: niv | last post by:
Hi, I am unsure on how to retrieve data from tables 2 tables that are related through a relationship table.. TableOne -------- TableOneID TableOneDesc
2
by: cspowart | last post by:
Consider first, table "A" : Plate Make Model ===================== ABC123 Ford F150 XYZ789 Dodge 1500 IJK444 Chev Silverado Then consider, table "B";
1
by: learning_codes | last post by:
Hi, I need your help. I created a relationship on four tables. It works nicely. Each table has a code type. Group A Table --- Field Name: Code Type "T" Group B Table
1
by: Ivan | last post by:
Hi I created a databse system but i have a problem figuring out how to create some tables / relationships.... The part of the systems that i am having problems with is the following i have a...
6
by: BD | last post by:
Hi, all. I need to enforce a one-to-many relationship on 2 tables, with a join table. Say the join table contains account information. It has cust_no and acct_no. Both cust_no and acct_no are...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.