Data Shaping Question (Syntax)

Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
can see only one example of data shaping in MSDN). How do I calculate
fields ? Starting with:
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
SomeNumber FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, ShipVia, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"RELATE CustomerID TO CustomerID)"
(from Northwind), I'd like to find out the syntax for sum-ming on, say,
ShipVia, (although it does not make any logical sense to sum on that
column). So, in other words, I'd like to add ***to the parent record***
(the one coming from Customers), a calculated column containing a sum
on the child (chapter), so for customer "ALFKI" I should add up 1, 2,
1, 3, 1, and 1, obtaining 9.
I can, of course, change it to
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, EmployeeID, OrderDate,
SUM(ShipVia) as SumOfShipVia FROM Orders " & _
"GROUP BY CustomerID, OrderID, EmployeeID, OrderDate} AS Orders
" & _
"RELATE CustomerID TO CustomerID)"
but then the SUM is not a sum, since it's still in the child chapter.
How do I show the sum in the parent record ?
Similar - This contains three levels: Customers, Orders, and
OrderDetails.
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ((SHAPE {SELECT CustomerID, OrderID, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, Quantity FROM
[Order Details]} AS Orderdetails " & _
"RELATE OrderID TO OrderID) As OrderDetails) " & _
"RELATE CustomerID TO CustomerID)"
What if I wanted to add to the child (Orders) a calculated column on a
column in the grandchild, OrderDetails, say, OrdersDetails.UnitPrice ?
What would be the syntax then ?
Thank you very much for your help.
Alex."Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166718883.058620.244010@.80g2000cwy.googlegroups.com...
> Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
> can see only one example of data shaping in MSDN). How do I calculate
> fields ? Starting with:
> . . ..
This data shaping syntax is an old, dead feature of ADO. Don't use it. In
.NET use multi-table DataSets, which are a much better solution for
client-side shaped data.
David|||Well, David, the thing is that I don't use dotnet - I have to write
some VBA code in Excel 2000 to show data in a hierarchical data grid,
so that the user can look at a list of workgroups with their details
(aggregate data at workgroup level), then click on a workgroup and
explode it so that the teams contained in that workgroup are seen, with
their details, and then clicks on one of the teams in the workgroup and
sees the contained team-members, with their details, kind of a treeview
(actually, a fancy datagrid). Since I don't have any 3rd party tools
available, I have to make it work with the default (MS) controls.
MSHFlexGrid is one of them, and it does exactly what the user needed
(show data in a tree-like structure).
So I should show:
root
-- list of workgroups, with agregate data (#hours worked, for
instance, by workgroup)
-- list of teams in current workgroup, with agregate data (#hours
worked by team)
-- list of workers in current team, with #hours worked by
worker
PS. All this data comes from some MSAccess tables.
Thank you very much.
David Browne wrote:
> "Radu" <cuca_macaii2000@.yahoo.com> wrote in message
> news:1166718883.058620.244010@.80g2000cwy.googlegroups.com...
> This data shaping syntax is an old, dead feature of ADO. Don't use it. I
n
> .NET use multi-table DataSets, which are a much better solution for
> client-side shaped data.
> David|||"Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166727210.486584.307260@.i12g2000cwa.googlegroups.com...
> Well, David, the thing is that I don't use dotnet - I have to write
> some VBA code in Excel 2000 to show data in a hierarchical data grid,
> so that the user can look at a list of workgroups with their details
> (aggregate data at workgroup level), then click on a workgroup and
> explode it so that the teams contained in that workgroup are seen, with
> their details, and then clicks on one of the teams in the workgroup and
> sees the contained team-members, with their details, kind of a treeview
> (actually, a fancy datagrid). Since I don't have any 3rd party tools
> available, I have to make it work with the default (MS) controls.
> MSHFlexGrid is one of them, and it does exactly what the user needed
> (show data in a tree-like structure).
> So I should show:
> root
> -- list of workgroups, with agregate data (#hours worked, for
> instance, by workgroup)
> -- list of teams in current workgroup, with agregate data (#hours
> worked by team)
> -- list of workers in current team, with #hours worked by
> worker
> PS. All this data comes from some MSAccess tables.
>
SO first off, this is posted in a SQL Server group. Your solution doesn't
involve SQL Server. If it did, I'd advise you to create views in SQL Server
that add the appropriate columns to the resultset. Perhaps you can do the
same in access with saved queries, or subqueries.
David
>