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