Foreign Key

[dspinner]

I am working in an HR data base linking two tables with a one to many relationship. In the "Main" table i have the field "EmployeeID" which is a number assigned by the HR Department. IN a "Positions" table, I have a jobID and and EmployeeID field, with the two employee ID fields joined one-to-many. When i created forms and enter data in the "Jobs" form, it fails to capture the Employee ID as a foreign key. Any suggestions?

 

[dcx693]

What's the recordsource for the form? When you say it fails to capture the ID as a foreign key, what's the controlsource for the employeeID on the form?

 

[dspinner]

The record source is a lookup query. The control source is "employeeid" from the "jobs" table

 

[Mile-O]

You should not have an EmployeeID field in the Positions table. The PositionID should be in the Employees table.

 

[Mile-O]

I think your structure is suspect - having a table called Main, too, leads me to that conclusion. In other words, I think you've built one big main table for all with smaller tables feeding into it.

 

[dspinner]

STRUCTURE:

MAIN TABLE
EmployeeID
Lstname
Frstname
MI
Prefix
Address
ZIp
County
Phone

JOBS
EmployeeID
JobID
Department
Position
Supervisor
Office
Phone

One employee may have more than one job. So where is the link made?

 

[neileg]

So the Main table is really an employee table. In the Jobs table, JobID should be the PK and EmployeeID an FK. As you say, each employee can have more than one job. But the unanswered question is can each job have more than one employee? In other words, how many posts per job do you have?

If you only have one post per job, and if you have the relationship set between the EmployeeID in the Main table and the EmployeeID in the Jobs table, then if you create a mainform for the employee and add a subform for the jobs, the Access form wizard should manage the parent child link OK.

How are we doing so far?

 

[dspinner]

So far so good...

If I use the wizard to create a form/sub form..no problem

If i use the wizard to create a from - joined form...the employeeID does not go to the "job" form...?????

I want to be able to open the main form for Employee A, and when clicking on the "Jobs" button, show the jobs they have...

Seems simple. Have done it before, but I cant seem to get the connection right this time

 

[Mile-O]

STRUCTURE:
JOBS
EmployeeID
JobID
Department
Position
Supervisor
Office
Phone

Department needs a table of its own.
Position needs a table of its own.
Supervisor should link back to the EmployeeID
Office, I presume, is a location and therefore needs a table of its own.
Phone should be irrelevant as the employee should have a phone number in their table; unless, of course, the department phone number covers all within that number - in that case the department tables gets a Phone field.

 

[neileg]

So far so good...

If I use the wizard to create a form/sub form..no problem

If i use the wizard to create a from - joined form...the employeeID does not go to the "job" form...?????

I want to be able to open the main form for Employee A, and when clicking on the "Jobs" button, show the jobs they have...

Seems simple. Have done it before, but I cant seem to get the connection right this time

I'm back to confused. What is a from - joined form...

If the wizard creates an employee mainform with a jobs subform, what are you clicking a button to do? If you want to open another form, this should be based on a query where the criterion points at the JobID in the Main form. Or maybe you need to add a tab to your subform to hold the job details instead of opeing another form. I'm guessing here 'cos it's not obvious what you want.

Us Geordies need things spelled out. If it doesn't involve beer and wimmin, we get confused.