Help with relationship plase

  • Thread starter Thread starter Rock
  • Start date Start date
R

Rock

Hi,

I am having great trouble getting my relationships correct with these 3
tables and after reading and trying many things, need to be put on a
drip and helped out.

The trouble is I am from the old pre Dos database era and cannot get out
of my brain how they worked. Access does it differently but the penny
has not yet dropped for me.

I am trying to achieve this.. (3 Main tables/forms)

1. A Main table of Radio station contacts input via a form. (I have no
probs with creating the table or form - It is the relationships with the
following 2 tables/forms that mess me up)

A button link will lead to a..

2. A Transaction table. Every contact will have many transactions and
every transaction will have many reactions. Most input is manual except
the Call Sign.

A button link will lead to a..

3. A Reaction table. Every Transaction will have many Reactions. Most
input is manual except the Call Sign.

These are the tables..

1. Main Contact

ContactID (AutoNumber - Not Seen)
Call Sign (This is a text/number say, 4DHV)
Full Company Name
Frequency
AM-FM-Both
Formats
Address
etc.

Each Contact above will have many transactions to be recorded in the
Transactions table below.

2. Transactions


TransactionID:(Auto Number - Not seen)
Transaction Date:(To be entered)
Contact ID:(ID of the entry in the Main Contact table above)
Call Sign:(To be brought in from the Contact table above)
Promotion:(Comes in from another table - this is fine)

etc

Each Transaction will have a Reaction to it. (eg: A phone call will
produce a positive or neg result and this will need to be recorded in a
Reaction table below)

3. Reactions
ReactionID:(Auto Number - Not seen)
Reaction Date: (To be manually entered)
Call Sign (I need this from Main Contact table or Tansaction table)
Person Reacting: (To be manually entered)
Reaction Type:(In from another table - this is fine)

etc

I would really appreciate some clarity please.

My problem is..

How to keep the relationship correct ie: Contact=Trans=Reaction

Thanks

Rock
 
table Contacts has a one-to-many relationship with table Transactions.
you've included the primary key field (ContactID) from Contacts as a foreign
key field in Transactions. this is correct procedure.

your post is not clear on the relationship between Transactions and the
table Reactions:
3. A Reaction table. Every Transaction will have many Reactions.
Click to expand...
that indicates that Transactions has a one-to-many relationship with table
Reactions.
Each Transaction will have a Reaction to it. (eg: A phone call will
produce a positive or neg result and
Click to expand...
that indicates that Transactions has a one-to-one relationship with table
Reactions.

if Transactions has a one-to-many relationship with Reactions, then you need
to include the primary key field (TransactionID) from Transactions as a
foreign key field in the Reactions table.

on the other hand, if each transaction always has only one reaction, then
you don't need a third table. include the reaction data fields in the
Transactions table. i would only utilize a Reactions table if a significant
number of transactions do *not* have any reaction data at all; i prefer to
avoid having a lot of records with numerous empty fields in a table, if
possible.

my only other comment on your table design is that you should not *repeat*
the Call Sign data from table Contacts in table Transactions (or in table
Reactions). duplicating the same data (other than primary/foreign key
values, which link related records) in multiple tables violates data
normalization rules. you need to trust the principles of relational data
modeling: when you store data in one table, you can display it with linked
data in other tables whenever you wish - so there is no need to duplicate it
in additional tables.

oh, btw, i notice a field in table Contacts called Formats. if one contact
may have more than one format, then you should have a separate table for
Contact Formats. make sure you don't store multiple formats data in one
field in table Contacts.

hth
 
Thank you thus far - Things aren't quite as fuzzy as they were..

I am now here..
table Contacts has a one-to-many relationship with table Transactions.
you've included the primary key field (ContactID) from Contacts as a foreign
key field in Transactions. this is correct procedure.
Correct

your post is not clear on the relationship between Transactions and the
table Reactions:


that indicates that Transactions has a one-to-many relationship with table
Reactions.
Click to expand...
Yes it is a One to Many as I may get a different reaction for this one
transaction from another person or on another day in the future

if Transactions has a one-to-many relationship with Reactions, then you need
to include the primary key field (TransactionID) from Transactions as a
foreign key field in the Reactions table.
Click to expand...

I have now included the TransactionID primary field in the Reaction
table, but does it matter where I add it in the table? My instinct is to
make it the second field after the ReactionID primary. Would that be
correct? Also is it a 'Text' Data Type? Couldn't find 'foreign key'.
on the other hand, if each transaction always has only one reaction, then
you don't need a third table. include the reaction data fields in the
Transactions table. i would only utilize a Reactions table if a significant
number of transactions do *not* have any reaction data at all; i prefer to
avoid having a lot of records with numerous empty fields in a table, if
possible.
Click to expand...

Only thing here is that at times I do expect multiple reaction entries
for the one transaction. Even though they would be a minority I really
would need this info.
my only other comment on your table design is that you should not *repeat*
the Call Sign data from table Contacts in table Transactions (or in table
Reactions). duplicating the same data (other than primary/foreign key
values, which link related records) in multiple tables violates data
normalization rules. you need to trust the principles of relational data
modeling: when you store data in one table, you can display it with linked
data in other tables whenever you wish - so there is no need to duplicate it
in additional tables.
Click to expand...
The above had a confusion level of 9 for me before.

I am used to building dbases where any given field from the 'parent'
Contact table/form could automatically display in the 'child'
Transaction form once I went from the parent table to the child table
providing I had a field with the same name as one in the Contact table.
It does not *need* to be as data in the child table just as long at it
displayed there.

The table lookup I tried showed a list where I had to choose the data to
display. Maybe there is a lookup which can automatically display the
data field. In this case 'Call Sign'.
oh, btw, i notice a field in table Contacts called Formats. if one contact
may have more than one format, then you should have a separate table for
Contact Formats. make sure you don't store multiple formats data in one
field in table Contacts.
Click to expand...

Yes I do have a table called 'Formats' which does give me the option to
choose quickly. The Contacts table 'Format' field is a lookup one and
can only bring in one Format to the Transaction table.

I hope my explanations are understandable as I feel you have taken me
very close to getting a grip on it, but need a few more nudges I feel!
Maybe even a shove!

Thanks again

Rock
 
comments inline.

Rock said:
Thank you thus far - Things aren't quite as fuzzy as they were..

I am now here..

Yes it is a One to Many as I may get a different reaction for this one
transaction from another person or on another day in the future
Click to expand...

okay, since it is a one-to-many relationship, the third table (Reactions) is
necessary and correct.
I have now included the TransactionID primary field in the Reaction
table, but does it matter where I add it in the table? My instinct is to
make it the second field after the ReactionID primary. Would that be
correct? Also is it a 'Text' Data Type? Couldn't find 'foreign key'.
Click to expand...

the order of fields in a table is matter of convenience only. the system
does not store the data in that order. also, data should *never* be entered
directly into tables (except in limited amounts for testing during database
development). manual data entry should *always* be done in forms, and you
can arrange the "order" of the data fields (bound to form controls) any way
you want to, at that point. similarly, you can put data in any order you
desire when you create reports.
Also is it a 'Text' Data Type? Couldn't find 'foreign key'.
Click to expand...

the data type of a foreign key field must be the same as the data type of
the primary key field it refers to. *the only exception is when the primary
key field is an Autonumber data type.* a foreign key field that refers to
this primary key field, must have a data type of Number, with the Field Size
set as Long Integer.

for an explanation of foreign key fields, see the following list of links at
http://www.ltcomputerdesigns.com/JCReferences.html
click on Database Design 101 and choose a link there. one link that provides
a reasonably clear and simple explanation of foreign keys is
http://support.microsoft.com/?id=129519
download the file, which includes a paper on relational database design.

btw, the "JCReferences" webpage is an excellent one to bookmark - it has
many links to useful Access information. another excellent source to
bookmark is
http://www.mvps.org/access/
(it's included in the JCReferences webpage, too.)
The above had a confusion level of 9 for me before.

I am used to building dbases where any given field from the 'parent'
Contact table/form could automatically display in the 'child'
Transaction form once I went from the parent table to the child table
providing I had a field with the same name as one in the Contact table.
It does not *need* to be as data in the child table just as long at it
displayed there.
Click to expand...

don't confuse tables and forms. data is stored in tables; it is displayed in
forms. you can include fields from multiple tables in the RecordSource of a
single form (though sometimes you will not be able to update the data in
multi-table dataset). but if you have a Call Sign field in table Contacts,
and a Call Sign field in table Transactions, and a Call Sign field in table
Reactions - those are separate data fields, completely independent of each
other, and it is an unnecessary (and problematic) duplicaton of data.
The table lookup I tried showed a list where I had to choose the data to
display. Maybe there is a lookup which can automatically display the
data field. In this case 'Call Sign'.
Click to expand...

no, that's not how lookup fields work at all. a lookup field stores hard
data just as "normal" fields do. however, i strongly recommend that you do
*NOT* use lookup fields in your tables. if you have any now, convert them to
"normal" fields. see the link below for a detailed explanation.
http://www.mvps.org/access/lookupfields.htm
Yes I do have a table called 'Formats' which does give me the option to
choose quickly. The Contacts table 'Format' field is a lookup one and
can only bring in one Format to the Transaction table.
Click to expand...

as long as each Contact always has only one format, then putting the Format
field in the Contacts table is appropriate. see my comments above regarding
lookup fields in tables.

hth
 
Thank you again for your patience, advice and time.

I have taken on board the advice not to have Lookups in the tables and
have taken all fields back to text etc.

I will then create new forms which will include the lookups to display
pre entered data from other tables to allow for eventual quicker data input.

Is this the way to go?

My confusion was because I spent years creating dbases in Basic and Dos
before the graphic interfaces of Windows etc. Most input to them could
only be done direct to the table. Anyway, hopefully I'm gradually seeing
the light of the current day and its wonders.

I have snipped a lot of this post, in the interest of bandwidth but left
enough in case other readers need to know the info as well.

Okay now, back to the drawing board..

Regards,


Rock



the order of fields in a table is matter of convenience only. the system
does not store the data in that order. also, data should *never* be entered
directly into tables (except in limited amounts for testing during database
development). manual data entry should *always* be done in forms, and you
can arrange the "order" of the data fields (bound to form controls) any way
you want to, at that point. similarly, you can put data in any order you
desire when you create reports.
Click to expand...

I understand now that I can bring in fields from all different tables to
make up a Report so I can include the 'Call Sign' data I need to show in
a List of *all* Transactions or Reactions.
the data type of a foreign key field must be the same as the data type of
the primary key field it refers to. *the only exception is when the primary
key field is an Autonumber data type.* a foreign key field that refers to
this primary key field, must have a data type of Number, with the Field Size
set as Long Integer.
Click to expand...

Okay - I get it!
for an explanation of foreign key fields, see the following list of links at
http://www.ltcomputerdesigns.com/JCReferences.html
click on Database Design 101 and choose a link there. one link that provides
a reasonably clear and simple explanation of foreign keys is
http://support.microsoft.com/?id=129519
download the file, which includes a paper on relational database design.

btw, the "JCReferences" webpage is an excellent one to bookmark - it has
many links to useful Access information. another excellent source to
bookmark is
http://www.mvps.org/access/
(it's included in the JCReferences webpage, too.)
Click to expand...

Thank you for the sites, I will be lurking there for sure.
don't confuse tables and forms. data is stored in tables; it is displayed in
forms. you can include fields from multiple tables in the RecordSource of a
single form (though sometimes you will not be able to update the data in
multi-table dataset). but if you have a Call Sign field in table Contacts,
and a Call Sign field in table Transactions, and a Call Sign field in table
Reactions - those are separate data fields, completely independent of each
other, and it is an unnecessary (and problematic) duplicaton of data.
Click to expand...

Yes I understand now.
no, that's not how lookup fields work at all. a lookup field stores hard
data just as "normal" fields do. however, i strongly recommend that you do
*NOT* use lookup fields in your tables. if you have any now, convert them to
"normal" fields. see the link below for a detailed explanation.
http://www.mvps.org/access/lookupfields.htm
Click to expand...

At last the penny has dropped in this regard now.
I have taken all Lookups out of all tables and will recreate all forms.
I will then allow Lookups in the forms. This will allow for quicker data
input.
 
comments inline.

Rock said:
Thank you again for your patience, advice and time.
Click to expand...
you're welcome :)
I have taken on board the advice not to have Lookups in the tables and
have taken all fields back to text etc.

I will then create new forms which will include the lookups to display
pre entered data from other tables to allow for eventual quicker data input.

Is this the way to go?
Click to expand...
yes, you're on the right track now. "lookup" controls in forms are called
combo boxes (list boxes serve the same purpose), and it is appropriate to
use them there. as you say, a combo box can make data entry quicker and
easier for the user - and it has the added advantage of promoting accurate
data entry as well.
My confusion was because I spent years creating dbases in Basic and Dos
before the graphic interfaces of Windows etc. Most input to them could
only be done direct to the table. Anyway, hopefully I'm gradually seeing
the light of the current day and its wonders.

I have snipped a lot of this post, in the interest of bandwidth but left
enough in case other readers need to know the info as well.

Okay now, back to the drawing board..
Click to expand...
you're doing well, Rock, and absorbing new ideas very quickly. you'll find
lots of help and development ideas at the links i posted, and the newsgroups
are always here when you have specific questions or problems to solve. good
luck! :)
 
Back
Top