miffe Posted September 19, 2006 Share Posted September 19, 2006 I know how NAFT the 'visual' query editor in Access is, but even so I used it to create a quite complex query, now I've moved the DB to MySQL, but the same query wont work because Access add's a whole load of ( )'s and stuff... IS there any way to accurately convert it? Or do I have to hand sort it and remove all the glitches?Thanks -miffe Link to comment Share on other sites More sharing options...
aspnetguy Posted September 20, 2006 Share Posted September 20, 2006 hand sort my friend...post the query and I'll give you a hand and show you what to watch out for. Link to comment Share on other sites More sharing options...
miffe Posted September 20, 2006 Author Share Posted September 20, 2006 hand sort my friend...post the query and I'll give you a hand and show you what to watch out for.Ok, the query is complete madness because I need to show various things at the same time which are the result of multiplications and other operations with other values of other tables so it's kind of confusing, works in access but wont work in MySQL...I was advised that mysql wont accept access []'s (square brackets) and access also uses excess ()'s, as well at using the table!column nomenclature which I need to "convert" to real time or so I was said, but I have no clue because the whole query looks massive and I don't know where to start :sSELECT modelos.idModelo, modelos.nombre AS Modelo, tiposJoyas.nombreTipo, colores.color, brillantes.tamanoPiedra AS BTE, Round(Sum([valorVenta]+((brillantes!costoBTE*brillantes!importacion*brillantes!utdBruta/brillantes!comisionRep*brillantes!tamanoPiedra)+((ensambleMontadura!precioOro*32.15/1000*0.585+ensambleMontadura!costoAleacion+ensambleMontadura!utdBruta/ensambleMontadura!comisionRep+ensambleMontadura!costoEnsamble)*modelos!pesoMontadura))/2)) AS Normal, Round((Sum([valorVenta]+((brillantes!costoBTE*brillantes!importacion*brillantes!utdBruta/brillantes!comisionRep*brillantes!tamanoPiedra)+((ensambleMontadura!precioOro*32.15/1000*0.585+ensambleMontadura!costoAleacion+ensambleMontadura!utdBruta/ensambleMontadura!comisionRep+ensambleMontadura!costoEnsamble)*modelos!pesoMontadura))/2)/0.9)) AS Alta, Round((Sum([valorVenta]+((brillantes!costoBTE*brillantes!importacion*brillantes!utdBruta/brillantes!comisionRep*brillantes!tamanoPiedra)+((ensambleMontadura!precioOro*32.15/1000*0.585+ensambleMontadura!costoAleacion+ensambleMontadura!utdBruta/ensambleMontadura!comisionRep+ensambleMontadura!costoEnsamble)*modelos!pesoMontadura))/2)*0.85+1.2)) AS Casal, Round((Sum([valorVenta]+((brillantes!costoBTE*brillantes!importacion*brillantes!utdBruta/brillantes!comisionRep*brillantes!tamanoPiedra)+((ensambleMontadura!precioOro*32.15/1000*0.585+ensambleMontadura!costoAleacion+ensambleMontadura!utdBruta/ensambleMontadura!comisionRep+ensambleMontadura!costoEnsamble)*modelos!pesoMontadura))/2)*2)) AS Menudeo, Round((Sum([valorVenta]+((brillantes!costoBTE*brillantes!importacion*brillantes!utdBruta/brillantes!comisionRep*brillantes!tamanoPiedra)+((ensambleMontadura!precioOro*32.15/1000*0.585+ensambleMontadura!costoAleacion+ensambleMontadura!utdBruta/ensambleMontadura!comisionRep+ensambleMontadura!costoEnsamble)*modelos!pesoMontadura))/2)/0.95)) AS Cristal, Round((Sum([valorVenta]+((brillantes!costoBTE*brillantes!importacion*brillantes!utdBruta/brillantes!comisionRep*brillantes!tamanoPiedra)+((ensambleMontadura!precioOro*32.15/1000*0.585+ensambleMontadura!costoAleacion+ensambleMontadura!utdBruta/ensambleMontadura!comisionRep+ensambleMontadura!costoEnsamble)*modelos!pesoMontadura))/2)*1.2)) AS MM FROM tiposJoyas INNER JOIN (insumosFijos INNER JOIN (ensambleMontadura INNER JOIN (colores INNER JOIN (brillantes INNER JOIN ((modelos INNER JOIN modelosInsumos ON modelos.idModelo = modelosInsumos.idModelo) INNER JOIN modelosColores ON modelos.idModelo = modelosColores.idModelo) ON brillantes.idTipoBTE = modelos.BTE) ON colores.idColor = modelosColores.idColor) ON ensambleMontadura.idEnsamble = modelos.idEnsamble) ON insumosFijos.idInsumo = modelosInsumos.idInsumo) ON tiposJoyas.idTipoJoya = modelos.idTipo GROUP BY modelos.idModelo, modelos.nombre, tiposJoyas.nombreTipo, colores.color, brillantes.tamanoPiedra; thx.miffe Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now