SQLServerCentral.com / SQL Server 2012 / SQL 2012 - General / Converting MS Access Query to SQL / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 20:24:31 GMT20RE: Converting MS Access Query to SQLhttp://www.sqlservercentral.com/Forums/Topic1446579-2799-1.aspxThanks Evil! Changed that out and it worked. Still got plenty to learn, but it will allow me to test it out. Thanks again!Fri, 26 Apr 2013 14:42:33 GMTJimbofoxmanRE: Converting MS Access Query to SQLhttp://www.sqlservercentral.com/Forums/Topic1446579-2799-1.aspx[quote][b]Jimbofoxman (4/26/2013)[/b][hr]Created a new view and pasted this in, everything seems fine, the table view and links show up, columns and aliases fill in. When I save I get these errorsss;Invalid Column Name 'ArbCosts'Invalid Column Name 'VehCosts'I tried changing the name to see if it was something weird with that name, but it still gave me the same errors.Sorry, just to new to SQL to know what's going on.[/quote]It's looking for these:Round((([ArbCosts]+[VehCost]+Which you set down below, which doesn't work because you can't use a column name defined at the same level.Copy and paste those functions that define those fields everywhere you use their names and it'll clean itself up. It'll be MESSY, but it will work. :-)Fri, 26 Apr 2013 13:20:50 GMTEvil Kraig FRE: Converting MS Access Query to SQLhttp://www.sqlservercentral.com/Forums/Topic1446579-2799-1.aspxCreated a new view and pasted this in, everything seems fine, the table view and links show up, columns and aliases fill in. When I save I get these errorsss;Invalid Column Name 'ArbCosts'Invalid Column Name 'VehCosts'I tried changing the name to see if it was something weird with that name, but it still gave me the same errors.Sorry, just to new to SQL to know what's going on.Fri, 26 Apr 2013 07:01:45 GMTJimbofoxmanRE: Converting MS Access Query to SQLhttp://www.sqlservercentral.com/Forums/Topic1446579-2799-1.aspxI believe this gets you where you need to be. It's untested obviously as I don't have equivalent schema/test data in my system.the use of the DRv is just to allow us to re-use the data in multiple places, since unlike in access you can't use the column name at the same level you define it.[code="sql"]SELECT t.TreeID, t.TreeSort, t.ContractNo, t.Species, s.Species AS Tree, t.TreeQty, t.Location, t.Landmark, t.PlantHealth, t.DBH, t.Height, t.BD, t.Buttress, t.FGOther, t.FGOtherNotes, t.SMTrunk, t.Distance, t.EquipAccess, t.TTvsB, t.Haz_PI, t.Haz_EC, t.Haz_Pests, t.Equip_Bobcat, t.Equip_Bobcat_Cost, t.Equip_Crane, t.Equip_CraneHrs, t.Equip_Crane_Cost, t.Equip_Lift, t.Equip_LiftID, t.Equip_Lift_Cost, t.Equip_Lift_Time, t.Equip_Stump, t.Equip_Stump_Cost, t.GM, t.A, t.Aa, t.At, t.Gl, t.WorkHrs, Round((([ArbCosts]+[VehCost]+40)/(1-[GM])*[WorkHrs])+([Equip_CraneHrs]*[Equip_Crane_Cost])+([Equip_Lift_Cost]*[Equip_Lift_Time]+[MaterialsCost]),0) AS SuggTreeCost, t.ProposalTreeCost, t.ConfirmationDate, t.Veh_CTC, t.Veh_CTC_Cost, t.Veh_PT, t.Veh_PT_Cost, t.Veh_1T, t.Veh_1T_Cost, t.Veh_Int, t.Veh_Int_Cost, drv.ACost * t.[A] AS ACost, drv.AaCost * t.[Aa] AS AaCost, drv.AtCost * [At] AS AtCost, drv.GlCost * [Gl] AS GlCost, (drv.[ACost] * t.a) + ( drv.[AaCost] * t.Aa) + (drv.[AtCost] * t.at) + (drv.[GlCost] * t.gl) AS ArbCosts, ([Equip_Bobcat_Cost]+[Equip_Stump_Cost]+[Veh_CTC_Cost]+[Veh_PT_Cost]+[Veh_1T_Cost]+[Veh_Int_Cost]) AS VehCost, t.HazNotes, t.OnProposal, t.CustObj, t.CustObjDesc, t.TOWID, t.Transfer, t.Timing, t.Equip_Lift_Time, t.MissDig, t.MissDigNo, t.Haz_Poop, t.Grouping, t.GroupedCost, t.MaterialsCost, t.Materials, [Equip_CraneHrs]*[Equip_Crane_Cost] AS CraneCosting, t.MethodNotes, t.InvoiceNoFROM tblSpecies AS s INNER JOIN tblTrees AS t ON s.SpeciesID = t.Species CROSS JOIN (SELECT ( SELECT top 1 Cost FROM tblArboristType WHERE ArboristType = 'A') AS Acost, ( SELECT top 1 Cost FROM tblArboristType WHERE ArboristType = 'Aa') AS AaCost, ( SELECT top 1 Cost FROM tblArboristType WHERE ArboristType = 'At') AS AtCost, ( SELECT top 1 Cost FROM tblArboristType WHERE ArboristType = 'Gl') AS GlCost ) AS drv[/code]Thu, 25 Apr 2013 14:43:27 GMTEvil Kraig FRE: Converting MS Access Query to SQLhttp://www.sqlservercentral.com/Forums/Topic1446579-2799-1.aspxYeah, Haz is Hazards or Hazardous[quote]You can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in a Visual Basic for Applications (VBA) module, a macro, a query expression, or a calculated control on a form or report.You can use the DLookup function to display the value of a field that isn't in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the ProductName field is in another table, the Products table. You could use the DLookup function in a calculated control to display the ProductName on the same form.[/quote]So in mine....ACost: (DLookUp("[Cost]","tblArboristType","[ArboristType] = 'A'"))*[A]I am looking at the field "Cost" in the table "tblArboristType" where the "ArboristType" = A (You probably got that though)In my case there will only ever be one record where the ArboristType = A, so I don't care if it only finds the first one.Thu, 25 Apr 2013 14:01:37 GMTJimbofoxmanRE: Converting MS Access Query to SQLhttp://www.sqlservercentral.com/Forums/Topic1446579-2799-1.aspx[quote][b]Jimbofoxman (4/25/2013)[/b][hr]I get an error when I try to import the Cross Tab Query using the SQL Server Migration Assistant. I get A2SS0042: Crosstabe query cannot be converted because it is not supported.If I try to manual add the view I get "Unable to parse query text." Then when I went to save it, ignoring the error I get "Incorrect syntax near 'TRANSFORM'. Incorrect syntax near keyword 'Pivot'. Any help would be greatly appreciated.ThanksJIM[/quote]Well, there's a few concerns here. But you'll have to bear with me. I've been out of Access so long I forget what DLookup even does. I think it acts like a correlated subquery but I'm not sure offhand.Primary problem: TRANSFORM is not a word in the T-SQL library. It belongs to the JET engine only, so that'll never head over to Sql Server properly.Secondary concern: PIVOT in T-SQL is syntactically rather different.Third concern: That's a VERY wide view. You're trucking a lot of data around with that puppy.Final concern and slight amusement: LOLicats built your database ;-) tblTrees.[b]Haz[/b]_PI, tblTrees.[b]Haz[/b]_EC, tblTrees.[b]Haz[/b]_PestsOkay, the final one probably doesn't matter much and I'm sure is meant as Hazardous instead of has, but it amused me.If you can remind me of the particulars of DLookup again I can probably help get this re-written to what you're looking for.Thu, 25 Apr 2013 13:50:01 GMTEvil Kraig FConverting MS Access Query to SQLhttp://www.sqlservercentral.com/Forums/Topic1446579-2799-1.aspxI admit I am really green with SQL. I signed up with Access Hosting for a trial of SQL 2012 to test out with a database I am writing. It works great in Access 2013, but now we are looking at a couple users that would be on the road connecting to it, besides the 3-5 in the office. I posted this on Utteraccess got an initial response but still am kinda stuck. So this was my initial post;[quote]I am having a problem with one of my queries that has the following 4 DLookups.4 DLookups are;ACost: (DLookUp("[Cost]","tblArboristType","[ArboristType] = 'A'"))*[A]AaCost: (DLookUp("[Cost]","tblArboristType","[ArboristType] = 'Aa'"))*[Aa]AtCost: (DLookUp("[Cost]","tblArboristType","[ArboristType] = 'At'"))*[At]GlCost: (DLookUp("[Cost]","tblArboristType","[ArboristType] = 'Gl'"))*[Gl]They are in a query that is essentially the recordset for a form. The 4 costs are used in conjunction with the A, Aa, At and G fields in the form where people put in a value which is then all used to calculate out a suggested cost.Here is the whole query. I can't link the ArboristType table to the query because their is no reference, it's just strictly there for costing.[/quote]This is how the query looks in Access[quote]SELECT tblTrees.TreeID, tblTrees.TreeSort, tblTrees.ContractNo, tblTrees.Species, tblSpecies.Species AS Tree, tblTrees.TreeQty, tblTrees.Location, tblTrees.Landmark, tblTrees.PlantHealth, tblTrees.DBH, tblTrees.Height, tblTrees.BD, tblTrees.Buttress, tblTrees.FGOther, tblTrees.FGOtherNotes, tblTrees.SMTrunk, tblTrees.Distance, tblTrees.EquipAccess, tblTrees.TTvsB, tblTrees.Haz_PI, tblTrees.Haz_EC, tblTrees.Haz_Pests, tblTrees.Equip_Bobcat, tblTrees.Equip_Bobcat_Cost, tblTrees.Equip_Crane, tblTrees.Equip_CraneHrs, tblTrees.Equip_Crane_Cost, tblTrees.Equip_Lift, tblTrees.Equip_LiftID, tblTrees.Equip_Lift_Cost, tblTrees.Equip_Lift_Time, tblTrees.Equip_Stump, tblTrees.Equip_Stump_Cost, tblTrees.GM, tblTrees.A, tblTrees.Aa, tblTrees.At, tblTrees.Gl, tblTrees.WorkHrs, Round((([ArbCosts]+[VehCost]+40)/(1-[GM])*[WorkHrs])+([Equip_CraneHrs]*[Equip_Crane_Cost])+([Equip_Lift_Cost]*[Equip_Lift_Time]+[MaterialsCost]),0) AS SuggTreeCost, tblTrees.ProposalTreeCost, tblTrees.ConfirmationDate, tblTrees.Veh_CTC, tblTrees.Veh_CTC_Cost, tblTrees.Veh_PT, tblTrees.Veh_PT_Cost, tblTrees.Veh_1T, tblTrees.Veh_1T_Cost, tblTrees.Veh_Int, tblTrees.Veh_Int_Cost, (DLookUp("[Cost]","tblArboristType","[ArboristType] = 'A'"))*[A] AS ACost, (DLookUp("[Cost]","tblArboristType","[ArboristType] = 'Aa'"))*[Aa] AS AaCost, (DLookUp("[Cost]","tblArboristType","[ArboristType] = 'At'"))*[At] AS AtCost, (DLookUp("[Cost]","tblArboristType","[ArboristType] = 'Gl'"))*[Gl] AS GlCost, [ACost]+[AaCost]+[AtCost]+[GlCost] AS ArbCosts, ([Equip_Bobcat_Cost]+[Equip_Stump_Cost]+[Veh_CTC_Cost]+[Veh_PT_Cost]+[Veh_1T_Cost]+[Veh_Int_Cost]) AS VehCost, tblTrees.HazNotes, tblTrees.OnProposal, tblTrees.CustObj, tblTrees.CustObjDesc, tblTrees.TOWID, tblTrees.Transfer, tblTrees.Timing, tblTrees.Equip_Lift_Time, tblTrees.MissDig, tblTrees.MissDigNo, tblTrees.Haz_Poop, tblTrees.Grouping, tblTrees.GroupedCost, tblTrees.MaterialsCost, tblTrees.Materials, [Equip_CraneHrs]*[Equip_Crane_Cost] AS CraneCosting, tblTrees.MethodNotes, tblTrees.InvoiceNoFROM tblSpecies INNER JOIN tblTrees ON tblSpecies.SpeciesID = tblTrees.Species;[/quote]This is the response I got, which still worked in Access but I get an error when trying to import into SQL 2012;[quote]Below saved as qArboristRates;TRANSFORM Max(Cost) as TheCostSELECT 1 as BogusRowHeaderFROM tblArboristTypeGROUP BY 1Pivot "Rate" & ArboristTypeBelow is the query that refers to the above query;SELECT tblTrees.TreeID, tblTrees.TreeSort, tblTrees.ContractNo, tblTrees.Species, tblSpecies.Species AS Tree, tblTrees.TreeQty, tblTrees.Location, tblTrees.Landmark, tblTrees.PlantHealth, tblTrees.DBH, tblTrees.Height, tblTrees.BD, tblTrees.Buttress, tblTrees.FGOther, tblTrees.FGOtherNotes, tblTrees.SMTrunk, tblTrees.Distance, tblTrees.EquipAccess, tblTrees.TTvsB, tblTrees.Haz_PI, tblTrees.Haz_EC, tblTrees.Haz_Pests, tblTrees.Equip_Bobcat, tblTrees.Equip_Bobcat_Cost, tblTrees.Equip_Crane, tblTrees.Equip_CraneHrs, tblTrees.Equip_Crane_Cost, tblTrees.Equip_Lift, tblTrees.Equip_LiftID, tblTrees.Equip_Lift_Cost, tblTrees.Equip_Lift_Time, tblTrees.Equip_Stump, tblTrees.Equip_Stump_Cost, tblTrees.GM, tblTrees.A, tblTrees.Aa, tblTrees.At, tblTrees.Gl, tblTrees.WorkHrs, Round((([ArbCosts]+[VehCost]+40)/(1-[GM])*[WorkHrs])+([Equip_CraneHrs]*[Equip_Crane_Cost])+([Equip_Lift_Cost]*[Equip_Lift_Time]+[MaterialsCost]),0) AS SuggTreeCost, tblTrees.ProposalTreeCost, tblTrees.ConfirmationDate, tblTrees.Veh_CTC, tblTrees.Veh_CTC_Cost, tblTrees.Veh_PT, tblTrees.Veh_PT_Cost, tblTrees.Veh_1T, tblTrees.Veh_1T_Cost, tblTrees.Veh_Int, tblTrees.Veh_Int_Cost, RateA *[A] AS ACost, RateAA *[Aa] AS AaCost, RateAt*[At] AS AtCost, RateG1*[Gl] AS GlCost, RateA *[A] +RateAA *[Aa] + RateAt*[At] + RateG1*[Gl] AS ArbCosts, ([Equip_Bobcat_Cost]+[Equip_Stump_Cost]+[Veh_CTC_Cost]+[Veh_PT_Cost]+[Veh_1T_Cost]+[Veh_Int_Cost]) AS VehCost, tblTrees.HazNotes, tblTrees.OnProposal, tblTrees.CustObj, tblTrees.CustObjDesc, tblTrees.TOWID, tblTrees.Transfer, tblTrees.Timing, tblTrees.Equip_Lift_Time, tblTrees.MissDig, tblTrees.MissDigNo, tblTrees.Haz_Poop, tblTrees.Grouping, tblTrees.GroupedCost, tblTrees.MaterialsCost, tblTrees.Materials, [Equip_CraneHrs]*[Equip_Crane_Cost] AS CraneCosting, tblTrees.MethodNotes, tblTrees.InvoiceNoFROM qArboristRates, tblSpecies INNER JOIN tblTrees ON tblSpecies.SpeciesID = tblTrees.Species;[/quote]I get an error when I try to import the Cross Tab Query using the SQL Server Migration Assistant. I get A2SS0042: Crosstabe query cannot be converted because it is not supported.If I try to manual add the view I get "Unable to parse query text." Then when I went to save it, ignoring the error I get "Incorrect syntax near 'TRANSFORM'. Incorrect syntax near keyword 'Pivot'. Any help would be greatly appreciated.ThanksJIMThu, 25 Apr 2013 09:46:00 GMTJimbofoxman