Simple problem with access

[Guest]

Hey everybody,

I just got a problem dropped in my lap. I have a table that just
contains employees and employee ID numbers. On a form that creates a job
requisition, two employee IDs have to be entered to denote who will be doing
the job.

When I create my report with the relationships that I have entered for the
data, the report only spews out one name. Since I only have the one table
with the employee names and ID information.

You see my report looks something like:

ID NUMBER1 ID NUMBER2

LAST NAME LAST NAME

Where in the ID number fields the number that was entered in the form shows
up, and the last name that is related to that employee number from the data
table shows up.

So, for example it shows:

1 2

SMITH SMITH

I need somehow to get the report to kick out the two different names. I
tried creating another table with the exact same employee information in it,
with the label LAST NAME 2, and then I set up the new relationships. I set
up the report and I get an error.

This is the error that Access gives me:

Type mismatch in JOIN expression. (Error 3615)
A JOIN expression is attempting to join two tables on fields of incompatible
data types. For example, you will get this error if you attempt to join a
Memo field with a Text field.

If you can help me with this last hurdle I would greatly appreciate it.

Thanks a lot!

P. Rossiter

 

[Wayne Morgan]

On the form where the employees doing the job are entered, where is that
value stored? Are both entries in one field or two? If it is in two fields,
you can add the employees table to the query a second time and link this
second copy to the second field in the job table. The second employee table
will get a name such as tblEmployees_1 automatically. This is to distinguish
which copy of the table it is.

Now for the real problem. This will work as long as you only assign two
employees to the job. If this is ever going to change, a different table
relationship is needed. This is actually a many-to-many relationship,
multiple employees can be assigned to a single job and a single employee can
be assigned to multiple jobs. You actually need a third, linking table to
hold which employee(s) is assigned to which job(s). You would then link this
table to the other two on their common fields. The Employees and Jobs tables
would not link directly to each other.

This new table would have to have the following two fields, EmployeeID and
JobID. The combination of both of these fields would be set as the primary
key for the table. You could then include other fields, if desired, that
would give information about the job, such as when started, scheduled
completion date, cost, etc.

 

[Guest]

Ok Wayne, I see what you are saying. But, I have another question, how do I
create a many to many relationship? It seems I can only get access to create
a one-to-many relationship.

 

[Wayne Morgan]

The many-to-many relationship is created by using the linking table. It
creates a one-to-many relationship with each of the current tables. This
table gives you a many-to-many relationship between the two current tables.

Examples and further information:
http://msdn.microsoft.com/library/d...efinemanytomanyrelationshipbetweentabless.asp
http://support.microsoft.com/kb/304467/en-us
http://support.microsoft.com/kb/209193/en-us
http://www.databasedev.co.uk/many_to_many_example.html
http://www.smartcomputing.com/edito...4/s1508/49s08/49s08.asp&articleid=20969&guid=