Multiple Joins to a Single Table - MsAccess

The are some scenarios in #relational #database design where you need to design multiple independent #foreign-key relations between just two #tables. When you want to create a #query to retrieve the data from these tables, you must create multiple #joins to a single table.
Sounds complicated? It becomes much less complicated once we look at a concrete example of this scenario in Microsoft #Access in this video.

[@josephpatrick5381]

Thanks. New to MS Access and VBA. Expecting more such videos please.

[@tomseed2000]

Awesome video. Never even thought of that scenario, but now if I need it, I know how to handle it. Thanks.

[@paulgraunke6670]

Informative, useful. But I wish the video would have displayed the underlying SQL code generated by the design interface AFTER implementing the solution to compare with the underlying SQL code generated by the design interface BEFORE implementing the solution.

[@codekabinettcomen]

Thank you for the suggestion. I'll try to keep in mind to show such before/after comparisons in similar future videos.

[@crtune]

Yes, definitely. This is the basic process that brought me gradually to the world of writing complex, and useful SQL scripts, Stored Procedures and in general developing SQL for business work. Once you look at enough SQL it is actually easier than working within the GUI of MS Access. I have done extensive Access projects, but typically used both SQL writing and the GUI tools to achieve the building of the database.

[@NTSoftware]

hello , i from Viet Nam

[@IamDJalfi]

Hi Philip, I am watching your videos about Access and VBA, and currently, I am making an application using MS Access in my company to hold records and analyze them. However, I am wondering if it's worth it. With new features like PowerApps, do you know if MS Access will have long-term support, or is the time of MS Access coming to an end? Thank you for your insights.

[@codekabinettcomen]

There should be a new non-subscription release of Office/Access later this year, which will have support until 2029. So, if your application will be used in a desktop environment, Access is still a good choice. If you want to support mobile users, PowerApps or another web based technology will be better.

[@crtune]

Like the host says here there probably is plenty of support. And Access is not bad at all, assuming you do not have to scale up too far. If you think your tool is going to house large numbers of records, or you may wind up with really large table structures, and large numbers of fields, then regular SQL engines are not bad, and they all can be brought into a backend role behind something like Excel using Power Query. You could use something like Postgres SQL which can handled millions of records, and which is supported in an ongoing way for the foreseeable future. If, for some reason, Postgres went away, there are other ANSI SQL databases which could be used.

[@codekabinettcomen]

Access can already handle millions of records if the table and query design is clean and efficient. PostgreSQL and similar systems are basically only limited by the available hardware resources.

[@mikefochtman7164]

Thanks. I do wish though that you took a moment to show the SQL View, just so we could see what the designer had created. I like to use SQL View a lot because it helps my understanding.

[@codekabinettcomen]

Thank you for your feedback. This was mentioned before and I agree that this would have been helpful. I will try to remember showing the SQL view in similar future videos.

[@JANDERSO5554]

Holy crap, I've been writing programs with Access since Access 95, and I never knew this.

[@crtune]

My Instinct has been to use SQL script, which I deem to be actually quite easy to understand. I would have simply written the SQL using "OR" key words in the JOIN portion and saved it, after testing. It is also good to be aware of FULL JOIN, OUTER JOIN. I think SQL is highly comprehensible having been designed from the beginning to be straightforward.

[@codekabinettcomen]

Using OR in the join to just one instance of the person table may give you duplicate results and the result will not show which person acted in which role.