Help with relationship plase

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


TransactionIDAuto Number - Not seen)
Transaction DateTo be entered)
Contact IDID of the entry in the Main Contact table above)
Call SignTo be brought in from the Contact table above)
PromotionComes 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
ReactionIDAuto 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 TypeIn 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.
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
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


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


TransactionIDAuto Number - Not seen)
Transaction DateTo be entered)
Contact IDID of the entry in the Main Contact table above)
Call SignTo be brought in from the Contact table above)
PromotionComes 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
ReactionIDAuto 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 TypeIn 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

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:

3. A Reaction table. Every Transaction will have many Reactions.

that indicates that Transactions has a one-to-many relationship with table
Reactions.

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.

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.

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.

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.

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



"Rock" wrote in message
...

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


TransactionIDAuto Number - Not seen)
Transaction DateTo be entered)
Contact IDID of the entry in the Main Contact table above)
Call SignTo be brought in from the Contact table above)
PromotionComes 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
ReactionIDAuto 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 TypeIn 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

comments inline.

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

3. A Reaction table. Every Transaction will have many Reactions.

that indicates that Transactions has a one-to-many relationship with
table
Reactions.

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


okay, since it is a one-to-many relationship, the third table (Reactions) is
necessary and correct.

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.

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'.


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'.

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.)

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.

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.

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'.

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

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.

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.

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




tina wrote:


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.


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.

Also is it a 'Text' Data Type? Couldn't find 'foreign key'.


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.

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.)


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.


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


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" wrote in message
...
Thank you again for your patience, advice and time.

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?

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..

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!

Regards,


Rock