View Single Post
  #3  
Old January 20th, 2005, 09:47 PM
Khai
external usenet poster
 
Posts: n/a
Default

LeAnne, I've seen your stuff, and know you're heavily involved in the
newsgroups, and I just have to say - I love you! hehe.

Having read all that below, and re-reading my original post, I have to ask
one minor question.
Each client can have multiple applications (so 1:m, right?) if eID% is a
random autoincrement, when I add an application, do I have to make the
ClientID = tblClient.eID or... ? do I have to type it in, or can I
programattically assign it? So I can look up a client, then see all
applications for that client? But then, on the other side, I can pull up an
Application, and go the other way to see which client it belong to? (This
is a much more simplified version of what I'm trying to do. hehe.)

tblClient
First$
Last$
eID% (PK)
myApplication$

tblApplications
clientID
Date
Amount ($000.00)
AppID% (PK)

-dpharr

"LeAnne" wrote in message
...
Whoa there, young feller. Take a deep breath. Now take another. I think
you need to step out of the circle and consider some basic tenets of
relational databases.

First: Access creates Relationships when you join tables in the
Relationships window. It's up to you to develop & set up your db schema
in such a way that relationships between tables are clearly understood
by Access, and are therefore clearly defined (not of the dreaded
"indeterminate" type) when created. Access then uses the relationships
to find associated information in your db quickly and efficiently.

As with anything, you have to start at the beginning. What you
essentially have to do is chop your data down to their most atomic and
logical components (aka "decomposing"), then figure out how to put them
all back together again in meaningful ways. Ask yourself, what are the
entities (real-life persons, places, things, or events that the db will
keep track of) and their attributes (categories of related information
relevent to each entity)? Entities & attributes = tables & fields. Once
you have determined what tables and fields the db needs, then you have
to set your primary keys (using one or more fields) to uniquely identify
each record in a table.

Then once the pk's are in place, you have to tell Access how to bring
all the information together again using criteria that you will set when
you query the db (e.g. "All orders for fiscal year 1999", "All streams
in the Shenandoah River watershed", "All contributions by corporate
donors greater than $10,000," etc.). To set up relationships between
tables, add one table's pk to the other table (in which case it's known
as a foreign key). To decide which table's pk should go where, you have
to figure out how entities are related to each other:

- In a one-to-many relationship (1:m), a record in Table A can have more
than one matching record in Table B, but a record in Table B has no more
than one matching record in table A. To set up this relationship, add
the field(s) that make up the pk on the "one" side of the relationship
to the table on the "many" side. Check out the Northwinds example
database included with most versions of Access for an example of such a
relationship between the Suppliers and Products tables; one supplier can
supply more than one product, but each product has only one supplier.

- In a many-to-many (m:n) relationship, Table A can have more than one
matching record in Table B AND tblB can have more than one matching
record in tblA. This type of relationship cannot be specified to Access
as such; you have to break up a m:n relation into TWO 1:m relationships
by using a junction or "resolver" table. Put the pk's from each of the
two tables into the resolver table, which then acts as sort of a traffic
cop between the other two tables. Again, refer to the Northwinds db for
an example...the OrderDetails table resolves a m:n relationship between
the Orders and Products tables (each Order can include one OR MORE
Products, AND each Product can be ordered one OR MORE times).

- One-to-one (1:1) relationships are rare, and generally (but not
always) mean you need to rethink your table design. In this relationship
type, tblA can have only one matching record in tblB, and tblB can have
no more than one matching record in tblA. If entities are related 1:1,
that usually (but not always) means their attributes can be combined in
a single table.

This MS support site contains links to many excellent resources on
relational database design:

http://support.microsoft.com/default...b;EN-US;289533

Now, using Allen’s donor db as an example:

Take a look at the GroupTypes table. As the text explains, there can be
more than one type of group making contributions. Here’s some example
data to help you get your head around this idea:

GrpTypeID GrpTypeName
1 Corporate
2 Nonprofit
3 Individual

The TypeID is the pk for this table.

Why not just put this information in tblGroups, you may ask? The answer
is, this way the name of each group type only has to be typed in ONCE.
To clarify this, here’s some dummy data from tblGroups:

GrpID GrpName TypeID
1 IBM Corporate
2 Microsoft Corprate
3 Gen Motors Corpororate
4 PBS Nonprofit
5 NEH Non-profit
6 Joe Blow Individual
7 Jane Doe Indiv
8 Richard Roe Ind

Compare this to:

GrpID GrpName TypeID
1 IBM 1
2 Microsoft 1
3 Gen Motors 1
4 PBS 2
5 NEH 2
6 Joe Blow 3
7 Jane Doe 3
8 Richard Roe 3

You see where I’m going here? Users need never even see Type ID or
GrpID. These key fields exist to uniquely identify each record in their
respective tables AND to help Access keep track of related information
in tables. But users “look up” the correct group type & select it from
the data entry form (based on a query that includes both tblGroupTypes
and tblGroups)…they “see” the Type Name, but Access stores the TypeID,
and the user is never the wiser. This helps head off errors when
entering data, and typos when attempting to retrieve data via queries (a
query for all groups WHERE GrpTypeName=”Corporate” will return only 1
record from tblGroups; WHERE GrpTypeName=”Croprate” will return no
records at all). Incidentally, the above example presumes a 1:m
relationship between GrpTypes and Groups...group types may appear one OR
MORE times in tblGroups, but each group represents ONE AND ONLY ONE
Group Type. The TypeID field is an autonumber in tblGroupTypes, but a
Number (Long) in tblGroups; you can’t join autonumber to autonumber

Apologies for length, but I hope this helps you get handle on pk’s. If
not, post back and I’ll try to explain it better.

Good luck,

LeAnne


Khai wrote:
table1
eID as long (autonumber)
miscinfo as text
moreinfo as text

table2
eID as long?
e2ID as long?
yetmoreinfo as text

Say I had multiple table2's for each table1. Would eID in table2 be set

to
the same value in table1.eID, and then, would that mean e2ID would be a
unique key on it's own, labeled PK? Is this where Foreign Keys are? So
table1.eID and table2.eID equal each other?

I understand the reasoning for PK's - but having trouble understanding

the
concept and how exactly they relate to another table. =/

in the example relationship dbase at
http://members.iinet.net.au/~allenbrowne/AppHuman.html (I found that in
another post here in the group), it has GroupTypeID listed in tblGroup

and
tblGroupType. Say they're long autonumbers, do you defined tblGroupID

as
Long (Autonumber), or just as Long, and on Creation, set it to
tblGroupType.GroupTypeID ? Then, if that were the case, how would you

pull
up all tblGroups in a form that were listed to tblGroupType.GroupTypeID?

*wandering around in circles*
DPHarr, hopeful database creator.