If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Help/Suggestions on how to structure tables
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. . |
Thread Tools | |
Display Modes | |
|
|