Data Grid or Data Matrix...

D

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.
 
B

Bob Barrows

DebbieD said:
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
 
D

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.
 
D

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;
 
B

Bob Barrows

DebbieD said:
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.

http://www.google.com/search?sourceid=navclient&ie=UTF-8&oe=UTF-8&q=T-SQL+crosstab

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
(http://www.rac4sql.com/) that makes crosstabs in SQL Server easy.

HTH,
Bob Barrows
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top