Table Design and transform sql

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 .
 
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;
 
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 said:
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 said:
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 .
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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 .
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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;


:

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 .
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Parameter Queries 1
Total 2
VB.NET Datasets 0
historic VAT Rate lookup 10
Force all dates to appear between begin and end dates 3
crosstab or pivottable or ? 1
Proof and Control 4
Query By Date and Count Records 3

Back
Top