Relationship woes

[kupe]

The database for workers works ok from a solitary table, tblEmployees.

I want to make it a relational db.

I’ve incorporated lookup tables. But I’m lost about what to do next – and if it should be divided further.

I’ve studied many tutorials in relationships, but I don’t find one that resembles this one.

Could a relationship expert give advice please? I've included a sample, hoping this will be simplest way for you to understand the challenge.

 

[wazz]

you need a few more tables. i've added a few to give you an idea.

if an employee can do something many times (get many jobs, be reprimanded many times, get several levels of education) then you need another table where you add those many things.

if those many things (jobs, reprimands, education diplomas, etc.) can also apply in reverse - to many employees - then you need a table for that.

simply put a table in the middle where an employee's many "things" can be added, and, the "things" can be added to many employees. you never actually type an employee's details into the db more than once and you never type the "things" into the db more than once.

hth, w

 

[kupe]

Cheers, wazz, will take it away to study. Will report back. (My brother's just come back from KL. Very impressed he was.)

 

[wazz]

cheers. hope it helps.
w

 

[kupe]

Hi Wazz

Many thanks for this and the trouble you have gone to. It is appreciated very much.

What I've never understood is how the details go into these other tables. For instance, tblEmployeeEducation has the field EmpID.

Is the ID of the EmpID put into the table one by one. If the db had 5,000 employees that would be quite a task. (Fortunately, this one runs into the hundreds. Still, that's means quite a lot of time to commandeer the db and to make the changes.)

However, the chore aspect aside, is that how it's done - and for tblTransReq and tblEmpMisconduct, et al, please?

All the best

 

[neileg]

I would expect you to create forms that populate with the employee details and a subform or subforms that hold the details from other tables linked via the EmployeeID field. As you add new records via the form, the EmployeeID will automatically be added to the new record.

 

[kupe]

Cheers, Neil, thanks. Yes, I have something like that.

I was wondering if there is a way to put the parts of records from the old solitary table and insert them into the new tables using queries - or if it had to be done by hand (in a form etc).

 

[wazz]

What I've never understood is how the details go into these other tables. For instance, tblEmployeeEducation has the field EmpID. Is the ID of the EmpID put into the table one by one. If the db had 5,000 employees that would be quite a task. However, the chore aspect aside, is that how it's done - and for tblTransReq and tblEmpMisconduct, et al, please?

yes, that's how it's done. select an employee, then fill in the details. select the employee again, or a different employee, and add the details.

fortunately, yes, this can be done by queries. use 'Update' queries to fix data problems, 'Make Table' queries to isolate data, 'Append Queries' to add data to a table, etc. The queries can help you normalize the tables and move things around without having to do it one record at a time. (don't forget to search the queries forum and Access Help for more ideas).

w

 

[kupe]

I collect the EmpID with a look-up but it seems I cannot use a query to

UPDATE tblThis
Set tblThis.OneField = tblThat.AnotherField
WHERE tblEmplThis.EmpID = tblThat.EmpID;

And I assumed that must be because of look-up fields. (That an Update query won't work if the field is a look-up.)

And that requires quite a rethink on the problem because it would take a long time if even some of the fields have to be changed manually.

Anyway, really marvellous of you to have offered so much advice, and to have endured while I stumble along. Really grateful. Cheers.

 

[Mark-BES]

Hi all

I am also having the "Relationship woes". Is it possible to post a A97 version to compare my Db with?

Many thanks

 

[neileg]

I collect the EmpID with a look-up but it seems I cannot use a query to

UPDATE tblThis
Set tblThis.OneField = tblThat.AnotherField
WHERE tblEmplThis.EmpID = tblThat.EmpID;

And I assumed that must be because of look-up fields. (That an Update query won't work if the field is a look-up.)

And that requires quite a rethink on the problem because it would take a long time if even some of the fields have to be changed manually.

Anyway, really marvellous of you to have offered so much advice, and to have endured while I stumble along. Really grateful. Cheers.

Don't understand. It doesn't matter if your fields are lookups or not, once the field is populated.

If you give us an example of what is in tblEmployees, we may be able to help in normalising the data into different tables. Either give us the field names and a description if that's not obvious, or preferably some sample data (make sure you depersonalise the data though).

 

[kupe]

Sorry, Mark-BES, I thought I could convert Wazz's to A97 but it didn't work. We'll have to hope someone arrives who has that program. Cheers

 

[kupe]

Hi Neil
The db isn't working out too smoothly. I don't doubt it's things I'm not doing right. Here is an abridged version of where I am up to. It's marvellous of you to take a look.

Cheers, and thanks for interest.

 

[neileg]

OK, here’s a few observations, not a full analysis!

tblEmpWings serves no purpose. You can connect tblWings direct to tblEmployees.

Although not linked in the relationships diagram, I presume there’s a one to many between tblEmployees and tblEmpTransReq. But why does the field TransferRequest appear in tblEmpTransReq and tblTransferRequests?

I don’t think there’s a one to many relationship between tblEmployees and tblEmpRoots. This would imply that an employee can have many roots. Is that correct?

Now, as for populating the sub tables from data already held in the main table, there’s a problem in that some of the fields in the sub table don’t seem to exist in the main table. For example, you have [WorkID] but not [Work] in tblEmployees. Is this sample a sort of half way house? Have you done some of the transfer but not all of it?

 

[kupe]

Cheers, Neil, Cheers, Pat.

This gives me something to worry over. Your remarks are valued. I hope to let you know the outcome .. soon. Gratefully.

 

[kupe]

Gentlemen, many thanks. I think, I believe, the db is now relational. Well, the copy of it seems to be. I just have to correct my mistakes and apply the change to the actual db.

On a forum, I was told that every form should come from a query, and I have followed this style.

In this case, with a form and two subforms, I presume I would also feed them through queries. Does that seem correct?

Thank you, Wazz, thank you, Neil, thanks, Pat. I don't know why it has taken me so long to understand the principles. However, thanks to you, I think I have them at last.