Help/Suggestions on how to structure tables

Hello all,

I have posted this question before but really haven't found an answer
that is working.

I am working on building a database using linked tables. (linked to
our main employee database through ODBC links) I created a query
(Staff Listing) to weed out the unneeded information from the linked
tables. All of this is working great, the query is updating correctly
when the main database is updated just fine. (I would have thought
this would have been the hard part. I have other tables with
supplemental information that is not stored in the data base linked
through employee ID numbers. (Education Offerings - this table is
unrelated to the Staff Listings Query, Supervisor Information,
Credentials, etc.)

Here is where the problem is coming into play:

I am atempting to create a junction table between the StaffListings
and Education Offerings showing what classes an employee has taken.

To avoid data entry errors I wanted to have the Employee ID, Last_Name
and First_Name fields automatically fill based on the other, but was
advised against it and I completely understand why! I then decided to
in the StaffListing query to create an expression concatinating the
Last and First Name fields, so that when doing data entry we don't
reference two seperate records in the query on the junction table. (It
made sense in my head). The expression worked and the junction form
and table are updating beautifully.

But when I try to build a query off of all three objects Staff
Listing, ClassInput (junction), and EducationalOfferings I am getting
this error message and don't know what it means. Has anyone seen this
before.

"Cannont join on Memo,OLE, or Hyperlink Object ([last_name]&", "&
[first_name]=tbleClassInput.EmployeeFullName).

Any and all suggestions on how to do this more accurately and better
would be greatly appreciated.

Junction does not need names, just EmployeeID, Class, and maybe field to show
active and some dates.
You query uses junction table fields EmployeeID and Class joined to the Staff
Listing and EducationalOfferings to display names and course title.

--
Build a little, test a little.


"Steve" wrote:

Hello all,

I have posted this question before but really haven't found an answer
that is working.

I am working on building a database using linked tables. (linked to
our main employee database through ODBC links) I created a query
(Staff Listing) to weed out the unneeded information from the linked
tables. All of this is working great, the query is updating correctly
when the main database is updated just fine. (I would have thought
this would have been the hard part. I have other tables with
supplemental information that is not stored in the data base linked
through employee ID numbers. (Education Offerings - this table is
unrelated to the Staff Listings Query, Supervisor Information,
Credentials, etc.)

Here is where the problem is coming into play:

I am atempting to create a junction table between the StaffListings
and Education Offerings showing what classes an employee has taken.

To avoid data entry errors I wanted to have the Employee ID, Last_Name
and First_Name fields automatically fill based on the other, but was
advised against it and I completely understand why! I then decided to
in the StaffListing query to create an expression concatinating the
Last and First Name fields, so that when doing data entry we don't
reference two seperate records in the query on the junction table. (It
made sense in my head). The expression worked and the junction form
and table are updating beautifully.

But when I try to build a query off of all three objects Staff
Listing, ClassInput (junction), and EducationalOfferings I am getting
this error message and don't know what it means. Has anyone seen this
before.

"Cannont join on Memo,OLE, or Hyperlink Object ([last_name]&", "&
[first_name]=tbleClassInput.EmployeeFullName).

Any and all suggestions on how to do this more accurately and better
would be greatly appreciated.

.