none
Relationships, Lookups and Cascading deletes RRS feed

  • Question

  • I have a table (TableA) in the database with a field e.g. XcompanyID that is defined with a lookup against another table (TableC) with companyID and companyName.

    XcompanyID is unique in TableA.

    I open the table in datasheet view and sort it on the XCompanyID field.  This apparently automatically adds an Order By (Lookup_XCompanyID.CompanyName) to the table that is now executed everytime I open the table in datasheet view.

    The problem is that if I now delete a record from TableA it also deletes the lookup record from TableC (after the usual prompt about cascading deletions).

    There are no relationships defined between the tables (And I did Show All in the relationships window).

    Any idea what is causing this?  Thanks.

    Friday, August 14, 2015 7:20 PM

Answers

  • No one suggesting that having an order by, or sort will create or cause a cascade delete. Certainly the suggestion exists that creating a lookup with enforced relationships will create a relationship.

    We can only assume some detail or issue here exists that not clear on this matter. (as noted, perhaps this was Access 2010, and was a “client” side database.

    In fact, creating a look up will not necessary cause an “enforced” relationship between the tables. So what occurred in your case seems strange and not normal.

    It also rather possible that you created a lookup that allowed MULTIPLE selections. In this case then yes you get a hidden related table, and cascade delete of the selected records WILL occur, but not records used to drive the selection. In this case, you will not see nor can you display the hidden table in the relationship window that drives the multiple selection choice. Since you cannot see the hidden tables in this case, then you would not see the cascade delete either (but it does exist and cascade delete does occur in this case).

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Saturday, August 15, 2015 3:59 PM

All replies

  • It sounds like a relationship is defined.

    The only thing that comes to mind is you by accident create an Access 2010 web database. That would prevent the relationship window from working, yet you have a relationship.

    I would consider create a blank database and importing the tables – and un-check the import relationships.

    If a cascade delete is occurring, then you have a relationship setup – it just not clear why it not showing up.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Friday, August 14, 2015 8:52 PM
  • I have a table (TableA) in the database with a field e.g. XcompanyID that is defined with a lookup against another table (TableC) with companyID and companyName.

    XcompanyID is unique in TableA.

    If XcompanyID is unique in TableA why would need a lookup?

    Build a little, test a little

    Friday, August 14, 2015 9:00 PM
  • Good thought. When I copy into a new database without the relationships, the Order By is no longer set. So why is the sort setting an Order By and creating a relationship? 
    Friday, August 14, 2015 9:08 PM
  • It needs a lookup because it is just a numeric field, but when it displays I want to show the company name.
    Friday, August 14, 2015 9:09 PM
  • A LookUp Field automatically causes a relationship between the two Tables. There is no way to display the two sets of data as a recordset otherwise. The reason the data is in some order is because Access automatically sets Indexes in the database. You can change the indexing as needed but there has to be at least 1 index in a database or it cannot function.

    If you want to display the next column in a Lookup Field you have to choose settings for that. See image...

    As you can se above you need to set the Column count to 2 or more to get your Field to show. Then to make other Fields not show, you set their size in Column Widths to Zero. Typically the first column is your AutoID or primary Field so setting it to 0" is normal because you don't reall need to see it anyway. In your underlying Query you usually include your ID Field (Indexed) then the Field(s) you want to show in the LookUp next follwed by other Fields you need but don't want to see. You also want to make sure the List width is wide enough to display all the Fields you want to see.

    Hth


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    • Proposed as answer by David_JunFeng Monday, September 7, 2015 9:51 AM
    Saturday, August 15, 2015 4:36 AM
  • "A LookUp Field automatically causes a relationship between the two Tables. There is no way to display the two sets of data as a recordset otherwise."

    So since when does an Order By on a Lookup generate a relationship that will trigger a cascading delete?

    Saturday, August 15, 2015 5:23 AM
  • No one suggesting that having an order by, or sort will create or cause a cascade delete. Certainly the suggestion exists that creating a lookup with enforced relationships will create a relationship.

    We can only assume some detail or issue here exists that not clear on this matter. (as noted, perhaps this was Access 2010, and was a “client” side database.

    In fact, creating a look up will not necessary cause an “enforced” relationship between the tables. So what occurred in your case seems strange and not normal.

    It also rather possible that you created a lookup that allowed MULTIPLE selections. In this case then yes you get a hidden related table, and cascade delete of the selected records WILL occur, but not records used to drive the selection. In this case, you will not see nor can you display the hidden table in the relationship window that drives the multiple selection choice. Since you cannot see the hidden tables in this case, then you would not see the cascade delete either (but it does exist and cascade delete does occur in this case).

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Saturday, August 15, 2015 3:59 PM
  • Instead of doing Show All in the Relationships editor, try dragging the two tables in question over to the editor, or use the Show Table dialog to display them.  If they show up as TableA_1 and TableB_1, then they are already saved in the relationship builder.  It's possible that they are outside of the viewable / scrollable area, which does occur sometimes.

    If they show up as their original table names, TableA and TableB, then I expect you may see the relationship defined between the two.  If so, edit the relationship as you see fit and move forward.



    Saturday, August 15, 2015 8:23 PM
  • ok tried this. They do not show up with the _1, and no relationship shows up either.

    I notice that if I set the Limit to List property to No, it does not happen.

    So here is a summary of it:

    Lookup defined on a field with bound column not visible, Limit to List =Yes, sort on field with Lookup ==> defines Order By property ([Lookup__XCompanyID].[CompanyName] and relationship that is not visible) ==> when delete record, it does cascading delete on lookup table too.

    I think it is something related to the bound column not being visible but it still does not make sense.


    • Edited by serenejen Sunday, August 16, 2015 4:43 PM
    Sunday, August 16, 2015 4:38 PM
  • A few questions... 

    What version of Access are you working with?

    Can you share your database on OneDrive or via email so that we can investigate directly?

    If you can't share your database, can you show us your table definitions for the two tables in question?

    Sunday, August 16, 2015 5:53 PM
  • Access 2010.  Sorry but when I try to create a trimmed down database the problem goes away.  Will try again.
    Monday, August 17, 2015 4:40 AM
  • I don't understand what you mean about a lookup that allows multiple selections.  Isn't a lookup meant to show a single value for a single code?  There are no relationships that I have defined and any lookup only returns a single value since it is a primary key in the lookup table.
    Tuesday, September 8, 2015 3:05 AM
  • Isn't a lookup meant to show a single value for a single code?

    Not necessarily.  A 'multi-valued field' is in effect a 'lookup field' which references (looks up) multiple rows in another table, i.e. it represents a binary many-to-many relationship type.  Conventionally this is modelled by a third table (sometimes colloquially called a 'junction' table) which resolves the many-to-many relationship type into two one-to-many relationship types, e.g.

    Projects----<ProjectEmployees>----Employees

    In this model the Projects table has no column which references the Employees table, whereas a multi-valued field in projects allows each row in Projects to reference multiple rows in Employees without the need to create a table to model the relationship type.  Such a table is created transparently to effect the many-to-many relationship type, but is not exposed to the user.  Similarly the one-to-many relationship types between Projects and ProjectEmployees, and between ProjectEmployees and Employees are not exposed to the user as relationship objects.  Cascade deletes are enforced in the former, which is what Albert was referring to when he said '.....and cascade delete of the selected records WILL occur'.

    However, the is does not appear to be the situation in your case.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, September 8, 2015 12:21 PM Typo corrected.
    Tuesday, September 8, 2015 12:17 PM