SQL Help for Kits Components Report

Using Sql - I can get it to work . Now I'amtrying to make it into a QRP report.

Step1 create view viemkitcomponent as select i.itemlookupcode, i.description, i.price, k.componentitemid, k.quantity from item i join kit k on i.id = k.kititemid

Step2 select v.itemlookupcode, v.description, i.itemlookupcode, v.quantity from viemkitcomponent v join item i on v.componentitemid = i.id

View in try to creat a QRP see below - I get the following error:

-2147217900 The multi-part identifier "Item.Description" could not be bound.

Any help would be appreciated.

Dan

//--- Report Summary --- //

Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Kit Details" PageOrientation = pageorientationLandscape WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 0 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViemKitComponent') DROP VIEW ViemKitComponent" PreQuery2 create view viemkitcomponent as select i.itemlookupcode, i.description, i.price, k.componentitemid, k.quantity from item i join kit k on i.id = k.kititemid

TablesQueried = "from viemkitcomponent v join item i on v.componentitemid = i.id" SelCriteria = "" GroupBy = "Item.ItemLookupCode, ViewKitComponent.Itemlookupcode" SortOrder = "" End ReportSummary

//--- Title Rows ---//

Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 16 Color = "Blue" End TitleRow

Begin TitleRow Text = "" Font = "Arial" FontBold = True FontSize = 12 Color = "Black" End TitleRow

Begin TitleRow Text = "Generated On " Font = "Arial" FontBold = True FontSize = 10 Color = "Black" End TitleRow

Begin TitleRow Text = "" Font = "Arial" FontBold = False FontSize = 6 Color = "Black" End TitleRow

//--- Filters ---//

//--- Columns ---//

Begin Column FieldName = "Item.ItemLookupCode" DrillDownFieldName = "Item.ItemLookupCode" DrillDownReportName = "" StoreIDFieldName = "" Title = "Item Lookup Code" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1395 GroupMethod = GroupMethodNone ColFormat = "" End Column

Begin Column FieldName = "Item.Description" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Item Description" VBDataType = vbString Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2550 GroupMethod = GroupMethodNone ColFormat = "" End Column

Begin Column FieldName = "ViewKitComponent.Itemlookupcode" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Kit ILC" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 2550 GroupMethod = GroupMethodNone ColFormat = "" End Column

Begin Column FieldName = "ViewKitComponent.Description" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Kit Description" VBDataType = vbString Formula = "" ColHidden = True ColNotDisplayable = False FilterDisabled = False ColWidth = 1000 GroupMethod = groupmethodNone ColFormat = "" ColAlignment = flexAlignLeftCenter End Column

Begin Column FieldName = "ViewKitComponent.Quantity" DrillDownFieldName = "" DrillDownReportName = "" StoreIDFieldName = "" Title = "Kit Quantity" VBDataType = vbLong Formula = "" ColHidden = False ColNotDisplayable = False FilterDisabled = True ColWidth = 465 GroupMethod = groupmethodNone ColFormat = "" End Column

HI dANY, Update with my lines and it will work, remember you have changed the alieas of the item table, which no need.

//--- Report Summary --- //

Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Kit Details" PageOrientation = pageorientationLandscape WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 0 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viemkitcomponent') DROP VIEW viemkitcomponent"

PreQuery2 CREATE VIEW viemkitcomponent AS select i.itemlookupcode, i.description, i.price, k.componentitemid, k.quantity from item i join kit k on i.id = k.kititemid

TablesQueried = "FROM viemkitcomponent v JOIN item on v.componentitemid ITEM.id" SelCriteria = "" GroupBy = "Item.ItemLookupCode, ViewKitComponent.Itemlookupcode" SortOrder = "" End ReportSummary

Regards Akber

mODIFIED //--- Report Summary --- //

Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Kit Details" PageOrientation = pageorientationLandscape WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 0 GroupDescription = "" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'viemkitcomponent') DROP VIEW viemkitcomponent"

PreQuery2 CREATE VIEW viemkitcomponent AS select i.itemlookupcode, i.description, i.price, k.componentitemid, k.quantity from item i join kit k on i.id = k.kititemid

TablesQueried = "FROM viemkitcomponent v JOIN item on v.componentitemid ITEM.id" SelCriteria = "" GroupBy = "Item.ItemLookupCode, ViewKitComponent.Itemlookupcode" SortOrder = "" End ReportSummary

"Dan-902" wrote:

What do you mean to changed the alieas of the item table, which no need. I only need thr following fields: Item.Itemlookupcode, item.description, item.price , (the Itemlookupcode and description for the Kit.componentitemid), Kit.quantity

Thanks aga> HI dANY,

Dear Danny, You will get it what you want, what I meant was that before in your query you have given the alias "i" to table item.Which I mean to say no need. Because one side you are using the table and other site alias, you were getting error due to that before but modifed the code which I sent to you and it will work, Let me know if you face any problem.

"Danny" wrote:

v.componentitemid