Inner SQL an Table Setup - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Inner SQL an Table Setup

Adrian Connolly

9 months ago

Hi

I have for tables in which i need to Make relationships between business contacts, and the relationship to the individual contacts. Unfortunately some businesses can act as a trustee example; company can be a trustee of the trust. I need to show this relationship cannot show it in a format that have below to my present understanding.

And in the individual contact table that shows the relationship of what the individual is to the business table. An employee can have several positions over the lifetime of working for the company. I need to clarify this information. Keep it up-to-date.

Also as with the business I need to know which individuals are related to other individuals within that table, and when it started and ended. Example spouses, children

DbtBizContacts

This particular database, has businesses structures superannuation structures of a client, example partnerships, companies, corporate trust structures, trusts, self managed super funds and So on. With their tax file number, and other details

DbtBizName

This table is linked to the business contacts, is 1 of those businesses could trade under several names, example ABC Pty LT D to trade as agricultural manufacturer of machinery, and another business use of machinery and contract basis. So this is a one to many

dbtIsCorpRelationship

This table represents many to many relationship, with dbtBizContacts & dbtIndivContacts acted as a conduit. And can indicate the date of the relationship and when it ended. I type relationship, e.g. has trustee, or is trustee.

DbtIndivContacts

Individual contacts is a table that has, an individual who can be an employee of a company, director, sole trader connected to the business contacts through dbtIsCorpRelationship. This table is one to many

So I would appreciate if someone can help me on this predicament, and how to group of the groups so I can get a table to show on a form the following.

Enterprise Family Group

Company Pty Ltd

Cpt Kirk - Director

Richard Rost @Reply

9 months ago

It looks like you're on the right track. The easiest way to see this clearly is to map it out visually, like I do in my videos. I recommend creating an Excel diagram with all your key tables and fields, then draw lines to show how they connect. Mark whether each is one-to-one, one-to-many, or many-to-many.

You've basically got entities (businesses and individuals) and then relationships between them. If a relationship is one-to-one or one-to-many, you can use a direct link. If it's many-to-many (for example, a person can be a director for multiple businesses, and a business can have multiple directors), you'll need a junction table like your dbtIsCorpRelationship.

Once you've got that diagram, post it here and we can walk through the joins and grouping together. That will make it much easier to figure out how to display something like your "Enterprise Family Group" example.

In case you haven't watched these yet:
Many to Many
Self Join Relationships

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View