I came up with a translation to the first query, which had the two sub-selects, and it now seems to work. I've yet to come up with a solution to the second query, which had the Group By clause. I'd still like to hear some ideas.
- Ed >>> "Ed Reed" <[EMAIL PROTECTED]> 11/11/2002 8:03:03 PM >>> This is my first post. I hope it works and please excuse me if I screw up. I have an MS Access database I'm trying to convert to MySQL v4.0.3 for Netware. I use a VB frontend to talk to my .mdb file. So far I've gotten all my data into MySQL using the MyAccess tool I bought. In my VB frontend I've already changed my ADO connection provider to use MyOLEDB (I had success with both MyODBC and MyOLEDB but chose to stick with MyOLEDB). My frontend works for most of it's queries but I have a couple that fail. If I use MS Access as a frontend to connect to MySQL using ODBC the same queries will work correctly. This makes me think that the queries can work from my VB prog if I figure out how to modify them. I'm assuming that Access/ODBC is translating them into an acceptable query for MySQL. My two queries are below, if anyone could point me in the right direction I know I can get this converted and working well. Any suggestions would be appreiciated. Query 1 SELECT (Select PartNumber From Products Where((Bom.ProductID = Products.ProductID));) AS UpperLevelAssembly, (Select Description From Products Where((Bom.ProductID = Products.ProductID));) AS UpperLevelAssemblyDescription, BOM.Quantity FROM BOM INNER JOIN Products ON BOM.SubProductID = Products.ProductID WHERE ((Products.ProductID=1864)); Query 2 ( this query works in my VB app if I remove the Sum() and Group By clause and change the Having to a Where) SELECT Products.PartNumber, Products.Cost, Products.ModelNo, Products.USPrice, Products.VendorPart, Products.Description, Products.ProductID, Sum(tblInvTransaction.Qty) FROM Products LEFT JOIN tblInvTransaction ON Products.ProductID = tblInvTransaction.ProductID GROUP BY Products.PartNumber, Products.Cost, Products.ModelNo, Products.USPrice, Products.VendorPart, Products.Description, Products.ProductID, Products.Obsolete Having ((Products.PartNumber Like "*A-000*") AND (Products.Obsolete<>-1)) ORDER BY Products.PartNumber; --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
