Data Grid or Data Matrix...

[DebbieD]

Greetings,

This seems a very complicated task, so I welcome any
input. My boss wants a data grid or matrix of the top 6
orders with ordered items, and products, for a particular
customer and he wants to see it like this:
products down the left side
orders across the top
number of items under the respective order number column
across from the corresponding product.
I hope that makes sense.
I have three tables. Products, Orders, Orderitems. I
don't even know where to start. Many thanks in advance
for any input offered.

[Bob Barrows]

DebbieD wrote:

> Greetings,
>
> This seems a very complicated task, so I welcome any
> input. My boss wants a data grid or matrix of the top 6
> orders with ordered items, and products, for a particular
> customer and he wants to see it like this:
> products down the left side
> orders across the top
> number of items under the respective order number column
> across from the corresponding product.
> I hope that makes sense.
> I have three tables. Products, Orders, Orderitems. I
> don't even know where to start. Many thanks in advance
> for any input offered.

So you need a crosstab (also known as a pivot table). The first step is to
let us know what database you are using (version, as well). Also, if you
could give us some details about your tables, column names, datatypes,
relationships, it would get us that much further along in the process. Note,
if your tables have dozens of columns that aren't related to this question,
then we don't need to hear about them, just tell us about the relevant
columns and their datatypes.

Providing some sample data for us to work with will also help. Just a few
rows for each table - no need to use attachments.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[debbied]

Many thanks Bob. I am using a SQL2000 database.

I have three tables and have listed fields I need to
display next to the table name:
PRODUCTS - catalogid (int),name (text)
orders - orderid (int), lastname (text), firstname (text)
orderitems - numitems (int), unitprice (money)

orderitems is linked to orders with orderid
products is linked to orderitems with catalogid

I hope this helps. In the meantime, I'll look up things
on pivot tables.

Thanks again.

>-----Original Message-----
>DebbieD wrote:

>> Greetings,
>>
>> This seems a very complicated task, so I welcome any
>> input. My boss wants a data grid or matrix of the top

6

>> orders with ordered items, and products, for a

particular

>> customer and he wants to see it like this:
>> products down the left side
>> orders across the top
>> number of items under the respective order number

column

>> across from the corresponding product.
>> I hope that makes sense.
>> I have three tables. Products, Orders, Orderitems. I
>> don't even know where to start. Many thanks in advance
>> for any input offered.

>
>So you need a crosstab (also known as a pivot table).

The first step is to

>let us know what database you are using (version, as

well). Also, if you

>could give us some details about your tables, column

names, datatypes,

>relationships, it would get us that much further along

in the process. Note,

>if your tables have dozens of columns that aren't

related to this question,

>then we don't need to hear about them, just tell us

about the relevant

>columns and their datatypes.
>
>Providing some sample data for us to work with will also

help. Just a few

>rows for each table - no need to use attachments.
>
>Bob Barrows
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my

spam trap so I

>don't check it very often. If you must reply off-line,

then remove the

>"NO SPAM"
>
>
>.
>

[DebbieD]

Hi Bob,

I did exactly what I need in Access but it won't work in
my Active Server Page because my back end is SQL. How
can I translate this into a SQL query? Thanks!

Hello,

I've created this crosstab query in Access and works
great but my back end is SQL2000. How can this be
rewritten to pull from a SQL2000 database in an ASP?
Thank you very much in advance.

TRANSFORM Sum(qyMatrixTest.numitems) AS SumOfnumitems
SELECT qyMatrixTest.cname
FROM qyMatrixTest
WHERE (((qyMatrixTest.ocustomerid)=271))
GROUP BY qyMatrixTest.cname, qyMatrixTest.ocustomerid
PIVOT qyMatrixTest.orderid;

>-----Original Message-----
>DebbieD wrote:

>> Greetings,
>>
>> This seems a very complicated task, so I welcome any
>> input. My boss wants a data grid or matrix of the top

6

>> orders with ordered items, and products, for a

particular

>> customer and he wants to see it like this:
>> products down the left side
>> orders across the top
>> number of items under the respective order number

column

>> across from the corresponding product.
>> I hope that makes sense.
>> I have three tables. Products, Orders, Orderitems. I
>> don't even know where to start. Many thanks in advance
>> for any input offered.

>
>So you need a crosstab (also known as a pivot table).

The first step is to

>let us know what database you are using (version, as

well). Also, if you

>could give us some details about your tables, column

names, datatypes,

>relationships, it would get us that much further along

in the process. Note,

>if your tables have dozens of columns that aren't

related to this question,

>then we don't need to hear about them, just tell us

about the relevant

>columns and their datatypes.
>
>Providing some sample data for us to work with will also

help. Just a few

>rows for each table - no need to use attachments.
>
>Bob Barrows
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my

spam trap so I

>don't check it very often. If you must reply off-line,

then remove the

>"NO SPAM"
>
>
>.
>

[Bob Barrows]

DebbieD wrote:

> Hi Bob,
>
> I did exactly what I need in Access but it won't work in
> my Active Server Page because my back end is SQL. How
> can I translate this into a SQL query? Thanks!
>
> Hello,
>
> I've created this crosstab query in Access and works
> great but my back end is SQL2000. How can this be
> rewritten to pull from a SQL2000 database in an ASP?
> Thank you very much in advance.
>
> TRANSFORM Sum(qyMatrixTest.numitems) AS SumOfnumitems
> SELECT qyMatrixTest.cname
> FROM qyMatrixTest
> WHERE (((qyMatrixTest.ocustomerid)=271))
> GROUP BY qyMatrixTest.cname, qyMatrixTest.ocustomerid
> PIVOT qyMatrixTest.orderid;
>
>

Yes, it's easy in Access, but not so easy in SQL Server. Have you done a
Google search for crosstab and T-SQL? If so, you will find a lot of
solutions.

[url]http://www.google.com/search?sourceid=navclient&ie=UTF-8&oe=UTF-8&q=T%2DSQL+crosstab[/url]

The easiest solution may be to use an Access database with your SQL tables
linked in to run this Transform query. However, it may not perform very well
so YMMV.

If your budget can afford it, there's a tool called RAC
([url]http://www.rac4sql.com/[/url]) that makes crosstabs in SQL Server easy.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"