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.