Query for this simple join between two tables not displaying.

[twgonder]

I have Office Pro Plus 2021 ver. 2205 on Windows 10.

I've been playing with relationships between two tables,
and then trying to get a query to work.


When I open in design in qry_Dt_PersChar the one to many shows from the relationship.
Adding the fields and then running it returns nothing in the last three columns.

I've done similar queries without the relationship between the files, and they run fine.
What am I not understanding?

dbOneLuTable1.accdb

[pbaldy]

In order for a single field in one table to join to two fields in another, the same value would have to exist in both fields. You want a single join between Perity and the ID field in UserDesc, with a second single join between RelSts and a second instance of the UserDesc table.

[pbaldy]

To be honest I don't really understand how the data in the UserDesc table relates to the other. Maybe describe your intentions in words?

[twgonder]

To be honest I don't really understand how the data in the UserDesc table relates to the other. Maybe describe your intentions in words?

I guess a picture wasn’t worth a thousand words.
I tried to dummy down this thread: https://www.accessforums.net/showthread.php?t=86146

I’m loathe to create a bunch of tables just for information like descriptions and hierarchies.
This db is just a test of how to get things done in one lookup table. I tried to make the data somewhat small and logical.

The tblu_Dt_UserDesc (User Descriptions) is a table that allows for multiple lookups all in one place. Notice that it also handles hierarchies to help with selection in lookups. With luck I will be able to create forms that allow a table like tbl_Dt_Pers (persons) to enter and validate and even add new data from the specific main form without using ugly combo boxes. In this case, the lookup of personality and relationship status for the persons form (when created). With real luck, it will be one query and form that can serve a variety of “master forms”. In this example it’s for personality, relationship status, building type (this isn’t added yet)

I was able to get what I want in this second sample DbOneLuTable2.accdb, by removing the relationship between tbl_Dt_Pers and tblu_Dt_UserDesc, and then creating joins in the query instead. Notice I can see a lot of data. But I lost referential integrity. So how can I have both?

I would also like to test adding the tbl_Dt_PersBldg data to the query to get a report. But Access really freaked when I tried to do that with the relationships existing.

dbOneLuTable2.accdb

[twgonder]

In order for a single field in one table to join to two fields in another, the same value would have to exist in both fields. You want a single join between Perity and the ID field in UserDesc, with a second single join between RelSts and a second instance of the UserDesc table.

In my first sample that's what I wanted to do, but when there is an existing relationship, the two relationships in the query are shown by default. I don't see how to change that.

[CJ_London]

relationships will map to the initial query but not correctly when two fields in one table map to one field in another. You need to have an instance of the lookup table for each of the two fields - this sql should work for you

SELECT tbl_Dt_Pers.ID, tbl_Dt_Pers.Nm1, tbl_Dt_Pers.Perlty, tblu_Dt_UserDesc.ID, tblu_Dt_UserDesc.GrpLvl, tblu_Dt_UserDesc_1.DescL
FROM (tblu_Dt_UserDesc RIGHT JOIN (tbl_Dt_Pers INNER JOIN tbl_Dt_PersBldg ON tbl_Dt_Pers.ID = tbl_Dt_PersBldg.PersID) ON tblu_Dt_UserDesc.ID = tbl_Dt_Pers.Perlty) LEFT JOIN tblu_Dt_UserDesc AS tblu_Dt_UserDesc_1 ON tbl_Dt_Pers.Perlty = tblu_Dt_UserDesc_1.ID;

[twgonder]

relationships will map to the initial query but not correctly when two fields in one table map to one field in another. You need to have an instance of the lookup table for each of the two fields - this sql should work for you

Thank you, that will give me something to study. Did you create that SQL from within the Design View, or write it out by hand?
I'm assuming you didn't get rid of the relationships in the Database Tools for the original tables?

[CJ_London]

In the design view, just drag a table onto the query - note it aliases with a _1,suffix

Didn’t touch the relationships, but for the record there would need to be a very very strong reason for me to keep all lookups in one table. This isn’t one of them and right now can’t think of a single example where doing so would outweigh the benefits of not doing so

I'll use an analogy - best way to get across the atlantic? cruise ship or row boat? The only reason you would choose row boat is for another reason such as doing it for charity. Yes you can do it, but boy, is it hard work.

[twgonder]

I'll use an analogy - best way to get across the atlantic? cruise ship or row boat? The only reason you would choose row boat is for another reason such as doing it for charity. Yes you can do it, but boy, is it hard work.

My analogy is that I'm trying to create just one jet-airliner that can make several trips, hauling several loads of people across the Atlantic instead of dedicating one cruise ship for each passenger and having to have several cruise ships, or row boats.

[twgonder]

To be honest I don't really understand how the data in the UserDesc table relates to the other. Maybe describe your intentions in words?

Thanks for forcing me to explain what I’m doing here. As I mentioned in another thread, I’ve just played around in Access. Now I’m trying to get more serious for a big application conversion to Access.

The old system I used was PICK, it didn’t have inherent relationships and integrity. It gave you the ability to create files (tables), create dictionary items (records) and data items (records) [an inherent two-level file structure]. It also gave you a pretty advanced BASIC designed for the OS and the created files. There was an “English” based retrieval language that could build reports based on the dictionary or create a list of ids (keys) to feed to a BASIC program.

In short, we created files, dictionary items and got down to work programming in BASIC. Not a lot of need to “design” relationships as in Access and more formal methodologies. I’m unlearning old tricks and of course, learning new.

So, in my new way of thinking, this is what I was trying to accomplish with this exercise.



The first example can be explained in this non db manner:
Bob is one of many persons.
Bob has a personality which is Nice
Nice is just one of many personality types.
The personality type of Nice belongs to a parent group of Not used.
The parent group of Not used has many different personality types.
The Access table of User Data contains the descriptions for Personality types and their parent types.

The Access table of User Data also contains descriptions for Relationship Status.
Bob has a Relationship Status of Single.
Single is just one of many Relationship Status.
…

I’m guessing you could call it a many to one to one to many design.
I had these all over my old application, although we didn’t call it that.

I’m open to suggestions and especially feedback as to if this is a reasonable design in Access?
Or will I paint myself into a corner?

[CJ_London]

relational databases are about entities and how they interact. Don't understand your concept of 'not used' as a parent

I would have thought People, personalityType and personalityStatus are your entities

tblPeople
PeoplePK
PersonName e.g.Bob

tblpersonalityTypes
PersonalityTypePK
PersonalityTypeName e.g. Marital, Health, state of mind

tblPersonalityStatus
PersonalityStatusPK
PersonalityTypeFK
PersonalityStatusName e.g. Single, Nice, Widowed

tblPeopleStatus
PeopleStatusPK
PeopleFK
PersonalityStatusFK

PK's link to the FK's

In a form for tblPeopeStatus you would use a technique called cascading combo to first select the personalityType which then limits the options for personality status

you can assign many personality types to people and many personalitystatus to personality type

[twgonder]

Or I totally messed up in my analysis, and instead of the middle one-to-one, I should just make a one_to_many from the second UD back to the first UD table in the diagram? Aggghhhh!

[mike60smart]

Hi
I would just go with the following:-

tblPeople
PeoplePK
PersonFirstName e.g.Bob
PersonSurname e.g.Jones
PersonalityID
StatusID

tblpersonalityTypes
PersonalityTypePK
PersonalityTypeName e.g. Nice, Very Nice, Grumpy

tblStatus
StatusPK
Status e.g. Single, Married, Widowed

PK's link to the FK's

[twgonder]

relational databases are about entities and how they interact. Don't understand your concept of 'not used' as a parent

I would have thought People, personalityType and personalityStatus are your entities

I don't think of all of those as entities, or if they have attributes more than just a Description all the attributes are the same, and can be duplicated in one Description Entity table with good design.

Imagine you have a sales application where a salesman has a boss, that has a boss, that has a boss, etc. but you have no idea for any organization how deep that hierarchy goes. But each level in that hierarchy has the same attributes (fields) of name, address, phone, etc. 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?

As to another point you made about why have Not used and why it’s there, look at tblu_Dt_UserDesc for the BldgTyp and see how it has a hierarchy of descriptions. “Not used” just means that level isn’t used and there aren’t any higher. When you reach the top level of the hierarchy, you just have it point back to itself as its parent.

So, I think I’m zeroing in on this solution. Take a look at the Relationships in this sample in this third iteration of the db. When I at first created the relationships, I make the links as I thought best, using the last columns of tables. I got the idea from another post in another thread where a self-join was suggested.




After saving and reopening Relationships, Access changed it to the mess there is there now.




My main question is if I am doing it correctly?
What is Access doing to my original relationship design?
Can tables be split in the diagraming, like for the Person and Person Building tables and then the User Description relationships? This would make diagraming much simpler in very large and complex application designs.

dbOneLuTable3.accdb

[pbaldy]

Here's a discussion on the "one lookup table" method:

https://oracle-base.com/articles/mis...up-tables-otlt

Their conclusion, it isn't worth it.