Relationships in Access

[Coolboy55]

I have a Contractors table and an Employees table, and I would like to link them both to my Trades table, so that any given trade can have a list of both Employees and Contractors who have that trade assigned.

Is it possible to link the same field to two different tables? Or how might I get this functionality?

Thanks!

CB55

[Stewart Ross]

I have a Contractors table and an Employees table, and I would like to link them both to my Trades table, so that any given trade can have a list of both Employees and Contractors who have that trade assigned.

Is it possible to link the same field to two different tables? Or how might I get this functionality?

Thanks!

CB55

Hi CB55. Certainly possible to link the Contractors and the Employees to Trades, though not in one table. The relationship between trades and contractors, and trades and employees, is many-to-many. Each contractor can have more than one trade (plumbing, electrical, joinery say), and each trade is a feature of more than one contractor. Many-to-many's are resolved into two one-to-many relationships using an intermediate table - a linking table - which contains the key fields of both joined tables (contractor and trades, or employees and trades). The link table is joined to contractor so that there is a one-to-many between contractor and ContractorTrade s, and ContractorTrade s is joined to Trades as a many-to-one.

There is a good article about normalisation and table relationships in the forum, at Database Normalization and Table structures

I don't know what the key fields of your tables are, but producing linking tables is quite straightforward . Once done, you need to be able to add the data to them - and for end-users this can be done using a form-subform structure (adapting the contractor main form to include a trades subform, for instance).

-Stewart

[Coolboy55]

Hi Stewart,

Thanks for your response. I should probably clarify my goal to make sure that I'm following the right instructions. I have a Tasks table as well as a Tasks form. The Tasks form will be used to add new tasks and indicate the trades required to complete the tasks. I have a TradeID combobox controlled from the Trades table, and I'd like to be able to select a trade in this combobox and have a second combobox updated to show either employees or contractors that match this trade. There would be a checkbox that indicates whether the second combobox should list employees or if it should list contractors.

I will go ahead with your instructions below for the time being. Are there any other pointers you might be able to give to help me get the above functionality?

Thanks so much, your time is appreciated!

CB55

Hi CB55. Certainly possible to link the Contractors and the Employees to Trades, though not in one table. The relationship between trades and contractors, and trades and employees, is many-to-many. Each contractor can have more than one trade (plumbing, electrical, joinery say), and each trade is a feature of more than one contractor. Many-to-many's are resolved into two one-to-many relationships using an intermediate table - a linking table - which contains the key fields of both joined tables (contractor and trades, or employees and trades). The link table is joined to contractor so that there is a one-to-many between contractor and ContractorTrade s, and ContractorTrade s is joined to Trades as a many-to-one.

There is a good article about normalisation and table relationships in the forum, at http://www.thescripts.com/forum/thread585228.html

I don't know what the key fields of your tables are, but producing linking tables is quite straightforward . Once done, you need to be able to add the data to them - and for end-users this can be done using a form-subform structure (adapting the contractor main form to include a trades subform, for instance).

-Stewart

[jaxjagfan]

If you have 2 identical datasets the easiest way to get both into a single list is use a Union query. The datasource for your combo could be something like this:

Select qryEmployeeTrades.*
Union
Select qryContractorTrades.*

Each of the queries could have its own criteria but each must return the same number of columns with the same datatypes.

Hi Stewart,

Thanks for your response. I should probably clarify my goal to make sure that I'm following the right instructions. I have a Tasks table as well as a Tasks form. The Tasks form will be used to add new tasks and indicate the trades required to complete the tasks. I have a TradeID combobox controlled from the Trades table, and I'd like to be able to select a trade in this combobox and have a second combobox updated to show either employees or contractors that match this trade. There would be a checkbox that indicates whether the second combobox should list employees or if it should list contractors.

I will go ahead with your instructions below for the time being. Are there any other pointers you might be able to give to help me get the above functionality?

Thanks so much, your time is appreciated!

CB55

[Coolboy55]

Thank you very much! I didn't know it was possible to query from two tables like that. But now is there a way to decide whether to show employees or contractors in this combobox based on the status of a checkbox? I don't imagine I could use a WHERE clause...?

I'm fairly new to this... thanks for your patience! :)

CB55

If you have 2 identical datasets the easiest way to get both into a single list is use a Union query. The datasource for your combo could be something like this:

Select qryEmployeeTrades.*
Union
Select qryContractorTrades.*

Each of the queries could have its own criteria but each must return the same number of columns with the same datatypes.