help with query please

[angelpr23]

hello all, i am trying to create a complecated query with ado shape sql statement, i am gonna use it in a routine for the desired for me results on a data report in visual basic 6 in my application.
Well i want this query:
SELECT Categories.CategoryName, Products.ProdName, (Sum(OrderDetails.ProdSumPrice)) AS synolo
FROM Products, OrderDetails, Categories
WHERE ((Categories.CategoryID=Products.CategoryID) AND (OrderDetails.ProdID=Products.ProdID))
GROUP BY Categories.CategoryName, Products.ProdName;

to convert it in sql shape command
i tried this:

sql1 = "Select CategoryName " & _
"from Categories Order By CategoryName;"
sql2 = "Select ProdName " & _
"from Products order by ProdName;"
sql3 = "Select sum(ProdSumPrice) from OrderDetails;"
Set rsRep = New Recordset
rsRep.CursorLocation = adUseClient
rsRep.Source = _
" SHAPE {" & sql1 & "} " & _
" APPEND ((SHAPE {" & sql2 & "} " & _
" APPEND ({" & sql3 & "} " & _
" RELATE ProdID TO ProdID))AS OrdDtProd" & _
" RELATE CategoryID TO CategoryID) AS CategProd "
rsRep.Open , conn 'and here i am getting runtime error
-2147217900 (80040e14) The column ProdID doesn't exist in the properly rowset.

I think that i am wrong in sql shape command by i don't understand the reason. Any help will be much appreciated.
Thank you all.

 

[genomon]

The following assumes a connection object conRport has been dimmed:

    conReport.Open "Provider=MSDataShape.1;Persist Security Info=False;Data Source=" & _
        "C:\Program Files\DMI\DMI.mdb;Data Provider=MICROSOFT.JET.OLEDB.4.0"

        'Create a hierarchical recordset to use with the report.
    strShape = "SHAPE {SELECT * FROM `tblReportHeader`}  AS RptInvoiceHeader " & _
        "APPEND ({SELECT * FROM `tblReportOptions`}  AS RptReportOptions " & _
        "RELATE 'INVOICE_NUM' TO 'INVOICE_NUM') AS RptReportOptions"

    rsReport.Open strShape, conReport, adOpenStatic, adLockOptimistic, -1

As far as I know, you cannot use another SHAPE command within an APPEND clause as you do above. Too bad this stuff is so poorly documented - hope this helps!

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[genomon]

OOPS! You will need this too:

       'Bind the Recordset to the Report
    Set drMemberInvoice.DataSource = rsReport
    drMemberInvoice.DataMember = rsReport.DataMember

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.