Relations between tables

[KLP]

I read ACC2000: Defining Relationships Between Tables in MS Access Database.
This is in response to getting the error message about too many fields. The
current table has 180 fields and counting. So I would like to break them up
into several individual tables. By the way, no field is redundant. I
understand from the article about linking tables. I guess I am using the
one-to many relationships. According to the article, you drag the primary
key from one table to a similiar field in the other table, often with the
same name. The primary key in the "main" table is loan#. I created a
"collateral" to hold loan collateral information. So I created a primary key
in the collateral table called Loan#, with the same field specs as in the
main table. It seems to me I now have a redundant field as I would have to
now enter the loan # twice. Am I confusing myself or what?

 

[Jack MacDonald]

Hopefully, you are entering your data into the main table using a
form. Next step is to design another form for the collateral table,
but exclude the primary key. Drag the second form onto the first, thus
creating a subform. Populate the "link" fields of the subform using
the primary key from the main table and the collateral table.

Now, whenever you create a new record in the main table (using the
form) and subsequently enter collateral information using the subform,
Access will automatically populate the primary key of the collateral
table with the correct value.

Even though you say that none of your 180 fields are redundant, that
seems like a *lot* of fields, and is rather suspicious.

I read ACC2000: Defining Relationships Between Tables in MS Access Database.
This is in response to getting the error message about too many fields. The
current table has 180 fields and counting. So I would like to break them up
into several individual tables. By the way, no field is redundant. I
understand from the article about linking tables. I guess I am using the
one-to many relationships. According to the article, you drag the primary
key from one table to a similiar field in the other table, often with the
same name. The primary key in the "main" table is loan#. I created a
"collateral" to hold loan collateral information. So I created a primary key
in the collateral table called Loan#, with the same field specs as in the
main table. It seems to me I now have a redundant field as I would have to
now enter the loan # twice. Am I confusing myself or what?

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Allen Browne]

KLP, I have to agree with Jack regarding the field count.

In the last few years, the largest table I can recall creating had 70-odd
fields, and it was not fully normalized.

It would be very unusual to be recording 180 different kinds of things about
anything. If this is about loans, you would probably need a related table to
hold the collateral items. It would have fields like this:
CollateralID AutoNumber primary key
Loan# Number foreign key to Loan.Loan#
CollateralType foreign key to the kind of collateral
CollateralValue Currency amount this is currently worth
...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

[snip]

Even though you say that none of your 180 fields are redundant, that
seems like a *lot* of fields, and is rather suspicious.

I read ACC2000: Defining Relationships Between Tables in MS Access
Database.
This is in response to getting the error message about too many fields.
The
current table has 180 fields and counting. So I would like to break them
up
into several individual tables. By the way, no field is redundant. I
understand from the article about linking tables. I guess I am using the
one-to many relationships. According to the article, you drag the primary
key from one table to a similiar field in the other table, often with the
same name. The primary key in the "main" table is loan#. I created a
"collateral" to hold loan collateral information. So I created a primary
key
in the collateral table called Loan#, with the same field specs as in the
main table. It seems to me I now have a redundant field as I would have
to
now enter the loan # twice. Am I confusing myself or what?

 

[KLP]

Yes, I am using forms. Thank you for the tips. If I understand you and
Allen right, instead of having 5 fields for each collateral item, e.g., coll
desc 1, coll desc 2 ... coll desc 5, (total of 30 fields for all collateral
items) because I figure no loan would have more than 5 pieces of collateral,
I use a subform as you described, link by loan #, use a collateral auto # id,
then list the 6 collateral fields. If my understanding is correct, it would
reduce a number of those 180+ fields, and relieve the concern if a loan has
more than 5 pieces of collateral.

Thank you,
Kelvin

 

[Jack MacDonald]

It sounds like you have a classic case/requirement for a one-to-many
relationship: one loan has one or more (i.e. "many") collateral
items. This type of relationship requires you to set up two or more
related tables. You approach the design from a completely different
perspective in a relational database such as Access than you do with a
spreadsheet. Bringing spreadsheet mentality to database design is a
classic mistake that beginners make (no offense intended).

I highly recommend that you purchase a beginners book for Access in
your local computer store and read about proper database design. It
would not surprise me if your 180 fields could (should !!??) be broken
into several separate tables.

Or post back with a broader description of what you are storing in
your 180 fields. Lots of people here are willing to help.

Good luck.

Yes, I am using forms. Thank you for the tips. If I understand you and
Allen right, instead of having 5 fields for each collateral item, e.g., coll
desc 1, coll desc 2 ... coll desc 5, (total of 30 fields for all collateral
items) because I figure no loan would have more than 5 pieces of collateral,
I use a subform as you described, link by loan #, use a collateral auto # id,
then list the 6 collateral fields. If my understanding is correct, it would
reduce a number of those 180+ fields, and relieve the concern if a loan has
more than 5 pieces of collateral.

Thank you,
Kelvin

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[KLP]

No offense taken. The info provided was very helpful. And after reading
your's and Allen's post, most of those 180 fields will be eliminated because
they are redundant - they are like the collateral fields issue - one loan has
many guarantors and I had five guarantor fields for a total of 25 fields that
can be reduced to 5.

I am quite proficient in Excel, and as you said Access is a whole new
ballgame.

Is there a book you cold recommend?

Regards,
Kelvin

 

[Jack MacDonald]

I did a search on Woody's Lounge (www.wopr.com) for "recommend book"
and found these:

http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=430296&page=&view=&sb=&o=&vc=1
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=424911&page=&view=&sb=&o=&vc=1
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=391626&page=&view=&sb=&o=&vc=1

That should get you started!

No offense taken. The info provided was very helpful. And after reading
your's and Allen's post, most of those 180 fields will be eliminated because
they are redundant - they are like the collateral fields issue - one loan has
many guarantors and I had five guarantor fields for a total of 25 fields that
can be reduced to 5.

Actually -- it will be reduced to zero fields in the Loans table.
Here's the principle:

Start with a table of people
- PeopleID
- name
- address
- etc

You will have a table of Loans that contains information specific to
the loan -- eg.
- an ID number (fieldName: LoanID)
- date
- the person taking the loan (loanee??) which is a 'foreign key' to
the people table

You will have another table of Collaterals
- ID number for each Collateral item
- a description
- which loan it pertains to (a 'foreign key' to the Loan table)

This table allows each loan to have zero or more collaterals.

Another table of relations between Guarantors and Loans
- the loan it pertains to (foreign key to Loan table)
- the person making the guarantee (foreign key to People table)

This table creates a many-to-many relationship between Loans and
Guarantors. In other words, each loan can have zero or more
guarrantors, and each person can guarrantee zero or more loans. Note
that the list of guarantors is just the 'people' table -- the people
making guarantees are treated no differently than the people taking
out loans. They are just people...

Using forms, you will be able to manage these tables very efficiently.

In relational database design, you must look at the big picture and
decide what are the entities that you need to represent in your
database. These become individual tables. In Excel, they all get
mushed into a single table. Totally different thought process.

I am quite proficient in Excel, and as you said Access is a whole new
ballgame.

Is there a book you cold recommend?

Regards,
Kelvin

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[KLP]

Jack,

I purchased Relational Databse Design for Mere Mortals, read it, and am now
taking a 12 week Access course at community college.

I changed the entire database design (wish I would have read the book
first!).

I am now running into a problem using queries. I have a lookup field in the
primary table that is linked to the tables containing the values to be
selected. When I run a query and include those fields, the data displays
correctly; however, when I put them in a calculation, I get incorrect
numbers; in fact, I cannot figure out where the numbers come from. The
objective is to be able to change the lookup tables and run the query to see
the results. I read the article at
http://www.mvps.org/access/lookupfields.htm. Is there a work around for this
problem?

Thank you for your assistance.

 

[KLP]

Jack,

I purchased Relational Databse Design for Mere Mortals, read it, and am now
taking a 12 week Access course at community college.

I changed the entire database design (wish I would have read the book
first!).

I am now running into a problem using queries. I have a lookup field in the
primary table that is linked to the tables containing the values to be
selected. When I run a query and include those fields, the data displays
correctly; however, when I put them in a calculation, I get incorrect
numbers; in fact, I cannot figure out where the numbers come from. The
objective is to be able to change the lookup tables and run the query to see
the results. I read the article at
http://www.mvps.org/access/lookupfields.htm. ; Is there a work around for
this
problem?

Thank you for your assistance.

 

[Duane Hookom]

You should include the "linked to the tables containing the values" in your
queries.

 

[KLP]

At the risk of sounding like a rube, hpw is that done?

Kelvin

 

[Duane Hookom]

Select Add Table while in the query design view. Find your lookup tables and
add them to the query. Join the proper fields and add your "descriptive"
field(s) to the query grid.

 

[John Vinson]

At the risk of sounding like a rube, hpw is that done?

Kelvin

Create a new Query.

Add your main table to the query window.

Add the Lookup Table to the query window.

Select the "lookup field" from the joined lookup table.


Access' Lookup Field is *horrible* for just this reason. It conceals
the actual content of your table. What's stored in the table - and
what gets used in any calculation - is a meaningless ID number. What
you *SEE* on the screen (and reasonably may assume is actually
*there*) is the value from the related lookup table.

You can't get at that looked-up value by simply including your table;
you need to include the lookup table into the query as well (and you
can do so perfectly as well even if no Lookup field exists).

John W. Vinson[MVP]