473,320 Members | 1,939 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Relationships in Access

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
Feb 19 '08 #1
4 1158
Stewart Ross
2,545 Expert Mod 2GB
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 ContractorTrades, and ContractorTrades 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
Feb 19 '08 #2
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 ContractorTrades, and ContractorTrades 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
Feb 20 '08 #3
jaxjagfan
254 Expert 100+
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:

Expand|Select|Wrap|Line Numbers
  1. Select qryEmployeeTrades.*
  2. Union
  3. Select qryContractorTrades.*
  4.  
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
Feb 20 '08 #4
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:

Expand|Select|Wrap|Line Numbers
  1. Select qryEmployeeTrades.*
  2. Union
  3. Select qryContractorTrades.*
  4.  
Each of the queries could have its own criteria but each must return the same number of columns with the same datatypes.
Feb 20 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

20
by: Ed | last post by:
I am running Access 2002 and just ran the built in Access wizard for splitting a database into a back end (with tables) and front end (with queries, forms, modules, etc.). After running the...
49
by: Mal | last post by:
Hi, As I gain knowledge through a lot of trial, error, and usenet posts.. I have a potentially odd question. I am using a commercial access application. It is a front-end / back...
2
by: Max | last post by:
Hi. I really hope someone can help me. Going slowly insane with this problem. I have a two Access 2000 databases. One is the backend containing tables and some admin queries. The other is the...
3
by: paulwilliamsonremove | last post by:
Hi, I have been manually setting up relationships in Access 2003. I received an error message when leaving a form that told me the record could not be saved because I had to have a related...
7
by: davegb | last post by:
I'm totally new to relational database design. My boss has asked me to create a database of information on the employees in our group. Seemed to me like a simple application to learn the ropes. A...
10
by: Dixie | last post by:
I need to delete some relationships in code. How do I know what the names of those relationships are?
45
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see...
5
by: MightyAC | last post by:
Corrupt data in an access database seems to occur far too often. What's worse is that compacting an access 2000 or later db with corrupt data can result in the destruction of relationships. It...
13
by: ARC | last post by:
Hello all, Prior to going live with my app, I have questions on relationships theory. My prior app was done in Access 97, and I did NOT use relationships at all. I have 65 tables in my...
8
by: Phil Stanton | last post by:
I have a FE, BE database on my home computer and a duplicate on the office computer. Both use Ak2 on Windows XP. The one at home appears normal. The BE consists of about 50 tables with numerous...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.