Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Table Design and transform sql

Discussion in 'Information Technology' started by IMA, Nov 6, 2005.

  1. IMA

    IMA
    Expand Collapse
    Guest

    I'd like to have the total of each items.
    Required style is shown below.
    xGroup |A | B | C
    ----------------------------
    01| 0 | 0 | 4
    02| 0 | 2 | 4

    an original table would be like this
    xGroup | A | B | C
    ------------------------------
    02 | 0 | 0 | 2
    01 | 0 | 0 | 2
    02 | 0 | 0 | 2
    01 | 0 | 0 | 2
    02 | 0 | 2 | 0

    Items might be changed and increased in the future.
    So, I thought the original table should have selective field about items.
    and minimum number or fields.
    [Data table]
    xGroup | ITEMS | Q
    --------------------------
    02 | C | 2
    01 | C | 2
    02 | C | 2
    01 | C | 2
    01 | B | 2

    Appending another [ITEM tables]
    to be selected in a [ITEM] field on [Data table].
    Then , for Sum up
    Transfer query would be useful.
    But Transfer query
    omits unselected items count.

    xGroup | B | C |
    ---------------------
    01| 0 | 4 |
    02| 2 | 4 |

    How do I count(show) unselected items?
    Or should I prepare each item fields?
    Both way, VBA can adjust for required style of the total table .

    any comment about "the basic of table design" or query(SQL)
    I'd appreciate it .
     
  2. Loading...

    Similar Threads Forum Date
    As A Child, Public Marches Of Sikhism Made Me Uncomfortable. They Still Do . Why ? Whats The Logic Hard Talk Oct 16, 2016
    Arts/Society Backyard Vegetable Garden Language, Arts & Culture Dec 27, 2013
    Heritage Now a Vegetable Market, Ranjit Singh's Royal Haveli a Picture of Neglect History of Sikhism Nov 11, 2013
    Sikhism Helium: 1984 and the "Periodic Table of Hate" (Jaspreet Singh) Book Reviews & Editorials Oct 28, 2013
    Heritage How our entire history was dumped in a horse stable History of Sikhism Oct 28, 2013

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Use this structure --
    [Data table]
    xGroup | ITEMS | Q
    --------------------------
    02 | C | 2
    01 | C | 2
    02 | C | 2
    01 | C | 2
    01 | B | 2

    Use this query for simple selection of item --
    TRANSFORM Sum(DataTable.Q) AS [The Value]
    SELECT DataTable.xGroup
    FROM DataTable
    WHERE (((DataTable.Item)="B" Or (DataTable.Item)="C" Or (DataTable.Item)="A"))
    GROUP BY DataTable.xGroup
    PIVOT DataTable.Item;

    For more complex list use another table with Item & Active fields.
    Use this query--
    TRANSFORM Sum(DataTable.Q) AS [The Value]
    SELECT DataTable.xGroup
    FROM DataTable INNER JOIN ItemList ON DataTable.Item = ItemList.Item
    WHERE (((ItemList.Active)="X"))
    GROUP BY DataTable.xGroup
    PIVOT DataTable.Item;


    "IMA" wrote:

    > I'd like to have the total of each items.
    > Required style is shown below.
    > xGroup |A | B | C
    > ----------------------------
    > 01| 0 | 0 | 4
    > 02| 0 | 2 | 4
    >
    > an original table would be like this
    > xGroup | A | B | C
    > ------------------------------
    > 02 | 0 | 0 | 2
    > 01 | 0 | 0 | 2
    > 02 | 0 | 0 | 2
    > 01 | 0 | 0 | 2
    > 02 | 0 | 2 | 0
    >
    > Items might be changed and increased in the future.
    > So, I thought the original table should have selective field about items.
    > and minimum number or fields.
    > [Data table]
    > xGroup | ITEMS | Q
    > --------------------------
    > 02 | C | 2
    > 01 | C | 2
    > 02 | C | 2
    > 01 | C | 2
    > 01 | B | 2
    >
    > Appending another [ITEM tables]
    > to be selected in a [ITEM] field on [Data table].
    > Then , for Sum up
    > Transfer query would be useful.
    > But Transfer query
    > omits unselected items count.
    >
    > xGroup | B | C |
    > ---------------------
    > 01| 0 | 4 |
    > 02| 2 | 4 |
    >
    > How do I count(show) unselected items?
    > Or should I prepare each item fields?
    > Both way, VBA can adjust for required style of the total table .
    >
    > any comment about "the basic of table design" or query(SQL)
    > I'd appreciate it .
    >
    >
    >
     
  4. IMA

    IMA
    Expand Collapse
    Guest

    Thank you for your quick respond, KARL.
    I understand that
    selective items, gathering together in one field.
    is not so bad idea.

    But, how can I show every items
    (not existing item in data_table but existing in item table)
    in a view for a total of Qty?
    [TRANSFORM_VIEW] [IDEAL VIEW]
    xGroup | B | C | xGroup | A | B | C |
    --------------------- ----> -------------------------
    01| 0 | 4 | 01| 0 | 0 | 4 |
    02| 2 | 4 | 02| 0 | 2 | 4 |

    Items which is not selected in a data table is ignored in Transfer SQL.
    This is a matter of concern to me. This doesn't adjust to a requested style.
    Full item fields are necessary, because,
    I am going to sticking together some of these views with UNION sql,
    But, after all LEFT JOIN would solve this, I remind the LEFT JOIN.
    Thank you again.

    "KARL DEWEY" wrote:

    > Use this structure --
    > [Data table]
    > xGroup | ITEMS | Q
    > ---------------------------
    > 02 | C | 2
    > 01 | C | 2
    > 02 | C | 2
    > 01 | C | 2
    > 01 | B | 2
    >
    > Use this query for simple selection of item --
    > TRANSFORM Sum(DataTable.Q) AS [The Value]
    > SELECT DataTable.xGroup
    > FROM DataTable
    > WHERE (((DataTable.Item)="B" Or (DataTable.Item)="C" Or (DataTable.Item)="A"))
    > GROUP BY DataTable.xGroup
    > PIVOT DataTable.Item;
    >
    > For more complex list use another table with Item & Active fields.
    > Use this query--
    > TRANSFORM Sum(DataTable.Q) AS [The Value]
    > SELECT DataTable.xGroup
    > FROM DataTable INNER JOIN ItemList ON DataTable.Item = ItemList.Item
    > WHERE (((ItemList.Active)="X"))
    > GROUP BY DataTable.xGroup
    > PIVOT DataTable.Item;
    >
    >
    > "IMA" wrote:
    >
    > > I'd like to have the total of each items.
    > > Required style is shown below.
    > > xGroup |A | B | C
    > > ----------------------------
    > > 01| 0 | 0 | 4
    > > 02| 0 | 2 | 4
    > >
    > > an original table would be like this
    > > xGroup | A | B | C
    > > ------------------------------
    > > 02 | 0 | 0 | 2
    > > 01 | 0 | 0 | 2
    > > 02 | 0 | 0 | 2
    > > 01 | 0 | 0 | 2
    > > 02 | 0 | 2 | 0
    > >
    > > Items might be changed and increased in the future.
    > > So, I thought the original table should have selective field about items.
    > > and minimum number or fields.
    > > [Data table]
    > > xGroup | ITEMS | Q
    > > --------------------------
    > > 02 | C | 2
    > > 01 | C | 2
    > > 02 | C | 2
    > > 01 | C | 2
    > > 01 | B | 2
    > >
    > > Appending another [ITEM tables]
    > > to be selected in a [ITEM] field on [Data table].
    > > Then , for Sum up
    > > Transfer query would be useful.
    > > But Transfer query
    > > omits unselected items count.
    > >
    > > xGroup | B | C |
    > > ---------------------
    > > 01| 0 | 4 |
    > > 02| 2 | 4 |
    > >
    > > How do I count(show) unselected items?
    > > Or should I prepare each item fields?
    > > Both way, VBA can adjust for required style of the total table .
    > >
    > > any comment about "the basic of table design" or query(SQL)
    > > I'd appreciate it .
    > >
    > >
    > >
     
  5. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    You will get all of the columns if you change the last line of the SQL to
    read --
    PIVOT DataTable.Item In ("A","B","C","D");

    I am not sure how to fill blank space with zero.

    "IMA" wrote:

    > Thank you for your quick respond, KARL.
    > I understand that
    > selective items, gathering together in one field.
    > is not so bad idea.
    >
    > But, how can I show every items
    > (not existing item in data_table but existing in item table)
    > in a view for a total of Qty?
    > [TRANSFORM_VIEW] [IDEAL VIEW]
    > xGroup | B | C | xGroup | A | B | C |
    > --------------------- ----> -------------------------
    > 01| 0 | 4 | 01| 0 | 0 | 4 |
    > 02| 2 | 4 | 02| 0 | 2 | 4 |
    >
    > Items which is not selected in a data table is ignored in Transfer SQL.
    > This is a matter of concern to me. This doesn't adjust to a requested style.
    > Full item fields are necessary, because,
    > I am going to sticking together some of these views with UNION sql,
    > But, after all LEFT JOIN would solve this, I remind the LEFT JOIN.
    > Thank you again.
    >
    > "KARL DEWEY" wrote:
    >
    > > Use this structure --
    > > [Data table]
    > > xGroup | ITEMS | Q
    > > ---------------------------
    > > 02 | C | 2
    > > 01 | C | 2
    > > 02 | C | 2
    > > 01 | C | 2
    > > 01 | B | 2
    > >
    > > Use this query for simple selection of item --
    > > TRANSFORM Sum(DataTable.Q) AS [The Value]
    > > SELECT DataTable.xGroup
    > > FROM DataTable
    > > WHERE (((DataTable.Item)="B" Or (DataTable.Item)="C" Or (DataTable.Item)="A"))
    > > GROUP BY DataTable.xGroup
    > > PIVOT DataTable.Item;
    > >
    > > For more complex list use another table with Item & Active fields.
    > > Use this query--
    > > TRANSFORM Sum(DataTable.Q) AS [The Value]
    > > SELECT DataTable.xGroup
    > > FROM DataTable INNER JOIN ItemList ON DataTable.Item = ItemList.Item
    > > WHERE (((ItemList.Active)="X"))
    > > GROUP BY DataTable.xGroup
    > > PIVOT DataTable.Item;
    > >
    > >
    > > "IMA" wrote:
    > >
    > > > I'd like to have the total of each items.
    > > > Required style is shown below.
    > > > xGroup |A | B | C
    > > > ----------------------------
    > > > 01| 0 | 0 | 4
    > > > 02| 0 | 2 | 4
    > > >
    > > > an original table would be like this
    > > > xGroup | A | B | C
    > > > ------------------------------
    > > > 02 | 0 | 0 | 2
    > > > 01 | 0 | 0 | 2
    > > > 02 | 0 | 0 | 2
    > > > 01 | 0 | 0 | 2
    > > > 02 | 0 | 2 | 0
    > > >
    > > > Items might be changed and increased in the future.
    > > > So, I thought the original table should have selective field about items.
    > > > and minimum number or fields.
    > > > [Data table]
    > > > xGroup | ITEMS | Q
    > > > --------------------------
    > > > 02 | C | 2
    > > > 01 | C | 2
    > > > 02 | C | 2
    > > > 01 | C | 2
    > > > 01 | B | 2
    > > >
    > > > Appending another [ITEM tables]
    > > > to be selected in a [ITEM] field on [Data table].
    > > > Then , for Sum up
    > > > Transfer query would be useful.
    > > > But Transfer query
    > > > omits unselected items count.
    > > >
    > > > xGroup | B | C |
    > > > ---------------------
    > > > 01| 0 | 4 |
    > > > 02| 2 | 4 |
    > > >
    > > > How do I count(show) unselected items?
    > > > Or should I prepare each item fields?
    > > > Both way, VBA can adjust for required style of the total table .
    > > >
    > > > any comment about "the basic of table design" or query(SQL)
    > > > I'd appreciate it .
    > > >
    > > >
    > > >
     
  6. IMA

    IMA
    Expand Collapse
    Guest

    About Null/Zero, someone wrote in this discussion group.
    he is introducing Nz() function with Some data type Convertion functions.
    Cint() Clng() Cdbl()
    in
    Answer of
    Subject: Re: How do i replace empty cells with zeros after a cross tab query
    12/17/2004 6:35 AM PST
    By: Allen Browne

    In the project I am concerning about full items fields,
    I can use IIF() function, if I made another Query based on the Query...

    But this would be faseter
    TRANSFORM Clng(Nz(Sum(T_Data.Qty),0)) AS [The value]
    SELECT T_Data.GroupX
    FROM T_Data RIGHT JOIN T_Item ON T_Data.ITEM = T_Item.ID
    GROUP BY T_Data.GroupX
    PIVOT T_ITEM.ITEM;

    It seems complicated, but, SQL is easier to understand for others,
    than VBA code... I think.


    I made some mistakes in the former message,
    RIGHT JOIN is correct, LEFT JOIN ON FULL ITEM is wrong
    another mistake is that I forgot to tell that
    PIVOT should be following a field of the full set of item table, ID.ITEM TABLE
    otherwise missing items (columns) are still ignored.
    ....cause DATA TABLE doesn't have those.



    "KARL DEWEY" wrote:

    > You will get all of the columns if you change the last line of the SQL to
    > read --
    > PIVOT DataTable.Item In ("A","B","C","D");
    >
    > I am not sure how to fill blank space with zero.
    >
    > "IMA" wrote:
    >
    > > Thank you for your quick respond, KARL.
    > > I understand that
    > > selective items, gathering together in one field.
    > > is not so bad idea.
    > >
    > > But, how can I show every items
    > > (not existing item in data_table but existing in item table)
    > > in a view for a total of Qty?
    > > [TRANSFORM_VIEW] [IDEAL VIEW]
    > > xGroup | B | C | xGroup | A | B | C |
    > > --------------------- ----> -------------------------
    > > 01| 0 | 4 | 01| 0 | 0 | 4 |
    > > 02| 2 | 4 | 02| 0 | 2 | 4 |
    > >
    > > Items which is not selected in a data table is ignored in Transfer SQL.
    > > This is a matter of concern to me. This doesn't adjust to a requested style.
    > > Full item fields are necessary, because,
    > > I am going to sticking together some of these views with UNION sql,
    > > But, after all LEFT JOIN would solve this, I remind the LEFT JOIN.
    > > Thank you again.
    > >
    > > "KARL DEWEY" wrote:
    > >
    > > > Use this structure --
    > > > [Data table]
    > > > xGroup | ITEMS | Q
    > > > ---------------------------
    > > > 02 | C | 2
    > > > 01 | C | 2
    > > > 02 | C | 2
    > > > 01 | C | 2
    > > > 01 | B | 2
    > > >
    > > > Use this query for simple selection of item --
    > > > TRANSFORM Sum(DataTable.Q) AS [The Value]
    > > > SELECT DataTable.xGroup
    > > > FROM DataTable
    > > > WHERE (((DataTable.Item)="B" Or (DataTable.Item)="C" Or (DataTable.Item)="A"))
    > > > GROUP BY DataTable.xGroup
    > > > PIVOT DataTable.Item;
    > > >
    > > > For more complex list use another table with Item & Active fields.
    > > > Use this query--
    > > > TRANSFORM Sum(DataTable.Q) AS [The Value]
    > > > SELECT DataTable.xGroup
    > > > FROM DataTable INNER JOIN ItemList ON DataTable.Item = ItemList.Item
    > > > WHERE (((ItemList.Active)="X"))
    > > > GROUP BY DataTable.xGroup
    > > > PIVOT DataTable.Item;
    > > >
    > > >
    > > > "IMA" wrote:
    > > >
    > > > > I'd like to have the total of each items.
    > > > > Required style is shown below.
    > > > > xGroup |A | B | C
    > > > > ----------------------------
    > > > > 01| 0 | 0 | 4
    > > > > 02| 0 | 2 | 4
    > > > >
    > > > > an original table would be like this
    > > > > xGroup | A | B | C
    > > > > ------------------------------
    > > > > 02 | 0 | 0 | 2
    > > > > 01 | 0 | 0 | 2
    > > > > 02 | 0 | 0 | 2
    > > > > 01 | 0 | 0 | 2
    > > > > 02 | 0 | 2 | 0
    > > > >
    > > > > Items might be changed and increased in the future.
    > > > > So, I thought the original table should have selective field about items.
    > > > > and minimum number or fields.
    > > > > [Data table]
    > > > > xGroup | ITEMS | Q
    > > > > --------------------------
    > > > > 02 | C | 2
    > > > > 01 | C | 2
    > > > > 02 | C | 2
    > > > > 01 | C | 2
    > > > > 01 | B | 2
    > > > >
    > > > > Appending another [ITEM tables]
    > > > > to be selected in a [ITEM] field on [Data table].
    > > > > Then , for Sum up
    > > > > Transfer query would be useful.
    > > > > But Transfer query
    > > > > omits unselected items count.
    > > > >
    > > > > xGroup | B | C |
    > > > > ---------------------
    > > > > 01| 0 | 4 |
    > > > > 02| 2 | 4 |
    > > > >
    > > > > How do I count(show) unselected items?
    > > > > Or should I prepare each item fields?
    > > > > Both way, VBA can adjust for required style of the total table .
    > > > >
    > > > > any comment about "the basic of table design" or query(SQL)
    > > > > I'd appreciate it .
    > > > >
    > > > >
    > > > >
     

Share This Page