Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Update Relation ID col using subquery...

Author  Topic  Mchael Starting Member 3 Posts Posted - 2011-07-05 : 08:04:29 Hello, I imported the Software PAD File Database from http://paddatabase.net/download.html into Microsoft Access in a table called main: MAIN ----- ID ProgramName Program_category_Class CategoryID <- I created this to create a one to many relation with a new table Category . . I created two new tables: category and subcategory. Category -------- ID CategoryName I extracted all the values from the field Program_Category_Class on the left side of the :: delimiter into the Table Category. Data from Program_Category_Class looks like this: Program_Category_Class Business::Accounting & Finance Games & Entertainment::Action Business::Accounting & Finance Business::Databases & Tools What I want to do is create a SQL Query which Updates the MAIN table with the Category ID from the Category table that is a LIKE (Left) match on Program_Category_Class. I will then modify the query for a right left match for the subcategory table. I tried something like this but get no result: UPDATE Main SET Main.category = (SELECT Category.ID FROM Category WHERE Category.CategoryName LIKE Main.Program_Category_Class+'%') WHERE (((Exists (SELECT Category.ID FROM Category WHERE Category.CategoryName LIKE Main.Program_Category_Class+'%'))<>False)); As it is a public DB I can provide a copy of the database to anyone interested. Any assistance would be most appreciated. Thank you. ----- Expert Windows Hosting Since 1997 - M6.Net