• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

In Access can I have a "Foreign Key Relationship" within a single Table?

I am developing an Access 2007 "Project" (.adp) as a front-end to a SQL Server 2005 Express database.

The "real world" scenario is that we have a special group of stock items that other stock items can be "mapped" to. The purpose of this is that some customers do not want a long list of individual stock items on an invoice, they just want to see the overall quantities summed together and presented as these "mapped" items. (eg instead of a list of 30 different bangles  all listed separately, the invoice will have a single line, showing the summed quantity for a single item).

This means that each record in the stock table has a field that identifies another "mapped" record in the stock table. When I try to establish a "Foreign Key Relationship" between these fields using Microsoft SQL Server Management Studio Express, the "Delete Rule" and "Update Rule" appear to be disabled. I was hoping to change these to handle the occurence of one of the special "mapped" items being deleted or having its identifying code changed.

Is it possible to have the type of relationship I am after? Am I going about this the wrong way?
I was able to handle this myself in my old Pascal-based application where we had to do all our indexing ourselves.
0
colinasad
Asked:
colinasad
  • 4
  • 2
  • 2
2 Solutions
 
Richard LeeSoftware EnthusiastCommented:
What I think you need is a relationship in this fashion. Sound ground work may be needed to get the categories associated with the products but I think this is taking you along the correct route.

You can also enforce constraints within the ProductsInCategory table to:

1. prevent a product from being added to a category more than once.
2. prevent a product from being added to more than one categories if so desired
category-product-rel.JPG
0
 
colinasadAuthor Commented:
Thanks for the prompt response, DaTribe.

Just to clarify; the "mapped" "generic" items are fully functioning stock items in their own right, but can be used in place of numerous other stock items for the customers who want a "condensed" invoice and are not interested in all the individual stock item codes.

They are not "Categories" as such; we do make use of "Categories" for other purposes.

However, you might have fully understood all this and were just demonstrating how the "internal" relationship I am trying (unsuccessfully) to establish can be taken out of the single Table. I will experiment with this "loop" of relationships approach and get back to you with my results.

Many thanks.
0
 
Richard LeeSoftware EnthusiastCommented:
Ok Colinasad. Let me know how it works out.
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Jeffrey CoachmanCommented:
colinasad,

I believe you are looking for what is called a "Self Join"

Do a google search of:  Self Join
And first determine if this is what you really need.

Here is my favorite link on the subject:
http://msdn.microsoft.com/en-us/library/aa140085(office.10).aspx

JeffCoachman
0
 
colinasadAuthor Commented:
Thanks, boag2000.

The article in your link does describe a similar situation to the one I have. In that case, a single Employees table is used, with some of the employees also being "Managers". Individual employee records in the table hold the identifying code of another record who is the individual's Manager.

What I would want to do in my case is ensure that the data is tidied up automatically when a "Manager" is deleted (or their identifying code is changed in my case). I would not want an employee to be referencing an "empty" record for their Manager. I don't know what Access's strategy is for "re-using" unique identifers, but a further danger would be that a subsequent "new" record might be assigned an old identifier and that new employee would become a manager unintentionally.

Colin.
0
 
Jeffrey CoachmanCommented:
Again, you will have to go through the info in the links and adapt it to your specific situation.
0
 
Richard LeeSoftware EnthusiastCommented:
I think that Colin's question was answered within the posts therefore I object to this delete request.
0
 
Richard LeeSoftware EnthusiastCommented:
I think that Colin's question was answered within the posts therefore I object to this delete request.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now