RE: This is driving me crazy

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You need to change your database structure to the following.
Employee --
EmpID - Autonumber - Primary key
EmployeeNumber - text
LName - text
FName - text
MI - text
DOB - DateTime
etc.

Jobsite --
JobID - Autonumber - Primary key
ProjectNum - text
ProjName - text
StartDate - DateTime
EndDate - DateTime
Active - Yes/No
etc

Then have junction table so you can have a one-to-many relation between
employee & jobs and a one-to-many relation between jobs & employees.

Emp_Job --
EmpID - number - integer - foreign key
JobID -number - integer - foreign key
StartDate - DateTime
EndDate - DateTime
Active - Yes/No
etc

Set the relationships between the tables. Build a query with left join from
employee to Emp_Job and left join from Jobsite to Emp_Job.

Use this query with a form/subform (employee to jobs) setting the
Master/Child links on EmpID.

Use this query with a form/subform (jobs to employee) setting the
Master/Child links on JobID.

"Lori" wrote:

The more I work with this program the more I realize how little I actually
know.

Some please give me a clear answer. I have two tables. One with Employees
and one with jobsites. Several of our employees work on more than one
project at a time. I am trying to generate a report that will list all of
the employees working on a project (when the user enters the appropriate
project #). The problem I have is that I have not been able to build my
expression in a way that will work. I either end up with one name because
only one person on that project is working on 2 projects, or I end up with
none.

My employee table has the fields current and 2nd, while the jobsite table
has the field project #, this project number field is the common factor, both
the current and 2nd fields on the employee table use the project #s listed on
the jobsite table. So how do I write this expression that will tell the
query to look into the current field and then look into the 2nd field without
excluding everyone not listed in both fields?
--
Lori A. Pong
.

Relevant Pages

  • RE: Employee Training Help
    ... HireDate - DateTime ... CourseID - Autonumber - primary key ... I am creating an employee training database where I can run reports and ...
    (microsoft.public.access.tablesdbdesign)
  • Re: New to Access db
    ... The Foreign Key fields in the Subform which arentt linked to the main form ... Add EmpID and Employee to the query. ... AttEventID - Primary key Auto Number ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Understanding Relationships
    ... have pages of things they might track about an employee. ... and for which a history is to be kept MUST be in a related (child) ... primary key of this employee's record in tblEmployee. ... Table 2 [SalaryScale related to Table 3 SalaryScale] ...
    (microsoft.public.access.gettingstarted)
  • Re: Understanding Relationships
    ... have pages of things they might track about an employee. ... and for which a history is to be kept MUST be in a related (child) ... primary key of this employee's record in tblEmployee. ... Table 2 [SalaryScale related to Table 3 SalaryScale] ...
    (microsoft.public.access.gettingstarted)
  • Re: WWW/Internet 2009: 2nd CFP until 21 September x
    ... Firsst_Name char 25 not null, ... Noe let's say that the rules are that you can't put an employee into the table unless you know the First Name and Last Name, and you get assigned to the employee an Employee_Id that's never been used before. ... (Employee_Id integer primary key foreign key references ... There's no nulls allowed in the two table solution, but there's no law that says you can't leave an entire row out. ...
    (comp.databases.theory)