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
  #11  
Old March 7th, 2005, 04:41 AM
KLP
external usenet poster
 
Posts: n/a
Default

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

Kelvin

"Duane Hookom" wrote:

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




  #12  
Old March 7th, 2005, 05:37 AM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

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.

--
Duane Hookom
MS Access MVP


"KLP" wrote in message
...
At the risk of sounding like a rube, hpw is that done?

Kelvin

"Duane Hookom" wrote:

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






  #13  
Old March 7th, 2005, 05:42 AM
John Vinson
external usenet poster
 
Posts: n/a
Default

On Sun, 6 Mar 2005 19:41:02 -0800, KLP
wrote:

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

Kelvin

"Duane Hookom" wrote:

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


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]
 




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 08:07 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.