Search results for query: *

OK the problem is that the only field involved in this query that has NO Duplicates set or is a Unique field is the item_id field which is an auto_increment in the va_c2k and va_items tables. I'm trying to populate the va_items table with data from the va_c2k table. I've checked the va_c2k table...

That reset the auto_increment but I still get the same error. Duplicate entry '38' for key 1 (#1062) Any oidea what this error means. Thanks for all your help so far, I think I'll give it a rest till morning. Paul

You were right r937. The syntax was due to spaces in the scripting. It works now but I ran into another problem. When running another query I get the following "Duplicate entry '38' for key 1. This is the code, INSERT INTO va_items ( item_code, manufacturer_code, item_name, trade_price...

Thanks I tried something similar to that but used UNSIGNED. UPDATE va_c2k INNER JOIN va_categories ON (va_c2k.Sub_SubCategory = va_categories.category_name) AND (va_c2k.cat2 = va_categories.parent_category_id) SET va_c2k.cat3 = va_categories.category_id WHERE va_c2k.cat1 = CAST...

Hello again, wondering if I could get a little more help on my last query. UPDATE va_c2k INNER JOIN va_categories ON (va_c2k.Sub_SubCategory = va_categories.category_name) AND (va_c2k.cat2 = va_categories.parent_category_id) SET va_c2k.cat3 = va_categories.category_id WHERE va_c2k.cat1 =...

No problem, I'll figure it out some how. And you are right I was splitting a string on a comma. There is one other thing you may be able to advise me on. is it possible to split a string field (text string) and compare the result with a number field. I keep getting a Type Mismatch. Thanks...

although this makes MIN(Subcategory) irrelevant, yes?" Does it, I'm not sure. As I mentioned I've got a query with a module. THIS IS THE QUERY UPDATE va_c2k INNER JOIN va_categories ON (va_c2k.Sub_SubCategory=va_categories.category_name) AND (va_c2k.cat2=va_categories.parent_category_id) SET...

Thanks guys for the help. Your code r937 nearly worked but not quite. It’s only inserting 9 entries instead of 70. This has to do with the fact that there are only 9 cat1 entries. But now that I've got the , CONCAT('0,' , cat1 , ',') bit I think I can solve the rest. But don't fright as I'll be...

Hello can anyone help me convert the following MS Access Query to a MySQL pass through query. INSERT INTO va_categories ( category_name, parent_category_id, category_path, is_showing ) SELECT First([va_c2k].SubCategory) AS [SubCategory Field], [va_c2k].cat1, "0," & [cat1] & "," AS...

OK I give up I can't figure out what to do with this code. INSERT INTO va_categories (category_name, parent_category_id, category_path, is_showing) SELECT (va_c2k.SubCategory) AS 'SubCategory Field', va_c2k.cat1, '0,'cat1',' AS category_path, '1' AS is_showing FROM va_c2k GROUP BY va_c2k.cat1...

Sorry, I got the above to work. Just had some silly spaces to remove. Paul

Ok, I managed to get your 1st code snippet to work but I changed the '.' in tables names back to '_'. I've got stuck on the second query; INSERT INTO va_categories (category_name, parent_category_id, category_path, is_showing) SELECT va_c2k.SubCategory AS 'SubCategory Field', va_c2k.cat1...

Hello, the DB name is 'geminus' and va_categories is a table name. I change the '_' to a '.' because I taught I was supposed to do that. I'll give your first code snippet a try. Paul

Hello again DonQuichote, I did what I mentioned above and I get an error which I've attached. I was wondering if the '_' in field names shoud be replaced with a '.' also. The query I'm trying to run is; INSERT INTO va.categories ( category_name, category_path, is_showing ) SELECT...

Hello DonQuichote, 2 day later I get around to reading your reply which I appreciate very much. That said I don't fully understand your reply. Either you’re extremely cryptic or you've made a few typos. What exactly did you mean by this; "If your tables are in the same database, you do not...

Hello again lameid, I've posted about the SQL pass through queries and I'm hoping I'll get some help soon. But I wonder if you could help me with one other issue. I get an error when running one query, well it's not really an error but it means that not all entries are updated. Attached is a...

Hi, thanks for the reply. My queries are quite small and simple. But I was wondering if I should put the local control table in the mysql db first before making any pass-through queries. Here is one of my queries: [code] INSERT INTO va_items ( item_code, manufacturer_code, item_name...

Hello all, I have an Access DB connected to a MySQL DB using linked tables. In access I've got several various update and append queries and a local products table that help populate the MySQL DB. Everything works fine but it takes forever to run the queries. I was told that if I use SQL...

OK lameid thanks for the help. I'll let this run first and see how it's working and then I'll work on SQL Pass-through. Thanks, Paul

That looks like it is working but the only thing is it takes so long to run the queries when the tables are linked to MySQL on a remote server. I'm wondering if there is a faster way of doing this. Thanks again, Paul