 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Transferring Field from Existing Table/limitations and change of d
Thank you in advance for your help! I have two Excel spreadsheets that I successfully imported into Access 2003 and created tables for. I need to add the field from one table to the other, but there is not a direct match in the relationship. The large table uses the Employee ID as the primary key. The smaller table contains one field that lists a subset of these Employee ID numbers (a selection of certain employees). I need to transfer this field to the larger table, but I do not know how to tell Access to match up the corresponding numbers (i.e., the large table lists all employees, but the added field will only match up with those employees who appear in it.) In addition, I need to change the value of the data in the added field from its original number form to a "yes." Therefore, the "yes" would appear in the added field in the record that corresponds with the appropriate Employee. Any help would be greatly appreciated. I'm fairly new at this program and feel that there has to be an easier way to do this than just adding a field and hand keying all of the "yes" entries with the corresponding numbers. THANK YOU! Hi A little bit more information is needed (for me - someone else may understand) You hve 2 tables - large table and small table What are the names if the tables In the large table you have a primary field Is this unique (ie. autonumber) In the small table you have a number of records that have the same primary field (???) or do you have records with a field that correceponds to the primary field in the large table - sorry I'm lost with this bit. Wha field do you want to add to the large table from the small table Field names would give you a better answer The field that you want to add to the large table from the smaller table is a number (???) and you want to change this to the test string "yes". Sorry lost with this as well. Is it a yes/no (0/-1) format or something else. If it's a number do you want to change the numbers or are the all the same Sorry I just don't understand this bit - can you give more info.  SignatureWayne Manchester, England. > Thank you in advance for your help! I have two Excel spreadsheets that I > successfully imported into Access 2003 and created tables for. I need to add [quoted text clipped - 12 lines] > and hand keying all of the "yes" entries with the corresponding numbers. > THANK YOU! >Thank you in advance for your help! I have two Excel spreadsheets that I >successfully imported into Access 2003 and created tables for. I need to add [quoted text clipped - 12 lines] >and hand keying all of the "yes" entries with the corresponding numbers. >THANK YOU! You may still be in a spreadsheet mindset. If all you need is a report, or a display, listing a YES for all employees who match in the two tables, you don't need any new fields in either table (and in fact should not create such a redundant field at all!) Instead, create a Query joining the two tables on this ID. Select whatever fields you want to see from each query. This query will show only those employees who appear in both tables. If you want to see all the employees, with a Yes/No field showing Yes for those who match in the smaller table, select the Join line in the query design window and choose option 2 (or maybe 3 depending on how you created the query): Show All Records in <employees> and matching records in <matching table>. In a vacant Field cell type something like InSmallTable: Not IsNull([smalltable].[Employee ID]) This field will be YES if there is a matching record, NO otherwise. John W. Vinson [MVP] Thank you both so much for your help! Alas, I have been directed to insert the field from the small table so that it exists in the table but in the different form of a yes response for the matching information. To clarify, the "Employees" table is the large table and has a primary key of Employee ID. The "Bad Rating" table consists of one field, "Bad Rating," that is a list of employee ID numbers of those employees who received a poor rating. This list of numbers is what needs to be added to the "Employees" table, but instead of listing the ID numbers, it needs to correspond with the "Employee ID" field and return a "Yes" for those numbers that were in the "Bad Rating" field. Does this field need to be renamed "Employee ID" so that it can link with the "Employee ID" field in the "Employees" table? I'm going to try the query suggested below so that I may view all employees, but I had one question (not to sound like an idiot - I'm just an amateur!): the formula I am to type in a vacant field cell should go in the field where I want the "Yes" response to show up? Once again, I cannot thank you enough for your help. Going to try suggestions now! > >Thank you in advance for your help! I have two Excel spreadsheets that I > >successfully imported into Access 2003 and created tables for. I need to add [quoted text clipped - 33 lines] > > John W. Vinson [MVP] Try something like this UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes" WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID])); SignatureWayne Manchester, England. > Thank you both so much for your help! Alas, I have been directed to insert > the field from the small table so that it exists in the table but in the [quoted text clipped - 55 lines] > > > > John W. Vinson [MVP] Thank you, Wayne. If you wouldn't mind, can you give me a bit more instruction on where to type the below formula? I tried doing it in SQL view of a query, but I keep getting errors. Thank you for your patience! > Try something like this > [quoted text clipped - 60 lines] > > > > > > John W. Vinson [MVP] Sorry my fault. I used made-up names. 1st MAKE A BACKUP of your DB. I have assumed this You have a table called tblEmpoyee This is the large table You have a table called tblBadRating This is the small In tblBadRating you have a field that holds numbers that are the same as "some" of the record primary field in tblEmployee You want to add the word (text) "Yes" to tblEmployee for each record in tblBadRating --------------------------------- Add a field to tblEmployee (or whatever your large table is called) Call this new field BadRating Open a new query and insert the sql "then change each of the names of the tables" from; tblEmployee to the real name of your large table tblBadRating to the ral name of your smaller table UPDATE tblBadRating, tblEmployee SET tblEmployee.BadRating = "Yes" WHERE ((([tblEmployee]![EmployeeID])=[tblBadRating]![EmployeeID])); Then run this (click the red apostraphy - or just save the query and then open it again) Hope this helps SignatureWayne Manchester, England. > Thank you, Wayne. > If you wouldn't mind, can you give me a bit more instruction on where to [quoted text clipped - 66 lines] > > > > > > > > John W. Vinson [MVP] No errors this time, but it is now asking for a parameter value for Employee.BadRating, then for Employee!EmployeeID and BadRating!EmployeeID. Any advice? THANK YOU so very much for the great step-by-step in your last post. > Sorry my fault. I used made-up names. > [quoted text clipped - 102 lines] > > > > > > > > > > John W. Vinson [MVP] Have you added the field BadRating to the Employee table (the large table). Have you changed the sql to show the real name of the tables in your DB Is your primary field in the small table called EmployeeID I don't have all the correct tables names and field names so I can't write the sql here - just give you an example - you need to alter the sql to get it to work in your DB. If you post the real field names and table name then someone will be able to quicky give you an update query but you should try and just change the snipet I gave to show the real names 1st. The more you do on this type of stuff the simpler it gets. Give it a go. SignatureWayne Manchester, England. > No errors this time, but it is now asking for a parameter value for > Employee.BadRating, then for Employee!EmployeeID and BadRating!EmployeeID. [quoted text clipped - 108 lines] > > > > > > > > > > > > John W. Vinson [MVP] OK...we're almost there! I was able to correct my error (didn't include a space in the Field name when there was one) and got the query to work. However, instead of adding the data to the Employee table, it just displays a list of "Yes" entries in a Bad Rating field that stands alone - it was not incorporated into the Employee table and matching up with the ID numbers that needed the "Yes." THANK YOU! > No errors this time, but it is now asking for a parameter value for > Employee.BadRating, then for Employee!EmployeeID and BadRating!EmployeeID. [quoted text clipped - 108 lines] > > > > > > > > > > > > John W. Vinson [MVP] HOORAY! It worked! Thank you so very much, Wayne, for your time and patience. :) > OK...we're almost there! I was able to correct my error (didn't include a > space in the Field name when there was one) and got the query to work. [quoted text clipped - 117 lines] > > > > > > > > > > > > > > John W. Vinson [MVP] I'm pleased it worked for you. SignatureWayne Manchester, England. > HOORAY! It worked! Thank you so very much, Wayne, for your time and patience. > :) [quoted text clipped - 120 lines] > > > > > > > > > > > > > > > > John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.