Data Grid or Data Matrix...

Discussion in 'ASP General' started by DebbieD, Oct 26, 2003.

  1. DebbieD

    DebbieD Guest

    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.
     
    DebbieD, Oct 26, 2003
    #1
    1. Advertising

  2. DebbieD

    Bob Barrows Guest

    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, Oct 26, 2003
    #2
    1. Advertising

  3. DebbieD

    debbied Guest

    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, Oct 26, 2003
    #3
  4. DebbieD

    DebbieD Guest

    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"
    >
    >
    >.
    >
     
    DebbieD, Oct 26, 2003
    #4
  5. DebbieD

    Bob Barrows Guest

    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.

    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

    --
    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, Oct 26, 2003
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Girish
    Replies:
    4
    Views:
    721
    Patrick.O.Ige
    Jul 19, 2005
  2. lvcargnini

    Matrix composed by two matrix

    lvcargnini, Jul 4, 2006, in forum: VHDL
    Replies:
    3
    Views:
    2,721
    Jonathan Bromley
    Jul 5, 2006
  3. Holgerson

    Matrix*Vector and Vector*Matrix

    Holgerson, Oct 25, 2007, in forum: C++
    Replies:
    3
    Views:
    436
    Holgerson
    Oct 26, 2007
  4. Terry Reedy
    Replies:
    0
    Views:
    587
    Terry Reedy
    Apr 2, 2009
  5. Robert Kern
    Replies:
    0
    Views:
    624
    Robert Kern
    Apr 2, 2009
Loading...

Share This Page