I cant establish a relationship between table

T

TracyT

Hello,

First time poster here but I'm looking forward to learning a lot.

I'm using Access database 2003. I have the following databases that I'd
like to create a 1-to-many relationship: Customers, Master Schedule and
Shipments.

The only way that I'm able access the Database Window is to click on a
database file (e.g. no F11 function). When this happens the Relationships
icon is activated but the only table in the Show Table dialog box is the one
for the database I'm in (does this make sense?).

I'm thinking there must be something in my set ups . . . can anyone help?
Thank you.

Tracy
 
C

Charlie German

In the relationship window right click on your mouse and then click on show
table and choose which table or tables you want to add, at this point you can
make the relationship to them.
 
K

Ken Sheridan

Tracy:

Are you saying you are trying to create relationships between tables from
more than one .mdb file? If so, you can't do it. The tables must be in the
same file.

You can link to tables in another file (File |Get External Data |Link Tables
on the main database menu bar) and join the linked tables in queries.
Because you can't create relationships between them, however, you can't
enforce referential integrity, so you'd be unable to stop a shipment record
for a non-existent customer being created for instance.

The simplest solution would be to import all of the tables from the other
databases into one of them, which would then enable you to create and enforce
relationships. The other database files would then be redundant of course
and could be deleted. You can import tables with the File |Get External Data
| Import menu item. This is in all probability the most appropriate solution
as if the tables need relating, then they almost certainly belong in one file.

The other thing to do if you haven't already, is to split the database into
front and back ends, the former containing the forms. queries, reports etc,
the latter the tables only. The built in Database Splitter Wizard will do
this for you. Multiple users can then use the database simultaneously by
each having a separate copy of the front end file. If the back end file is
moved to another location, e.g. on a server, then the built in Linked Table
Manager can be used to update the links in each copy of the front end. Even
with a single user splitting is advisable. It means, amongst other things,
that you can back up the data in the back end file regularly, and should a
front end file become corrupt the data in the back end is safe.
Relationships should be created between the real tables in the back end, not
the links to them in the front end.

Ken Sheridan
Stafford, England
 
T

TracyT

Ok so this worked to show all of my table but I still cant seem to establish
a one-to-many relationship. I have:

Customer and Client list, Prime Key = CustomerID
Master Schedule, Prime Key = Project No
Shipments, Prime Key = Packing List No

Each table has a field for Customer ID and I want to relate Customer ID in
both Master Schedule and Shipments.

Thanks.
 
L

Larry Linson

Click "CustomerID" in the Customer and Client List, drag to "Customer ID" in
the Master Schedule; do the same for Shipments. In each case choose the
option "all records in Customer and Client List and only those that match in
....". You will have established the join. Now if you want to see the "1" and
"lazy 8" symbols, click "Referential Integrity".

Larry Linson
Microsoft Office Access MVP
 
T

TracyT

Well it still wanted a one to one with Master Schedule so I deleted that. My
line goes through all three tables and my preliminary tests are showing that
this is working for each report.

This place rocks! I'm soooo happy I tried it. Thanks.
 
Top