Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting MS Access Query to SQL Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 9:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:41 PM
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
Post #1446579
Posted Thursday, April 25, 2013 1:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:15 PM
Points: 5,383, Visits: 7,454
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
Post #1446696
Posted Thursday, April 25, 2013 2:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:41 PM
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.
Post #1446705
Posted Thursday, April 25, 2013 2:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:15 PM
Points: 5,383, Visits: 7,454
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
) 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
Post #1446722
Posted Friday, April 26, 2013 7:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:41 PM
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.
Post #1446946
Posted Friday, April 26, 2013 1:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:15 PM
Points: 5,383, Visits: 7,454
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
Post #1447141
Posted Friday, April 26, 2013 2:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:41 PM
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!
Post #1447162
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse