Results 1 to 6 of 6
  1. #1
    whilburn is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    26

    WeirdQuery works but referential integrity needed

    Database example is attached.



    This database is chopped down in order for me to try and resolve the current issue.

    Test4.zip




    1. I have a table called BizTermXref that contains a two references (ChildBizTerm_ID & ParentBizTerm_ID) to another table, BizTerm (BizTerm_ID). Due to the fact that i have normalized my data, i will need to reference the data from the other tables in succession.

    2. So far, the "weirdquery1" shows what i am looking for, but if you look at the tables, 3 of them are not real, but are an accident where i just created the same objects but i suppose they are different instances. (if this has a name, please pass along, i would like to understand how access does this)

    3. Query1 is just me trying to show what i am trying to accomplish and it essentially does nothing.

    4. I tried using a 1:1 table and place all childbizterm types in their own table, as well as the Parentbizterm types, but one type of bizterm can be either a childbizterm or parentbizterm.

    I realize i have the info i am looking for through the query. but I need referential integrity due to the normalizing of the tables, as well as the query data.


    Thanks
    Wayne Hilburn

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    As you suppose, there are 2 instances of those tables in the query. The second has what's called an alias. Might be easier to see in SQL view:

    ...INNER JOIN BizTerm AS BizTerm_1...

    You can probably set up the same thing in relationships. This may help, though what you have isn't a "self" join:

    http://allenbrowne.com/ser-06.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, and you can change that alias to whatever you want to be more descriptive, like:

    INNER JOIN BizTerm AS BizTermUpstream

    or in design view by right clicking on the second instance, selecting properties and changing the Alias property.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    whilburn is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    26
    Ah, i see... i didnt think of viewing the sql... Im familiar with aliases due to MetaSql in AbInitio Reports. We are moving a process with data to Access and I have some very wierd table perspectives that work in a system that is created through something like Erwin and created in SqlServer but the data has to reside in SharePoint and due to government DISA regulations, the only engine that will work is the Access Services or Jet. all SQL server engine activity is inhibited..

    Thanks.
    I am going to test this morning.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, good luck with the project.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    whilburn is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    26
    Test4a.zip

    Paul, Thanks again for turning me onto the Allen Browne example. I sat and slobbered on myself until it popped. I realized that i was doing the same thing but in a many to many relationship. One term can be a child to many parents as well as one term can be a parent to many children terms. (not in the sense of horses or people but in overarching relationships)
    What was weird to me was that to establish relationships (referential integrity) i only needed the one alias, but for a query, i needed to create an alias for all associated tables...
    anyhoo if you would take another look at the database, i would like a sanity check on what ive done and any further ideas on the subject

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Referential integrity
    By Lou_Reed in forum Access
    Replies: 14
    Last Post: 12-17-2015, 03:08 PM
  2. Referential Integrity
    By lefty2cox in forum Forms
    Replies: 2
    Last Post: 11-30-2015, 07:26 AM
  3. referential integrity
    By askjacq in forum Database Design
    Replies: 1
    Last Post: 10-14-2011, 03:23 PM
  4. Referential Integrity
    By Desstro in forum Database Design
    Replies: 4
    Last Post: 06-13-2010, 01:29 PM
  5. Referential Integrity
    By jbarrum in forum Access
    Replies: 5
    Last Post: 01-14-2010, 09:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums