Re: [MS_AccessPros] Design Question

 

Sarah-

As noted earlier, it won't let you define the relationship if there are "orphan" records in the Product table, so first it validates all existing entries, then enforces the rule for any new ones.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Oct 29, 2015, at 6:46 PM, sarahk@schemesoftware.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I am using linked tables, so I defined the relationship in the  back end and it works!!!!!

I also found out that it works only on records that were entered after the relationship was defined..

Thanks again. (It is unbelievable how powerful Access is).

Sarah

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

On the Database Tools tab, click Relationships. Click Show Table to get your list of tables and then double-click the Product Category and Product tables to add them to the diagram.  Close the dialog.  Click on Product Category in the Product Category table and drag and drop it onto the matching field in the Product table.  That should open a dialog showing you Product Category on the left with the Product Category field selected in the list and Product on the right with the related field selected.  Select Enforce Referential Integrity to turn on the value checking.  It will disallow saving the relationship if you have values in the Product table that aren't in the Product Category table.  If that's the case, you will have to fix that before you can define the relationship.  To find unmatched values, run this query:

SELECT Product.*

FROM Product LEFT JOIN [Product Category]

ON Product.[Product Category] = [Product Category].[Product Category]

WHERE [Product Category].[Product Category] IS NULL;

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Oct 29, 2015, at 5:02 PM, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

This is what I am looking for.

Please point in the right direction to find the Relationships window between the two tables.

Thanks

Sarah

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Sarah-

If you define Referential Integrity using the Relationships window between the two tables, you can force any change in Product Category to be "cascaded" to all related Product rows.  If you don't turn on Cascade Update, then any attempt to change Product Category that would leave "orphan" rows in the Product table will be disallowed by Access.  Note that all Product rows must have a valid Product Category value for you to be able to define the relationship.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Oct 29, 2015, at 4:42 PM, sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have 2 tables:

Product Category

Product

The Product Categories table primary key is the product category. The Product table has a field Product Category that refers to the Product Category table.

If a user changes or deletes the primary key in the Product Categories table, this would affect the Product table, because the product category would no longer be valid.

My question is:

is there a way in Access in the design of the table to not allow or to warn the user not do this?. Or must I write VBA code to check all the tables that are using Product Category?.

This is just one small example, I have State and Country tables that are used in many other tables. If the user changes the primary key in the table, I would have to remember all the tables that refer to the State and Country tables and check all of them to see if the code is any of the entries in those tables and then warn the user no to change/delete the entry.

Is there a way to do this 'automatically'?

Sarah

 

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (23)

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___