April 25, 2013 at 9:46 am
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
April 25, 2013 at 1:50 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 25, 2013 at 2:01 pm
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.
April 25, 2013 at 2:43 pm
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
ONs.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
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 26, 2013 at 7:01 am
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.
April 26, 2013 at 1:20 pm
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. 🙂
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 26, 2013 at 2:42 pm
Thanks Evil! Changed that out and it worked. Still got plenty to learn, but it will allow me to test it out. Thanks again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply