Learning relationships - Newbie to Access

[Bob]

Hello. I have a db, developed for dog breeders that are advertising with me.
While I have a main customer phone number, they may have an evening phone
and a cell phone. Also, the breeder may have only one breed of dog or may
have multiple breeds they raise.

If the customer calls me from their main number, I can look at the main db
through a query to identify them. However if they call me from their cell or
home phone, I cannot readily find them without going throughout the db,
using a query for the home or cell phone number. This may seem simple, but
my old brain cannot put it together. This also applies to the potential
multiple breeds they may raise. All info is in 1 main db, probably not the
right way to create a db!

This may help any newbie in the db area, to understand relationships.

Question: Do I need to make a separate db for evening numbers, and another
for the cell phone numbers?
Since the breeds may be AKC, hybrids and 2 other kennel clubs, do I need a
table for each breed type, or combine?

How do I use a form to create the db for evening number and cell number, to
create the relationships.

To me, it's like rubbing the top of the head in one direction, and the
stomach in the other direction.

Maybe you could work 1 problem at a time, like the phone numbers. That way
we could see how to create the other tables and relationships.

Thanks from me and any other newbie's.
Bob

 

[Rolls]

Question: Do I need to make a separate db for evening numbers, and another

for the cell phone numbers?

No. You need a "Phone Type" Table. One person can have many phones. The
tblPhone will contain a foreign key field called PhoneTypeID that joins to
tblPhoneType primary key.

(Parent - one)
tblPhone
PhoneID (PK)(Autonumber)
PersonID
PhoneTypeID (FK)(long integer)
PhoneNumber

(Child - many)
tblPhoneType
PhoneTypeID (PK)(Autonumber)
PhoneType

Since the breeds may be AKC, hybrids and 2 other kennel clubs, do I need a
table for each breed type, or combine?

You need a table of breed types. Another table for kennels.

How do I use a form to create the db for evening number and cell number,
to create the relationships.

Any time you want to see two tables that have a parent (one) - child (many)
relationship you use a form + subform. When you switch parent records all
the child records change.

One person, many dogs
One person many phones
One phone many phonetypes
One dog, many kennels
etc.

 

[Bob]

First, let me thank you for taking the time to answer and help the senile!!!
I feel like I am in the classic Bud Abbot and Lou Costello skit "Who's on
first, whats on second etc" I hope you know their classic skit.

Now I have created 2 tables, the Parent table called tblPhone. Making
phoneID the Primary Key. Added field PersonID (Customer name) and
{phoneTypeID for the Foreign key. Also a field called PhoneNumber. This
takes care of the Parent side.

I than created a table called tblPhoneType (The child table) with a field
called Phone TypeID, using this field as the Primary Key, and a field called
PhoneType. (Doesn't PhoneType need to be another table with a drop down
list, HomePhone, CellPhone, EveningPhone, or am I getting ahead of myself?)

I went to the relationship button, and dragged from the parent to the child,
and created the relationship. Now, how do I create the main and child forms
so, the data changes in both tables?

Again, thanks for your help.
Bob

 

[Rolls]

The table such as tblPhoneType contains as many records as you have
different phone types.

To associate the tables to forms so you can add, change, delete, find, sort,
etc. takes patience and practice.

Wizards will get you most of the way but not all. Lets say you have frmMain
with the name of one person selected and you want to display several phone
numbers with various phone types. You create sfrmPhone and embed that form
into frmMain using the subform wizard. When you connect everything as you
change people (parent table) only the child records related to one parent
record will be displayed.

the phone record looks like:

tblPhone
PhoneID (PK)
PersonID (FK)
PhoneTypeID (FK)
PhoneNumber

To make sfrmPhone use the table wizard to create a tabular continuous form
using tblPhone. Do not display PhoneID or PersonID. Change the field
PhoneTypeID text box into a combo box and rename it cboPhoneType. This will
use tblPhoneType as a rowsource via a query which can sort the phonetypes
alphabetically if you didn't enter then in that order. Set the number of
columns to 2. Set the widths to 0; 1 (this may vary) Change "Auto" width
to the sum of the column widths. Display header row = yes. Now you will
see the descriptions but will not see the key which is what the control
saves when you select a phonetype and add a phonenumber.

You will need to practice this and experiment with sizing forms and field
widths to get the final display to look right. In access online help there
is forther discussion about form wizards, subforms, and combo box controls.

This learning curve mught seem daunting but learn to do one form - subform
set and get it all to work, then you will repeat this pattern several times
as you add more tables and build more relationships into your database.

The default is to use bound controls. Binding means building the two-way
communication link between form and table. There are limitations of
complexity to the use of bound forms. The upgrade is unbound forms and the
use of DAO or ADO recordsets and additional VBA code to push or pull the
data around more flexibly.

The form wizards will let you add both parent and child records on one
screen using record selector controls, but this is awkward. You might want
to change the parent record via a combo box and add new parent records via
the Not In List event. Or else you could add a button to add a new form
where all you do is add records.

Work on this then check back.