Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26
  1. #16
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi



    My Comments:-

    I have renamed the tables to make some sort of sense to anyone looking at the database for the first time.

    tblPersons is related to a tblPersonsAddress and the Address an be either a Work or Home Address

    The other tblu_Dt_UserDesc makes no sense to me at all
    Attached Thumbnails Attached Thumbnails RI.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  2. #17
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Are you going to tell a prospect for your application that they have to change their sales organization to have only three levels, when they have five?
    of course not - I would use a recursive design


    personPK
    personName
    personAddress
    ...
    ...
    ParentFK


    ParentFK would be populated with the personFK of the parent. The ultimate parent would have a null parentFK

    ergo a parent can have many 'children' each of which can have many children who in turn can have many children, etc

    In SQL Server there is functionality to handle this within SQL. However in Access, you either have to define the number of levels - by linking the table to itself x number of times, or by using a VBA recursive function to parse through records and build a view. I use them for things like organisation charts, tree views, etc

  3. #18
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    What is Access doing to my original relationship design?
    graphically it has it limitations - it can't show non standard joins for example. Relationships are not the same as query links - often they coincide but sometimes not.

  4. #19
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    Why do it this way

    Quote Originally Posted by pbaldy View Post
    Here's a discussion on the "one lookup table" method:<br>
    <br>
    <a href="https://oracle-base.com/articles/misc/one-true-lookup-tables-otlt" target="_blank" rel="nofollow">https://oracle-base.com/articles/misc/one-true-lookup-tables-otlt</a><br>
    Their conclusion, it isn't worth it.
    <br>
    <br>

    I don't know what I did to mess up this post when editing it!

    I understand, it's a bit complicated. I did it for (and wrote it) 40 years (ago) without problems (in PICK), but then I coded it. It took me about two days to get it all done from from design, to table to form and report, all done manually. We had dozens of clients, each with thousands of customers, that part of the application worked flawlessly.<br>
    <br>
    The problem I see in my example is that the "self-join" should work, based on a post in another thread.<br>
    <br>
    <a href="https://www.accessforums.net/showthread.php?t=86146" target="_blank">One table to create a hierarchy instead of many tables. How to create “link” logic? (accessforums.net)</a> post #2<br>
    It's pretty much the same logic, just there's only one parent. My other thread tried to create hundreds of parent relationships this way:<br><br><img src="https://www.accessforums.net/attachment.php?attachmentid=48155&amp;stc=1" attachmentid="48155" alt="" id="vbattach_48155" class="previewthumb"><br>
    <br>
    Creating the "links" in the query design works great without Relationships defined . Once Relationships are added between the tables, all hell seems to break loose.<br>
    At least, from what I've seen so far, but maybe I'm doing something wrong.<br>
    <br>
    (BTW, I don't see how to see all relationships or delete all at once in the Relationships Design tab.)
    Attached Thumbnails Attached Thumbnails P1110418.jpg  

  5. #20
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Quote Originally Posted by Ajax View Post
    graphically it has it limitations - it can't show non standard joins for example. Relationships are not the same as query links - often they coincide but sometimes not.
    Is there another way (other than graphically) to see all the relationships, and edit them once they are created?

  6. #21
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You need to be clear about whether you are talking about relationships or query links. You don't have to define relationships, but if you do you are specifying how one record is related to another. If you don't use relationships, you need to manage it using vba. For example if a relationship is specified as enforced then that means you cannot create a child table before creating the parent record, the db won't let you end of. If you try to do this after a few records have been created and there are orphan records (where either the specified parent doesn't exist or has not been specified) then you cannot update to enforced until you have resolved this. Similarly you cannot delete a parent unless it has no associated child records. Not much point in having an invoice lines record without a parent invoice header record for example. If you don't do this in sql and you need to enforce then you will need to use vba - another complicated solution. However there are plenty of developers out there who don't use relationships (but I'm not one of them).

    with regards viewing the relationships use this query (or open the table)

    SELECT *
    FROM msysRelationships

    but you cannot edit it.

    If you don't want to edit via the relationships GUI you can use a data definition query. see this link for examples
    https://www.w3schools.com/sql/sql_foreignkey.asp

  7. #22
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Reply to post #21

    We seem to be on the same page as far as a recursive solution goes. That’s what’s shown in my post #14. I don’t allow null values, they cause problems later in this type of scheme. When I say “Relationship, I’m referring to the Database tools>Relationships graphical designer and what it creates. When I say query table links, I mean the ones created in the top half of the query design view.

    Yes, I want to use Table Relationships in the Database tools>Relationships graphical designer. What’s the point of moving to access, if I have to do all that work again? But, I’m having trouble getting the “relationship” methods to play well together. I understand the order of precedence in creating records. Had that in my old systems too. Since I did create the sample data in the Datasheet view, there were a few errors when I established referential integrity.
    “with regards viewing the relationships use this query (or open the table)”
    I’ll try the query, but how do you open that table?

    Oddly, now these tables started showing up! Look at the top of the image.

    Click image for larger version. 

Name:	Sample3_3.jpg 
Views:	12 
Size:	90.9 KB 
ID:	48165

    If you go back to the first file I sent, dbOneLuTable1.accdb, you’ll see a clean design
    Click image for larger version. 

Name:	Sample3_4.jpg 
Views:	12 
Size:	78.0 KB 
ID:	48166
    And result.


    Click image for larger version. 

Name:	Sample3_5.jpg 
Views:	12 
Size:	277.6 KB 
ID:	48167
    Exactly what I wanted.

    The problem now comes when I try add the tblPers to the query after building the table relationships (in Database tools):
    Click image for larger version. 

Name:	Sample3_6.jpg 
Views:	12 
Size:	45.0 KB 
ID:	48168

    Oddly, if I remember correctly, this is where Access stumped me 20 years ago, when I first thought about porting the application.
    I admit to not knowing SQL well, but I can't see how the relationship designer makes such a mess out of a simple self-join, especially after 20 years.
    But, maybe I'm doing it all wrong?

  8. #23
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    Using all this in lookup forms

    Ooops, I just saw a small error in the caption for column 1, it should be L3 (not L2) for level three of the hierarchy.
    When creating a lookup form, you would only look at level one (of the tblCustDesc) for the appropriate type of information you're seeking.
    What I need to do is figure out how to pass a condition of GrpLvl =1 and FgnSrc = "Perlty" when initiating a foreign table lookup in a Person master form for the field Perlty.

    I'm trying to come up with a nice simple design where a user, when in the frmPers at the field for personality on a new record, can lookup the personality codes. Then if it's not there they can start to enter the data for the new personality in ONE FORM THAT WORKS AGAINST THE tblUserDesc. They should be able to create the parent items too, if necessary. When they return to the frmPers the ID they created is there. I.E. frmTblPers>to lu query>to selection form>to another form for entering the UserData if needed.

    When I was a system support analyst at McDonnel Douglas Computer Division, I had the chance to see a lot of applications. Since then I've seen a few in Access too. What I often see is a hodgepodge of forms that baffle the user as to how they work. I want to avoid that. Adding foreign key data for a new lookup field of UserData descriptions should always work in the same manner. IMHO.

    Here is selecting for just the first level as just described, and the lookup form only has to display columns 2, 5, 1 in that order:

    Click image for larger version. 

Name:	Sample3_8.jpg 
Views:	13 
Size:	29.8 KB 
ID:	48170

  9. #24
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I don’t allow null values
    in that case you populate the parenFK with the personPK - so you identify the top of the pile by comparing the 2 - so rather than finding the top of the pile with with parentFK is null, you use personPK=parentFK
    now these tables started showing up! Look at the top of the image.
    they appear by default. Don't know why but suspect it is for the benefit of users who use the navigation form facility (I don't, too restricting). Just remove them (select then hit delete, or right click and select Hide Table). The fact they are not displayed in the relationship window does not remove the relationship

    To open a table to make changes to the fields, right click on the table and select Table Design. Saved changes will be immediately reflected in the relationship window. Once you are in table design you can then view and add data to the table.

    I'm trying to come up with a nice simple design where a user, when in the frmPers at the field for personality on a new record, can lookup the personality codes. Then if it's not there they can start to enter the data for the new personality in ONE FORM THAT WORKS AGAINST THE tblUserDesc. They should be able to create the parent items too, if necessary. When they return to the frmPers the ID they created is there. I.E. frmTblPers>to lu query>to selection form>to another form for entering the UserData if needed.
    On a form, use a combo for personality, combos have an event 'not in list' which can be used to open the form that controls creation of new personalities. That form should also allow for creation of personalities. The code that opens that form should also requery the the control to update the combo list with the newly added/amended/deleted records

    When I say “Relationship, I’m referring to the Database tools>Relationships graphical designer and what it creates. When I say query table links, I mean the ones created in the top half of the query design view.
    but I can't see how the relationship designer makes such a mess out of a simple self-join
    You just need to be very clear that a relationship is not a join - but they use joins as part of the relationship, you have two interfaces showing different things - but in a similar way. when you open the query window, it's only interpreting the joins, which for complex relationships can present issues

    As an aside, I started off with using UCSD Pascal many years ago. In tables you could have a variant datatype, even a variant field group. This worked well for a 'one table lookup' as the looked up value might be text, numeric, date, whatever so you could just lookup something without having to worrying about converting type etc. But this is not available in Access, SQL Server and the like. Took me a while to unlearn and relearn

  10. #25
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Quote Originally Posted by Ajax View Post
    [COLOR=#333333] i
    On a form, use a combo for personality, combos have an event 'not in list' which can be used to open the form that controls creation of new personalities. That form should also allow for creation of personalities. The code that opens that form should also requery the the control to update the combo list with the newly added/amended/deleted records


    You just need to be very clear that a relationship is not a join - but they use joins as part of the relationship, you have two interfaces showing different things - but in a similar way. when you open the query window, it's only interpreting the joins, which for complex relationships can present issues
    Again thanks for all your insights. I'm working this out to know how the Relationship affects the join, trying to find the sweet spot that works. Obviously, I haven't yet found it for self-joins.

    I'm going to avoid the use of combo boxes in look ups. They are too limiting. Why not, when in the Person form and in the personality textbox, have a command button on the lookup form that allows for viewing only as default, but then editing if needed? Then it brings up the supporting form for UserDesc. And the UserDesc form can then use the same lookup form for finding a parent, and then adding it and it's parent, and on goes the cycle n levels deep.

  11. #26
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Not sure I understand what your concern is about combos and I thought the idea was to have few forms.

    Anyway, good luck with your project, will be interested to see what you come with as a final solution

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 03-16-2016, 10:11 AM
  2. Update in Simple Join Query
    By drunkenneo in forum Queries
    Replies: 3
    Last Post: 11-21-2013, 11:35 AM
  3. Replies: 6
    Last Post: 11-19-2013, 01:38 AM
  4. inner join query with three tables.
    By fabiobarreto10 in forum Queries
    Replies: 2
    Last Post: 01-30-2012, 07:34 PM
  5. Replies: 9
    Last Post: 01-21-2011, 03:57 PM

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