Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting MS Access Query to SQL


Converting MS Access Query to SQL

Author
Message
Jimbofoxman
Jimbofoxman
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
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;

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.


This is how the query looks in Access

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.InvoiceNo
FROM tblSpecies INNER JOIN tblTrees ON tblSpecies.SpeciesID = tblTrees.Species;


This is the response I got, which still worked in Access but I get an error when trying to import into SQL 2012;



Below saved as qArboristRates;

TRANSFORM Max(Cost) as TheCost
SELECT 1 as BogusRowHeader
FROM tblArboristType
GROUP BY 1
Pivot "Rate" & ArboristType



Below 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.InvoiceNo
FROM qArboristRates, tblSpecies INNER JOIN tblTrees ON tblSpecies.SpeciesID = tblTrees.Species;


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.

Thanks
JIM
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
Jimbofoxman (4/25/2013)

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.

Thanks
JIM


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.Haz_PI, tblTrees.Haz_EC, tblTrees.Haz_Pests

Okay, 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.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jimbofoxman
Jimbofoxman
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
Yeah, Haz is Hazards or Hazardous

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.


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.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
I 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.


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.InvoiceNo
FROM
   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
   Wink AS drv





- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jimbofoxman
Jimbofoxman
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
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.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
Jimbofoxman (4/26/2013)
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.


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. :-)


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jimbofoxman
Jimbofoxman
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
Thanks Evil! Changed that out and it worked. Still got plenty to learn, but it will allow me to test it out. Thanks again!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search