Access Relationships

  • Thread starter Continuing Education Teacher
  • Start date
C

Continuing Education Teacher

Could someone please show me to correct way to create a relationship in
Access. Everytime I create a relationship I get error messages
 
B

Beetle

If you provide a little more information about your situation, it will be
easier for someone in this group to provide you with more specific help. For
example, a little info about your tables and what the error message is
telling you.

Along the lines of general information (if that's what you are looking for)
you create relationships between table fields. Let's say you have a Customers
table with fields like;

CustomerID (primary key)
LastName
FirstName
Address

Then you have an Orders table with fields like;

OrderID (primary key)
OrderDate
OrderType

To create a relationship between the two tables you would need a CustomerID
field as a foreign key in the orders table. The relationship would be created
between the two CustomerID fields.
 
C

Continuing Education Teacher

Beetle,

I am sorry for the lack of information, but it frustrates me everytime I try
to create a relationship. I can create my two database two tables, for
instance Table A and Table B. I assigned a primary key to Table A and a
foreign key to Table B. So, I used your example. I made CustomerID as the
primary key for Table A and OderDate as the primary key for Table B.
Therefore, OrderID will be the foreign key. When I tried to create a
relationship between the tables, I just get the join line and without the one
to many symbols (no 1 for one or no 00 for many). What did I do wrong. Or
tell me the correct way to do it.
 
D

Douglas J. Steele

You'll only get the one to many symbol if you enforce relational integrity.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Continuing Education Teacher"
 
A

Albert D. Kallal

"Continuing Education Teacher"
Beetle,

I am sorry for the lack of information, but it frustrates me everytime I
try
to create a relationship. I can create my two database two tables, for
instance Table A and Table B. I assigned a primary key to Table A and a
foreign key to Table B. So, I used your example. I made CustomerID as
the
primary key for Table A and OderDate as the primary key for Table B.
Therefore, OrderID will be the foreign key. When I tried to create a
relationship between the tables, I just get the join line and without the
one
to many symbols (no 1 for one or no 00 for many). What did I do wrong.
Or
tell me the correct way to do it.
Click to expand...

Note that you don't really need a primary key in the Orders table right now,
but you SHOULD have one for EACH TABLE.

also, note that a foreign key field is a PLANE JANE regular field in the
table. There is NOTHING special about this field. This field is the CRITICAL
field you must use for you relationship. The foreign key field must be of
the same data type as the primary key it will connecting to in the main
table.

So, when you create the foreign key field, you make it a long number field.

Access will NOT set the value of this foreign key field for you, you have to
either type it in, use VBA code or use a sub-form. A sub for CAN set the
value of this foreign key field to the primary key of the parent record for
you.

-
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
 
T

Tom Wickerath

So, I used your example. I made CustomerID as the
primary key for Table A and OrderDate as the primary key for Table B.
Click to expand...

Using an OrderDate for a primary key in Table B is not a good idea, unless
you only want to allow one order to be placed each day. A primary key, by
definition, must be unique and it cannot be null.
Therefore, OrderID will be the foreign key.
Click to expand...

This also does not sound right. A foreign key should be a primary key from
another table. So, what you more likely need is a CustomerID field in your
Table B table. This field must be the same data type as the corresponding
CustomerID field in table A. If you are using an Autonumber data type for
CustomerID in Table A, then use a Number / Long Integer as the data type for
CustomerID in Table B. Remove the default value of 0 from the CustomerID
field in Table B. Do not set an index on this field. If you are using a text
data type, the field size should be the same as well.

Recommendation: Find a copy of Northwind.mdb that is likely installed on
your hard drive already. Take a look at the various relationships between the
tables in this sample database.

Which version of Access are you using? Specific directions for Access 2007
are going to be different versus directions for earlier versions of Access.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
B

Beetle

If I'm reading your post correctly, you are trying to create a relationship
between a field named CustomerID and a field named OrderID. If the two fields
don't have the same name then Access will create the relationship as 1 to 1.

Let's take your example. Table A is the "One" side of the relationship, with
a primary key named CustomerID (let's presume it is an autonumber field),
and table B is the many side with a PK named OrderID (also an autonumber).
You would then add an additional field to table B and name it CustomerID with
a data type of Number (not autonumber). Then in the relationships window you
would drag the CustomerID field from Table A to the CustomerID field in table
B. Since the two fields have the same names, and compatible data types,
Access would create a one to many relationship.

BTW - it may not be the best idea to make OrderDate your primary key.
Typically OrderID would be the PK to keep each record unique. You could have
more than one order with the same order date.

HTH
 
T

Tom Wickerath

Hi Beetle,
If the two fields don't have the same name then Access will create the
relationship as 1 to 1.
Click to expand...

This is not a correct statement. A 1 to 1 relationship will be created if
you join two uniquely indexed fields, of compatible data types, together. You
would not want to attempt to join two Autonumber data types, because they
would be fighting against each other. But, you can certainly join an
Autonumber (or Text) field that is uniquely indexed [either by setting it as
a PK, or by indexing the field: Yes (No Duplicates)] to a corresponding
Number/Long Integer (or Text) field that is also uniquely indexed. This will
create the one-to-one relationship. The names assigned to the fields does not
matter, although, one should always avoid using any words that are considered
reserved words when assigning a name to anything in an Access application.

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
B

Beetle

Thanks for the clarification Tom. That's why I like this forum. Always
learning something.

Tom Wickerath said:
Hi Beetle,
If the two fields don't have the same name then Access will create the
relationship as 1 to 1.
Click to expand...

This is not a correct statement. A 1 to 1 relationship will be created if
you join two uniquely indexed fields, of compatible data types, together. You
would not want to attempt to join two Autonumber data types, because they
would be fighting against each other. But, you can certainly join an
Autonumber (or Text) field that is uniquely indexed [either by setting it as
a PK, or by indexing the field: Yes (No Duplicates)] to a corresponding
Number/Long Integer (or Text) field that is also uniquely indexed. This will
create the one-to-one relationship. The names assigned to the fields does not
matter, although, one should always avoid using any words that are considered
reserved words when assigning a name to anything in an Access application.

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Beetle said:
If I'm reading your post correctly, you are trying to create a relationship
between a field named CustomerID and a field named OrderID. If the two fields
don't have the same name then Access will create the relationship as 1 to 1.

Let's take your example. Table A is the "One" side of the relationship, with
a primary key named CustomerID (let's presume it is an autonumber field),
and table B is the many side with a PK named OrderID (also an autonumber).
You would then add an additional field to table B and name it CustomerID with
a data type of Number (not autonumber). Then in the relationships window you
would drag the CustomerID field from Table A to the CustomerID field in table
B. Since the two fields have the same names, and compatible data types,
Access would create a one to many relationship.

BTW - it may not be the best idea to make OrderDate your primary key.
Typically OrderID would be the PK to keep each record unique. You could have
more than one order with the same order date.

HTH
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Beetle,

You're welcome. My personal standard, which most people do not use, is to
use unique field names throughout a database. I show the reason why I find
this helpful in my Crosstab Queries document:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html

See the second note in paragraph 4.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

tina

My personal standard, which most people do not use, is to
use unique field names throughout a database.
Click to expand...

well, that's my personal standard too, Tom, so at least there are two of us!
;)
 
T

Tom Wickerath

Hi Tina,

I can imagine that the databases you work on would be a joy for someone like
myself to look at! Another standard that I have, which is in conflict with
at least one of my Access MVP siblings, is to restrict the length of table
and field names to approx. 15 characters maximum. The reason is that I like
to see the full table and field names in a Relationships view. If one gets
carried away with names like:

MyReallyReallyLongTableOrFieldNameThatIsTooVerbose

then it be very difficult to get a good print relationships view, unless one
happens to have access to a plotter that is typically used for large
architectual blueprints.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

tina

I can imagine that the databases you work on would be a joy for someone
like
myself to look at!
Click to expand...

ditto! <g>

as for length of object names, i don't have a hard-and-fast limit, but i
tend to employ "standard" shortened words such as Purch for purchase, Emp
for employee, Bldg for building, and so on, as well as some abbreviations
that are common to the business or specific company that the database will
be used in. my goal is to keep object names reasonably short because, like
you, i like to see complete names in the Relationships window.

i also differ from many developers in that i prefix every table's fieldnames
with 1 to 3 (occasionally 4) letters that identify the table. that is often
the only thing that makes a fieldname unique in my db, such as cityName vs
busName.

as they say, different strokes... :)
 
B

balu

dear sir,
when ever ifailed to acheve the goal of database i suspect relation ship
setup,
is relationship setup should be report oriented ,how and guide me please
eg fields.

tblx,(receivedinvoice,receivedquantity).tblc,(storesid,invoiceid,quantitystored).tblf(issuesid,issuedquantity,item,date),or
there may other way of consumption and inspections generally which containes
irrelavent data to each other but includes in calculationa all . problom
creats in setting up relationship and generating report specific querys
 
Top