Circularly linking multiple records

[jdpeterson]

Hello,

I have two tables I wish to link together.

The first table contains records for multiple devices, i.e., two clocks,
five radios. I need it to contain a column linking to all the issues (RMAs)
associated with the device.

The second table contains the RMA number, with all the devices associated
with the RMA, i.e., one customer could send back one clock and two radios
under one RMA record.

I'm having a hard time visualizing how I can make the first table point to
multiple records in the second table, and the second table point to multiple
records in the first table.

 

[Roger Carlson]

You can't do that. What you have is a Many-To-Many relationship which can't
be directly implemented in Access (or any relational database that I know
of). In order to implement it, you have to create a third table (often
called an "intersection table" or a "linking table") which contains the a
foreign key to each of the other tables. Something like this

Device DeviceRMA RMA
====== ========= ======
DeviceID ---<DeviceID |--RMAID
(other RMAID >----| (other
fields) fields)

I have a more complete explanation on my blog here:
http://rogersaccessblog.blogspot.com/2009/01/what-is-normalization-part-v.html

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L