I am working in Access 2010.
Let's say I have a database that tracks where packages sit in a warehouse.
Table: Packages PackageID (PK) PackageHeight PackageWidth PackageWeight PackageOwner (etc)
Now I want to show what packages each package is next to.
For example, A is next to B and M.
Table: PackageNextTo ID (PK), PackageID (Packages.PackageID, FK), NextToPackageID(Packages.PackageID, FK) 1, A, B 2, A, M
So when I look up
A, I find out it is next to
But when I look up
B, I also want to find out it is next to
A.... and when I look up M, I want to find out it is next to
A and also, for example, next to
T. (but M and B are not necessarily next to each other). Do I have to define these relationships explicity by adding records as follows:
Table: PackageNextTo ID (PK, AutoIncrement), PackageID, NextToPackageID 1, A, B 2, A, M 3, B, A 4, M, A 5, M, T 6, T, M
OR is there a way to automatically say if
x is next to
y is next to
(OR a better way to structure this data altogether!!)
In human terms I see redundancy between records 1 and 3 above, and between 5 and 6 above, and so on.... but I don't know if this is redundant to a database or if the database needs this explicitly called out.
If the database requires this explicit, is there a way to run a macro or query that will automatically create (B,A) when (A,B) is defined?