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)

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 B and M.

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 then y is next to x.

(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?


In your proposed PackageNextTo table, "ID (PK)" appears to be a surrogate key, presumably an autonumber. While I'm not a surrogate key advocate myself, I know folk who are who avoid them in a relationship tables (junction tables, linking tables, whatever). You would certainly need to enforce the candidate key (compound) of (PackageID, NextToPackageID) anyhow and the presence of the ID seems to have confused at least one person answering here. Therefore, I suggest you omit it.

To ensure the 'next to' relationship are stored uni-directional, add a CHECK constraint (or Validation Rule) e.g.

 PackageID CHAR(1) NOT NULL 
    REFERENCES Packages (PackageID)
 NextToPackageID CHAR(1) NOT NULL 
    REFERENCES Packages (PackageID)
 CHECK (PackageID < NextToPackageID), 
 UNIQUE (PackageID, NextToPackageID)

To show all 'next to' relationships as bi-directional, expand them using a VIEW (or create a soted Query object in your usual way):

CREATE VIEW AdjacentPackages
SELECT PackageID AS PackageID_1, 
       NextToPackageID AS PackageID_2
  FROM PackageNextTo
SELECT NextToPackageID AS PackageID_1, 
       PackageID AS PackageID_2
  FROM PackageNextTo;

Note that CHECK and CREATE VIEW require ANSI-92 Query Mode.

  • 1
    Your check and unique constraints eliminate the need for a UNION to remove duplicates. – Leigh Riffel Jan 10 '12 at 14:56
  • @LeighRiffel: indeed, a disjoint union is what I really want but no SQL Standard or product support such a relational operator, Access included. UNION CORRESPONDING would be my next choice but again not supported in Access. Which brings me to my next choice UNION, which is supported and I have used. Because I never want duplicate rows, I never use UNION ALL. HTH – onedaywhen Jan 11 '12 at 15:47
  • You will never get duplicate rows even with a UNION ALL due to your check and unique constraints. You will also not get the performance hit of a sort which a UNION requires. – Leigh Riffel Jan 11 '12 at 16:41
  • @LeighRiffel: if you and I can see that there will never be duplicate rows then the optimizer should too and is not compelled to introduce a needless sort. When I see someone using a UNION ALL then I have to waste my time checking whether duplicate rows could occur! I only work with relational data so I avoid non-relational constructs in SQL such as UNION ALL. – onedaywhen Jan 12 '12 at 7:01
  • Indeed we can deduce that there will be no duplicates, but I doubt most optimizers would check to see if subqueries are unioning the same table and that table has a unique index and the column order is distinct and there is an ascending check constraint for the columns within within the unique index. – Leigh Riffel Jan 12 '12 at 15:34

Create a view as follows:

SELECT ID, PackageID, NextToPackageID FROM PackageNextTo
SELECT ID, NextToPackageID, PackageID FROM PackageNextTo

(this is Oracle syntax and may need to be modified for Access)

  • Examine the OP's last resultset: it shows that (A, B) is not the same as (B, A) because they have different values for ID, 1 and 3 respectively. Therefore, I do not think your VIEW should include the ID column because it would incorrectly associate ID = 1 with both. I also think UNION ALL should be changed to UNION in order to remove duplicates. – onedaywhen Jan 10 '12 at 11:34
  • @onedaywhen The OP indicates the desire to not have two entries for (A,B) and (B, A) with seperate IDs. I included the ID for completness, but it is not required for the solution. It would not be incorrect to associate ID 1 with (A,B) and (B,A), because no other ID would be appropriate. ID 3 would be assigned to a different set entirely -- (M,T) probably. If the OP were concerned about duplicates then a unique key should be created on (PackageID, NextToPackageID) [as you explained in your solution] rather than using a run time UNION to remove duplicates. – Leigh Riffel Jan 10 '12 at 14:51
  • This works for what I need... just one note: I made the two select statements be "FROM PackageNextTo" .... this data is being stored in another table because one package may be next to zero or more packages. – maneesha Jan 10 '12 at 17:29
  • @maneesha Of course, I miss-typed that, but have corrected it. – Leigh Riffel Jan 10 '12 at 18:00

Do you have any locations that the packages are stored in/at (like shelf number/silo/etc)? If you do, then you can have a table with locations and package at that location. To find the adjacent packages you need only to know the location id +/-1 location. The location ID would need to be unique and sequential.

If not, then you would could define a constraint (which probably means using a database engine that is more powerful the Access, maybe SQL server express?) Then you could create a trigger function that checks that the X next to Y == Y next to X on any updates.

Another option is to create a linked list structure in your table, so that the data element for X has next filled and Y and the previous element for Y has X, with the prev/next/current fields having a unique constraint on them. Eg

Table: Packages
PackageID (PK)
PrevPackageID (unique, allow null)
NextPackageID (unique, allow null)

And then to be real sure put in a constraint or trigger function to check that prevID matches the nextID of the previous element.

  • A design rule of thumb states that a table should model either an entity or a relationship between entities but not both. Because you have broken this rule I think your suggested table will suffer update anomalies. – onedaywhen Jan 10 '12 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.