setting up a one to many link

R

rzitowsk

How would one set up the one to many link between Master List and
Transactions? If the ID in Master List is a primary key, autonumber
field, and the ID in transactions is not a primary key, number field,
would those two fields link. I have tried such a link but I am not
able to enforce referential integrity or get cascade update related
fields or cascade delete related records?
 
A

Al Campagna

rzitowsk,
First, let me suggest that you use names that are more descriptive. Rather than
"Master List" (avoid spaces) something like tblCustomers or tblClients would be much
clearer. Or tblSales or tblServices instead of just "Transactions".
The same would apply to your "ID" fields... CustomerID, or ClientID, and SalesID or
ServicesID would be much clearer... particularly anyone elese reading your code.

In keeping with what you have now, I'll use MasterID and TransID though...
MasterID is autonumber, TransID should be Numeric Long. Master is the ONE table and
Transactions is the MANY.

If you have entered data into both of these tables already, and then want to link them
using a one to many realtionship... there may be Transactions that have no matching Master
record (orphans), and that is preventing you from creating the proper relationship.
Set up a query that compares Transactions against Master. The link should be "Show all
records in Transactions, and only those in Master where they match.
If the query produces TransIDs with no corresponding MasterID, then those records need
to be deleted before establishing an Integrity and Cascade link. In other words, there
are records right now that violate Integrity and Cascade linking, and therefore you can
not establish that link until those violations are removed.
TransID MasterID
1324 1324
1625 << TransID orphan (delete)
16957 16927
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."
 
J

Josh D

In addition, if one of the tables is local to the database and the other is a
linked table from a back-end database (or both are linked tables to a
front-end mdb file) you will not be able to create referential integrity
between the 2 tables
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top