Thursday, April 25, 2013

[SQL 2012] Converting MS Access Query to SQL

[SQL 2012] Converting MS Access Query to SQL

Converting MS Access Query to SQL

Posted: 25 Apr 2013 02:46 AM PDT

I 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.ThanksJIM

BI MCSE

Posted: 24 Apr 2013 11:51 PM PDT

Hi,I want to become certified in SQL Server 2012 BI.I'm now reading the Self Passed Training Kit "Implementing a Data Warehouse with Microsoft SQL Server 2012 (exam 463)"I cann't find other self passed training kits to complete my studies in this subjet (BI).The exams that I need training kits are:466, 467Can someone tell me if there are self passed training kits for this exams?Thank you.P.S - The only BI certification in SQL Server is the MSCE BI?

Cannot create a new Service Broker in read-only database "msdb"

Posted: 24 Apr 2013 10:54 PM PDT

Hi I am unable to install DB Engine on SQLServer2012TITLE: Microsoft SQL Server 2012 Service Pack 1 Setup------------------------------The following error has occurred:Cannot create a new Service Broker in read-only database "msdb".ALTER DATABASE statement failed.For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=9775&ProdVer=11.00.3128&EvtType=0x7590AFD6%25400x9A41B222------------------------------BUTTONS:OK------------------------------

Distributed Replay Controller

Posted: 24 Apr 2013 05:03 AM PDT

I started an installation of SQL Server 2012 and I encountered the screen to specify the Distributed Replay Controller.Does the Distributed Replay Controller need to be a dedicated server?From what I read I would need to start SQL Server Setup to install Distributed Replay Controller on another machine before I can continue with the SQL Server Installation.Any help would be greatly appreciated.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog