Problem Setting Up Relationships

May 31, 2005

I have a table which contains a list of names and the trades that each one is qualified for and a second table for a project which requires a number of different trades to fill specific roles. How do I set up a proper relationship between the two tables.

Tbl_employee.
empl_ID| Empl_Name | Discipline
001 | Mathew | Construction Manager
002 | Mark | Safety officer
003 | Luke | Construction Manager
004 | John | Architect

Tbl_Proj.
Project_ID
Project_name
Project_Architect
Project Manager
Health & Safety

Each employee could have more than one role on each project, one role on each project, or no roles on a project.

I have a form that shows a complete record for each project and permits me to select who fills what roles from a combo box for each role.

Using the relationship editor I try to create relationships between the following:

Discipline - Project Manager
Discipline - Project_Architect
Discipline - Health & Safety

I then set up a SELECT in the rowsource of each combo box on the form WHERE the Discipline is whichever is required by the combobox

The raltionship editor allows the creation of a relationship between Discipline and the (multiple) various roles on the project, but if I try to 'Enforce Referential Integrity' It refuses to work.

Do I need 'Referential Integrity' and if so How do I make it work?

Thx,
Kev.

View Replies


ADVERTISEMENT

Setting Up Database - Need Help With Relationships Between Tables

Sep 9, 2007

Hi,

I would like to create a small HR database that holds:

* Employee details (Name, Date of birth, Entry Date, Exit Date, Function,Department, Shift)
* Departments
* Departments and capabilities needed
* Employee, departments and capability achieved (1=yes, 0=no)

The idea would be to have a link between the employee the departments capabilities I can tick fields of the capabilities they have or have achieved.

When I have this I could run a report that shows a score per employee
For example:

Name: Department: Orderpicking: Packing: Cutting:

John Warehouse 1 1 0

As the tasks / capabilities required are different per department you would see something different if you would have user Jane that works in accounting


Is there anyone that can give me some hints so that I can get any further??

For the moment I have 3 tables:

1. Employee information - John, Jane
2. Departments - Warehouse, Accounting
3. Department tasks - Department, Cap1, Cap2, Cap3, Cap4

View 7 Replies View Related

Screwed Up Relationships , The LinkMasterFields Property Setting Has Produced This Er

Aug 16, 2007

I dont' know what happend but now when i tried to add something to one of my subforms i get this error
The LinkMasterFields property setting has produced this error:'The object doesn't contain the Automation object'Claims Header."
i had it working not sure when it got screwed up.
i'm freaking out. i have to get this working correctly.

EDIT:
just to make sure it's ok to do this.
my subform is running from a table that has my uid from my main form claim_id and i have it linked from those fields.
currently the claim_id i'm looking to add to isn't in the table that my subform runs off of.

View 3 Replies View Related

Help With "Join Types" When Setting Up Relationships

Apr 26, 2006

Ok here goes another Newbie question, I've been doing a lot of reading on the web and in books about relationships and setting them. But when I go to Access and set the relationships, theres a button called "Join Types". When clicked it brings up the "Join Properties" window with these three options in it:

1. Only Include rows where the joined fields from both tables are equal.

2. Include ALL records from "Company" and only those records from "Customer" where the joined fields are equal.

3. Include ALL records from "Customer" and only those from "Company" where the joined fields are equal.

I think im having trouble understanding what exactly these are refering to. The terms I think are throwing me off. If I had a table called "Company" with all the common info in it like name, address and such, What exactly is the "ROW" in that table, and the "RECORDS" of the table.

I think I need clarification on this so I can properly identify them, also if its possible could somebody supply an example for when I would use each join type? Your help is much appreciated. Thanks! ~[MikE]~

View 2 Replies View Related

Tables :: Setting Up Multiple Tables / Relationships

Dec 10, 2013

I have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).

The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.

I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.

View 3 Replies View Related

Help With Relationships

Jul 18, 2005

Hi all. I really need help with this. Does anyone see a problem with my relationships , i have attached a copy.
Any help will be greatly appreciated
ClaireB

View 5 Replies View Related

Many-to-Many Relationships

Aug 2, 2005

I am new to database design and curious about relationships. In a hypothetical situation, if I have four tables: tblProducts, tblOrders, tblEmployees, and tblCustomers, how could the relationship be set up?

The Products are stored to keep track of the quantity on hand and the employees will also need to be tracked as to how many products they sell. Customers are recorded and Orders wrap up all three tables with the fields: OrderID, ProductID, EmployeeID, and CustomerID.

Would it make sense to say that many products can be bought by many customers and/or many employees can place many orders.

View 10 Replies View Related

Relationships - One-to-many?

Aug 16, 2005

Hi

I've selected a number of tables to the relationship window. When I drag the primary key on one table to the corresponding foreign key on another table, the link created is one-to-one. I want one-to-many. Why did it create one-to-one? How do I change this to one-to-many?

Thanks

View 4 Replies View Related

One-to-Many Relationships

Mar 7, 2006

Hi all. I am new to the use of access and have been told that I need to create a one-to-many relationship to be able to have several records from a drop down list saved to one unique number (my primary key).

Is this correct and if so how do I do this? If it is not correct what is the best way to do what I require?

Thanks for helping.

View 6 Replies View Related

Relationships

Mar 16, 2006

Hi

I have created two tables which are linked with a one-to-many relationship

Using a form the user enters a date. Then in VB I add a value to the date and store this in the second table.

I have simplified this to try to find out what was going wrong and found that when the database adds a value to the second table which only has two fields it first of all adds it to the begining of the record set and the id field does not update to that of the first table where the relationship is joined.

Do I just captutre the value of the id field and add this to the recordset.
How to I get the database to look foir the end of record set before I appent the new generated record.

View 4 Replies View Related

Relationships

Aug 7, 2006

Hi,

I have looked for this on the help section; however, it does not seem to be covered. How would you change a one-to-many relationship to one-to-one? Access seems to automatically guess that the many side is where the foreign key.

Can it be changed from Access or is entirely on how you design your tables?

Thanks,
Bee

View 5 Replies View Related

Help With Relationships Plz

Aug 18, 2006

Hi !!
I am doing a school project on a school database. In my DB I have a table with all students records (eg. Student ID, Name, Surname, DOB, class etc) and I have another table where the student fees payment slips will be printed.
The fee table will only have the ID, name, surname and class fields and fee will be input for every student for every term.

I want the fee table to get all the info except the fee from the first table automatically when I give a student ID.

I have created a one to many with only Student ID fields linked, NOTHING.

Then I tried to link many fields on each side (eg Name to Name, Surname to Surname etc) but I got a msg "No unique index found for the referenced field in the primary table"

Plz help me !!
Thanks

View 1 Replies View Related

No Relationships!

Oct 10, 2006

would having a db with several tables and NOT setting any relationships up in the relationships window affect the db in any way?

i have such a db, which originally had all the relationships set etc. however, as i encountered errors and had to replace tables and delete entries, i must have at some point deleted all the relationships.

i just relaised that the db now has no relatioships set up (all the primary keys are still in the tables) so i'm wondering if this matters?

cheers
Jose

View 3 Replies View Related

Relationships

Nov 9, 2006

I'm trying to define relationships between a few tables in Access 2003 and It's only doing a one to one. How do I get it to do a one to many?

View 5 Replies View Related

Relationships

Nov 23, 2006

Please someone help! This is very urgent!

I am designing a database for a clinic

basically,

One Form is a Demographic Form, which was been designed. Each person was given an identifier code.

However, they all did 3 questionnaires. Each one of them has their ID code on it.

Is it possible for me to make a query so that when I search ID code, it actually brings up all the information on every questionnaire?

View 8 Replies View Related

Relationships

Jan 1, 2007

If I have a table "tblContinents" with just two fields ContinentID and Continent and I have another table called "tblCountries" with three fields CountryID, Country and Continent.

After reading several articles, i decided not to make the Continent field in the tblCountries as a lookup field but rather I will do that at form level via a combo box. In such situation, since i am not creating a lookup field at table level, do you think that it is necessary to create the relationship between the two tables in the "relationship window", or shall i just leave it as it is, and if the need arises i will create such "relationships" in queries?

Thanks very much.

View 3 Replies View Related

Help With My Db Please? - Relationships

Jun 19, 2007

hi, im new here and i dont really know much about databases with access. im currently doing an assignment at school requiring me to do a entity relationship diagram for all my tables, which i do not know how to do. Can anyone please assist me asap? thank you very much in advance

below is the link where i uploaded it because it was too big to be uploaded onto this post. :)

http://www.megaupload.com/?d=E6AU28CL

View 4 Replies View Related

Relationships

Jul 4, 2007

In my Db I have TblQuotes & TblQuoteItems

I want the one-to-many relationship to be based on my own generated quote reference rather than the PK/FK.

My quote reference looks something like this IN123/10

Does the character '/' cause a problem with creating the relationship as I get the error message "No unique index found" even though I know that they exist?

Thanks

View 6 Replies View Related

Where Are My Relationships?

Aug 10, 2007

When I view the relationships window, not one of the relationship connector lines (whatever the technical term is) do not show up! I click view all relationships, view direct relationships. I restarted my computer, restarted Access. Nothing!

I know I have relationships because when I am table design view I try to change something that I know has a relationship and it doesn't let me because it says I have a relationship.

What is going on!?:mad:

View 4 Replies View Related

Help With Relationships

Jan 31, 2008

Hello..

Here is what I have. The database is made up of three tables (see below and see attachment). I have it set as one event to many ethnicity's and many personnel with the event ID as the PK in all three. Did I do this correctly? I thought I did and I had a form with each one of these running correclty but now it is not working.

tblEvent
EventID(PK)

tbleventEthnicity
EventID (FK)

tblPersonnel
EventID(FK)

See attachment for the other fields.

Can someone just check to see if I set this up correctly or what I should do differently?

Thanks

View 14 Replies View Related

Relationships, 2 In One?

Nov 2, 2004

I have already made a relationship 1 to many, i want to add another relationship. I put the name of the feilds i want to join up in the drop down box. I put "enforce in..." and "Casgade update related fields" on and join type 2. Then when i press ok, it comes up with "file title " to "film title" and comes up with "no unique index found with the referenced field of the primarytable".

Does anyone know what this meens?

View 1 Replies View Related

Relationships

Nov 22, 2004

I am writing a access database for work(fire dept). It is a database for the vehicle checks. Most of the fields are Boolean fields we just need to know if the inventory is on the unit. I am kind of new to this. My tables so far are as follows:

TCardiacMonitors
TFirstInBags
TGlucometerKits
TIntubationKits
TUnitNumbers
TCompartmentNumberOne
thru
TCompartmentNumberEight
TSealNumbers
TEngineChecks

all list the inventory that is supposed to be in that table

Everything is dependant on the Vehicle it is on so my question is Do I relate everything to a look up field in TUnitNumbers(intUnitNumberID) or is there a better way to do this? I need to relate everything to the date it was checked

Thank you for your help Bill

View 14 Replies View Related

Relationships

Jan 7, 2005

OK. I'm not real sure how close I am on this. I have a feeling I'm not too close so I was hoping I could get some help. I have a database with 11 tables. These can be seen in the screen capture attached. For the 'Action Taken' table there are 10 fields for 'Departments Involved', 15 fields for 'Memo Sent To', and 15 fields for 'Job Title'. The 'Departments' table is linked to 'Departments Involved1', the 'Memo Sent To' table is linked to 'Memo Sent To1', and the 'Job Title' table is linked to 'Job Title1'. All the other tables are showing the fields that are in that table. When I enter a record and try to go to a new record it gives me the following error message.

"You cannot add or change a record because a related record is required
in table "Background Information'."

I'm not sure what to do about this. I'm sure there is a simple solution but I don't know what it is. I am pretty new to Access (this is my third week on the job) and a little confused on some things. I would appreciate any help you could provide.

View 2 Replies View Related

Relationships

Feb 21, 2005

Hello. I'm new to databases and anything I know I've read up on. I know that relationships are essential so I'm trying to sort a database in ms access that I have inherited at work. It contains health service staff and their record of training - various training that they do regularly. I've managed to put the data in 1nf but am having difficulty after that.

A couple of questions:

If I give each staff member a unique id do I need to link their job title to it? Job titles chang quite a lot.

Other information about courses, dates of training, trainers etc. - should they be linked to staff name via separate tables?

I'm using a spreadsheet example from microsoft that shows me how to go from raw data to 3nf but I'm not sure how to split the information, so if you have any suggestions or know any good guides I'd be grateful for your help.

Thanks.

View 6 Replies View Related

Help With Relationships

Feb 23, 2005

I have 2 tables. tblcontact and tblboat.

tblcontact
CustomerID-PK
Customer
Contact

tblboat
CustomerID-FK
boat
Serial

i have made a form to enter the data, all works ok. I built a form to search by customer and it works fine. I can not find a way to build a form to search by boat name or serial. Can some one please help?

Thanks in advance!
Tim

View 2 Replies View Related

Relationships Again!

Mar 1, 2005

Hello. I posted a while ago on a database I'm working on. Still having trouble with relations. These are the fields in 1nf:

FNAME
LNAME
JOB_TITLE
BASE
DEPT_WARD
COURSE
DATE(S)_ATTENDED_COURSE
COURSE_TRAINER(S)
COURSE_VENUE
CERTIFICATE_ISSUED

The database keeps a record of various training courses for health service staff. From answers to previous posts I figure I need:

TBSTAFF
STAFF_ID
FNAME
LNAME
JOB_TITLE

TBCOURSE
COURSE_ID
COURSE

TBTRAINING_DONE
STAFF_ID
COURSE_ID
DATE_TRAINED
TRAINED_BY
CERT_ISSUED

I think that's ok but it's when I try to link them I get into trouble. Assuming the above is correct do I have:
TBSTAFF linked by STAFF_ID to TBCOURSE-COURSE_ID
TBSTAFF linked by STAFF_ID to TBTRAINING_DONE-COURSE_ID

I'm not sure how base and dep/ward work either. I need to know a person's base and dept e.g. hospital x, ward y.

Thanks.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved