Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    One table to create a hierarchy instead of many tables. How to create “link” logic?


    Here is a little puzzler that's been in my mind for years. I don't do much with Access, other than poke around and look at samples.
    I've never seen anything like my solution (in an old super-mini database application) in Access.

    The idea is to create one table that can replace dozens or hundreds of sub tables to a primary table.
    By primary table I mean something like Customer, which might have a sub table of Territory (which is actually one level up the hierarchy).
    Much is explained in the two files attached in .zip file.
    Thanks in advance if you can find a simple and elegant solution within Access or with code.

    GrpTest1.zip

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Interesting! Have seen many attempts to convince people "y'ought to have 1 all encompassing lookup table", but always come back to Joe Celkco and others for an explanation and contrary position.


    If you are referring to a table with self-joins, that is a different story and often used to represent and process hierarchies.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I learned my database stuff using UCSD pascal which had a variant data type and you could also group specified data types into variant datasets - the space taken on storage was based on the largest defined dataset. It was useful for lookups but also for things like product specifications which could vary from one product type to another.

    You still needed a ‘key’ field to indicate what type of variant it was - which could be stored in the variant group.

    Took me a while to unlearn that way of doing things - you can mock it, have redundant fields or multiple tables without referential integrity but not quite the same

  4. #4
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Quote Originally Posted by orange View Post
    Interesting! Have seen many attempts to convince people "y'ought to have 1 all encompassing lookup table", but always come back to Joe Celkco and others for an explanation and contrary position.


    If you are referring to a table with self-joins, that is a different story and often used to represent and process hierarchies.
    Thanks for the insights.

    “Have seen many attempts to convince people "y'ought to have 1 all encompassing lookup table…”
    I won’t pretend to understand all of Joe’s ideas, because I didn’t work with SQL, but the query language was similar in construct. I also didn’t mean for the table to work across all applications, like payroll and sales, etc. So, the size wasn’t all that big. The database was very good at not wasting disk or memory space with full of null large fields. I organized the fields in probability of use to minimize field delimiters which separated the data in a record. As I understand it, Access also uses variable field lengths in the storage of the data, so a 5-byte data will use 5 bytes even if the maximum field size is 40. The keys were always text, so I didn’t have to deal with number conversions. The database I used didn’t have to pull up an entire join table construct, it was made to go straight to the data you wanted with polish styled code in a dictionary record, so if you went three levels deep, you only read three records. Which brings me to my concern about how to do this in Access, or if it’s even feasible in an efficient manner.
    My old Group design allowed for 5 groups in the primary table and three in each level. It then went 5 levels deep. That would mean 1,815 tables for each primary table, or do an ungodly join as shown in the horse example). If you have ten primary files in an application… you get the idea. It wasn’t uncommon for a client to have 5,000+ records in the Group table.
    My next step would be to create an application for multiple countries, languages and clients using this same design technique. One table to hold the prompts, headings and helps for forms and reports in the one application. So, when a user gets into a form, they might see Territory, or District, or Distrito or even bugabuga (or “Not used”) in the text-box prompt for a given field. I’ll be adding a thread on how to do this later. I’m waiting in another thread to see if Access has a methodology to support multiple languages in an application.

    "If you are referring to a table with self-joins,…"
    Yes, my design is more like the self-join concept. Except my table isn’t horse to horse to horse. It’s Customer to group to group to group. However, the Sire and Dam analogy is basically my generic Grp1 Grp2 fields, just without a descriptive field name. In my old database applications, which I’m thinking about redesigning in Access, a simple query/report command might look something like this:
    Sort CustB by Desc ID Desc Grp1 Group$Grp1$Grp1$Grp1.Desc
    And you would get a row for each customer similar to this in my example:
    3, c1, CG11, csgs1

    6, c4, CG24, csgs2
    The little piece of programming code that sits behind the pseudo field for a table query (Group$Grp1$Grp1$Grp1.Desc) does this, get Grp1 from CustB, get the record from the Group table with the unique Primary key of {"CustB",1,0,0,Grp1}, get the Grp1 data modify the primary key (and read the new Primary key record from Group… repeat two more times), return the Desc. Bang, a little code was run, the data was returned, three additional records were read from disk, no big table relationships were built, the query didn’t overrun your 256K memory shared by ten users, etc.

    Using my CustA example, the SQL would look something like this to get the same result:
    SELECT CustA.ID, CustA.Desc, CustA.Grp1, CustA.Grp2, CustAGrp1Grp1Grp1.Desc
    FROM CustAGrp2Grp1 INNER JOIN (CustAGrp2 INNER JOIN (CustAGrp1Grp2 INNER JOIN (CustAGrp1Grp1Grp1 INNER JOIN (CustAGrp1Grp1 INNER JOIN (CustAGrp1 INNER JOIN CustA ON CustAGrp1.[ID] = CustA.[Grp1]) ON CustAGrp1Grp1.ID = CustAGrp1.Grp1) ON CustAGrp1Grp1Grp1.ID = CustAGrp1Grp1.Grp1) ON CustAGrp1Grp2.ID = CustAGrp1.Grp2) ON CustAGrp2.ID = CustA.Grp2) ON CustAGrp2Grp1.ID = CustAGrp2.Grp1
    ORDER BY CustA.Desc;

    Good grief, who needs all that work and or iterative self joins? I think my example is kind of a hybrid between the two approaches.

    What I don’t know is if Access and it’s SQL allows for a pseudo field in a query (based on my CustB and Group example) that passes a set of parameters that then returns one value. In a query it might be triggered by something that looks like this:

    Click image for larger version. 

Name:	Query1.jpg 
Views:	52 
Size:	62.0 KB 
ID:	48081

    Then the question is what code to write, where to put it and how to trigger it with the something similar to the parameters/method shown above.

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

    Just a little more to consider

    A few more notes on my long (forgive me please) explanation.

    A real world problem, let's say we create a Group file and it self joins. This means that the key has to be unique, and data is only for one file, not Customer and Product.
    On the East Cost, a regional manager names his first territory T1. The area manager uses the same T1 on the West coast for his region, which at one time, long ago was a territory. Ooops duplicate keys in the same table (assuming you use the actual data codes and not AutoNumber), even though they are on different levels now.

    Imagine you create a form for input, and the user knows the code, so a combo box isn't displayed. But you want to show a description of the next two higher levels so the user can be sure they haven't made a entry mistake. This happens a few times on different fields in the form. We're going to create self joins , up two levels, over and over again just to display the data for a field entry (maybe there would be an easier way in Access, if there is then we should be able to use that in my query example for CustB/Groups)? If someone scrolls through multiple records by holding down the arrow key, what happens to performance? (I confess ignorance and that I might be all over the place when considering the different inner workings of Access in this.)

    Finally, I guess I should point out that my old db system only allowed one field to be the primary key (field 0), and it couldn't have duplicates. So, this made it easy to read from record to record without requiring queries to get the wanted data.

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think you are describing an Entity Attribute style of data storage.
    Generally they are cumbersome to use and require a lot of coded manipulation, because it's not really a normalised data structure.

    There is a reason SQL and other Databases are very strongly "data typed" and it's predominately for performance and ease of function.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have heard of the Pick Operating System but have never used it so have no familiarity.

    Perhaps Minty has identified the comparable/similar/closest construct in Access.

    Maybe someone else is familiar with the technique you describe and could comment on an approach using Access.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Seems you are looking for a one size fits all approach. The schema you are describing was to work within the limitations of old technology. In modern terms you would have two tables- customers and groups. The groups would relate to each other (it is called a recursive relationship) and the customer to a single group record. Recursive relationships can be handled within sql server etc but within access you need to use a recursive vba function

  9. #9
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Quote Originally Posted by Ajax View Post
    Seems you are looking for a one size fits all approach. The schema you are describing was to work within the limitations of old technology. In modern terms you would have two tables- customers and groups. The groups would relate to each other (it is called a recursive relationship) and the customer to a single group record. Recursive relationships can be handled within sql server etc but within access you need to use a recursive vba function
    I always try to design for a worst case, most complicated environment, if I'm going to commit a huge percentage of my time to a solution.
    Otherwise, I'll just go back out to the patio with the cats and a beer.

    So, after a nap I had an aha moment. I will try to implement the idea into the CustB & Group tables.
    The solution will depend on if I can create a second (other than primary key) multi-field index in a table and how.
    In the meantime, have you seen a good explanation/model of the recursive stuff in access?

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Really think it is time you provided some example data to illustrate what you are trying to achieve. I’m away from my computer at the moment but the scenario I’m suggesting is

    GroupPK… groupname.. groupparent
    1……………a……. Null
    2……….. b……………..1
    3…………….c…………. …2
    4……………..d…………….1
    5……………..e…………….1
    6………….. f…………. 3

    So a is top of the pile
    B,d & e are ‘owned’ by a
    C is owned by b
    F is owned by c

    For a recursive vba function, Google ‘vba recursive function’ plenty of examples exist.

    Be aware it will typically start to lose efficiency after around 80,000 records

    You would assign a customer to a group via the groupPK

    CustomerPK… customername…groupFK
    1………………..x…………. ……..2
    2………………..y………….. …. 6
    3………….. z………………….1

  12. #12
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    Quote Originally Posted by Ajax View Post
    Really think it is time you provided some example data to illustrate what you are trying to achieve. I’m away from my computer at the moment but the scenario I’m suggesting is
    I included a zip file in the first post to the thread of the db file. There's no code in it so it should be safe to open.
    I did back off and stop trying to put customer and product groups into the same table.
    I've been working on a redesign for the CustB and ProdB tables and their group tables.
    Just look at the CustA tables for now to see what I was originally trying to do. Best to just see the relationships.
    Now I just have to check the data and try the self joins with the composite index to see if it works.

    Last edited by twgonder; 06-21-2022 at 11:09 PM.

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

    The result of the original question

    After a few weeks of poking around in Access, reading lots of posts here and getting help on others of mine,
    here is what I was looking for.
    There are only two tables: 1) Department, 2)Group
    All the data resides in those two tables for this report.
    I don't think I bastardized any of the rules of a relational data base.
    And I could add a third group (without adding any additional fields or tables), that allows for three client defined modifications to my design of a Department.
    Notice how the sort field is used to create an organizational report (similar but easier to read and more complete than the typical org chart.)
    If you could see the top of the report, the entire "chain of command" to the board of directors is shown.
    This example is loosely based on the McDonnell Douglas McAuto Microdata division in the 80s.
    They built several models of super-minis and had sales branch and field service locations (the later not included).
    My thanks to those that helped make this effort worth the time.
    Attached Thumbnails Attached Thumbnails DepartmentHierarchyRpt01.jpg  
    Last edited by twgonder; 07-14-2022 at 08:05 AM.

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Would be interesting to see the actual database.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

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

    I will

    Quote Originally Posted by mike60smart View Post
    Would be interesting to see the actual database.
    I'm in the middle of some code mods/tests. When I finish, I'll post the db.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-26-2022, 05:59 PM
  2. check, create and link a table in BE?
    By Frits1969 in forum Access
    Replies: 1
    Last Post: 04-21-2016, 02:34 AM
  3. Replies: 1
    Last Post: 03-05-2013, 02:17 PM
  4. Replies: 4
    Last Post: 01-05-2013, 02:43 AM
  5. Create link table to .dbf that contains memo feald
    By vskarica in forum Import/Export Data
    Replies: 3
    Last Post: 10-06-2010, 12:08 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