Table relationship many to one showing all records.

[Kill_Switch]

Good day everyone.

I thought I had everything set correct, but it would seem not.

I am trying to have a table, where we can track what area's of the building people were employed. Many employment areas to one employee.

Problem being, when click on the "Area of Employment" and the form opens. It opens to all records, not just that employee's. I can add a record but it will appear on each record.

Thank you, very much, in advance for any of your time.

Employment form VBA if that helps

Private Sub Add_Employment_Click()
    DoCmd.GoToRecord , , acNewRec
    START_DATE = Now()
End Sub


Private Sub Command368_Click()
DoCmd.RunCommand acCmdCloseWindow
End Sub

Private Sub Delete_Record_Click()

If MsgBox("Are you sure you want to delete the area of employment?", vbYesNo, "Warning!") = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord
Else
    DoCmd.OpenForm "frm_EMPLOYMENT"
End If
End Sub

Private Sub Form_GotFocus()
    EMPLOYED_AREA.SelStart = 0
End Sub

 

[theDBguy]

Can you post a sample copy of the database with test data?

 

[Kill_Switch]

I can try in the morning, will have to sanitize the data.

 

[CJ_London]

Many employment areas to one employee.

So an employment area only has one employee - ever

are you sure you don’t mean many to many?

 

[Gasman]

I can try in the morning, will have to sanitize the data.

How to arrange order of records - Page 2

Hi, I've got a tricky to explain problem, so I'll try to explain with an example. Basically, I have a payments table, with various payment types (fee, porterage, travel, early start etc). What I want to do eventually, is to create separate remittance advice reports for each supplier...

www.accessforums.net

 

[Kill_Switch]

So an employment area only has one employee - ever

are you sure you don’t mean many to many?

I had a long self discussion. I don't think it would be a many to many, I say this the employment area is per employee is by each employees experience. If I didn't want to track their start and end dates, then I would consider having a junction table for a many to many.

1 employee can have multiple unique time spent at a employment area at work.

I would consider that a many to one. Am I wrong? I mean this being inquisitive not sarcastic (emotion in text is hard )

 

[jdraw]

Still not clear to me.
Consider, the following: with associated dates/duration.
Bob works in IT.
Joe works in Plumbing
Ted works in Accounting
**Joe gets new job in IT
**Bob gets moved to Corporate Management

If your Employment areas are Accounting, IT, Plumbing and Corporate Management, then what exactly do you want to report?

-here is/are the work area(s) where Employee X has worked
-here is/are the Employee(s) who have worked in WorkArea X

 

[mike60smart]

Still not clear to me.
Consider, the following:
Bob works in IT.
Joe works in Plumbing
Ted works in Accounting
**Joe gets new job in IT
**Bob gets moved to Corporate Management

If your Employment areas are Accounting, IT, Plumbing and Corporate Management, then what exactly do you want to report?

-here is/are the work area(s) where Employee X has worked
-here is/are the Employee(s) who have worked in WorkArea X

I would imaging a Form where you select an Employee
with a Subform that allows you to keep the History of the Employees Departments

 

[jdraw]

I agree Mike. Was editing while you posted.
Sounds like Many Employees and Many Work areas and employees can relocate (possibly many times).
I also note that the OP offers an Option to delete workarea(s)??

If MsgBox("Are you sure you want to delete the area of employment?", vbYesNo, "Warning!") = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord

So, history records could be deleted?? "Data integrity requirement"?

 

[Kill_Switch]

Still not clear to me.
Consider, the following:
Bob works in IT.
Joe works in Plumbing
Ted works in Accounting
**Joe gets new job in IT
**Bob gets moved to Corporate Management

If your Employment areas are Accounting, IT, Plumbing and Corporate Management, then what exactly do you want to report?

-here is/are the work area(s) where Employee X has worked
-here is/are the Employee(s) who have worked in WorkArea X

The 1st one.

Here are/is the work areas where employee x has worked.

ID

Employee

Area

Year

I would normally have a subform and just tell the parent and child relation, but it doesn't fit the template well.

 

[Kill_Switch]

I agree Mike. Was editing while you posted.
Sounds like Many Employees and Many Work areas and employees can relocate (possibly many times).
I also note that the OP offers an Option to delete workarea(s)??

If MsgBox("Are you sure you want to delete the area of employment?", vbYesNo, "Warning!") = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord

So, history records could be deleted??

Only if a record was added to the wrong person, just that employment timeline, not employment area completely.

Option to delete the record to be able to place it in its proper location.

 

[mike60smart]

Only if a record was added to the wrong person. Option to delete the record to be able to place it in its proper location.

Are you able to upload a zipped copy of the database with no confidential data?

 

[jdraw]

Only if a record was added to the wrong person. Option to delete the record to be able to place it in its proper location.

Wouldn't that be an edit to an existing (incorrectly entered) record?

 

[Kill_Switch]

Are you able to upload a zipped copy of the database with no confidential data?

I will sanitize the data and post a stripped down dB later when I can.

 

[Kill_Switch]

Wouldn't that be an edit to an existing (incorrectly entered) record?

Essentially. If Bob had employment is Plumbing entered against them in error. The option to select that plumbing employment for Bob to be removed exists. I probably misused the term delete record.

 

[jdraw]

Sound like

Employee---->EmployeeEmploymentHistory<---WorkArea

 

[Kill_Switch]

I agree Mike. Was editing while you posted.
Sounds like Many Employees and Many Work areas and employees can relocate (possibly many times).
I also note that the OP offers an Option to delete workarea(s)??

If MsgBox("Are you sure you want to delete the area of employment?", vbYesNo, "Warning!") = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord

So, history records could be deleted?? "Data integrity requirement"?

It would be a many to many, except I want to track when they were employed in that area. So would that not make it one-to-many? It's unlikely there would be users with the same start and end dates for employment areas

 

[Kill_Switch]

Sound like

Employee---->EmployeeEmploymentHistory<---WorkArea

Oh

I didn't think of it like that.

 

[Kill_Switch]

Oh

I didn't think of it like that.

I'm going to try and sneak down and post a stripped db

 

[The_Doc_Man]

I want to track when they were employed in that area.

This is a classic case of a junction table, which is how you do many-to-many, and you put the dates in junction-table entry that shows:

Employee ID (as FK to employee table)
Department or Area ID (as FK to department or area table)
Starting date
Ending date