A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Relations between tables



 
 
Thread Tools Display Modes
  #1  
Old December 6th, 2004, 01:45 AM
KLP
external usenet poster
 
Posts: n/a
Default Relations between tables

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?
  #2  
Old December 6th, 2004, 02:12 AM
Jack MacDonald
external usenet poster
 
Posts: n/a
Default

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.



On Sun, 5 Dec 2004 16:45:03 -0800, KLP
wrote:

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?



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
  #3  
Old December 6th, 2004, 02:56 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jack MacDonald" replied in message
...

[snip]

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


On Sun, 5 Dec 2004 16:45:03 -0800, KLP
wrote:

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?



  #4  
Old December 10th, 2004, 04:57 PM
KLP
external usenet poster
 
Posts: n/a
Default

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" wrote:

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.



On Sun, 5 Dec 2004 16:45:03 -0800, KLP
wrote:

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?



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

  #5  
Old December 11th, 2004, 05:07 AM
Jack MacDonald
external usenet poster
 
Posts: n/a
Default

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.


On Fri, 10 Dec 2004 07:57:02 -0800, KLP
wrote:

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" wrote:

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.



On Sun, 5 Dec 2004 16:45:03 -0800, KLP
wrote:

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?



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
  #6  
Old December 12th, 2004, 05:37 AM
KLP
external usenet poster
 
Posts: n/a
Default

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" wrote:

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.


On Fri, 10 Dec 2004 07:57:02 -0800, KLP
wrote:

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" wrote:

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.



On Sun, 5 Dec 2004 16:45:03 -0800, KLP
wrote:

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?


**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

  #7  
Old December 12th, 2004, 07:15 PM
Jack MacDonald
external usenet poster
 
Posts: n/a
Default

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

http://www.wopr.com/cgi-bin/w3t/show...w=&sb=&o=&vc=1
http://www.wopr.com/cgi-bin/w3t/show...w=&sb=&o=&vc=1
http://www.wopr.com/cgi-bin/w3t/show...w=&sb=&o=&vc=1

That should get you started!


On Sat, 11 Dec 2004 20:37:01 -0800, KLP
wrote:

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

"Jack MacDonald" wrote:

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.


On Fri, 10 Dec 2004 07:57:02 -0800, KLP
wrote:

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" wrote:

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.



On Sun, 5 Dec 2004 16:45:03 -0800, KLP
wrote:

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?


**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
  #8  
Old March 7th, 2005, 03:33 AM
KLP
external usenet poster
 
Posts: n/a
Default

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.

"Jack MacDonald" wrote:

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.


On Fri, 10 Dec 2004 07:57:02 -0800, KLP
wrote:

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" wrote:

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.



On Sun, 5 Dec 2004 16:45:03 -0800, KLP
wrote:

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?


**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

  #9  
Old March 7th, 2005, 03:45 AM
KLP
external usenet poster
 
Posts: n/a
Default

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.



"Jack MacDonald" wrote:

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

http://www.wopr.com/cgi-bin/w3t/show...w=&sb=&o=&vc=1
http://www.wopr.com/cgi-bin/w3t/show...w=&sb=&o=&vc=1
http://www.wopr.com/cgi-bin/w3t/show...w=&sb=&o=&vc=1

That should get you started!


On Sat, 11 Dec 2004 20:37:01 -0800, KLP
wrote:

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

"Jack MacDonald" wrote:

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.


On Fri, 10 Dec 2004 07:57:02 -0800, KLP
wrote:

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" wrote:

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.



On Sun, 5 Dec 2004 16:45:03 -0800, KLP
wrote:

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?


**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

  #10  
Old March 7th, 2005, 03:48 AM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

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

--
Duane Hookom
MS Access MVP
--

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

"Jack MacDonald" wrote:

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.


On Fri, 10 Dec 2004 07:57:02 -0800, KLP
wrote:

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" wrote:

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.



On Sun, 5 Dec 2004 16:45:03 -0800, KLP
wrote:

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?


**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
two relations between same two tables Christopher Glaeser Setting Up & Running Reports 4 November 25th, 2004 01:46 AM
Relationship design problem with multiple tables Don New Users 3 November 24th, 2004 07:27 PM
Help with Junction Tables and Subforms Maureen Smith New Users 11 September 23rd, 2004 02:39 PM
Mutliple Tables lookup? Westley Database Design 4 June 15th, 2004 01:07 AM
searching for "join tables" and "join word tables" Uncle Bill Tables 1 June 11th, 2004 09:33 PM


All times are GMT +1. The time now is 11:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.