Creating New Table & Linking to Datas

Hi Everybody,
I have a table as "AdjSummary" with 5 columns. I have to create a
table with 2 columns from the above table. The structure of the table
"AdjSummary" is as below
INVNO ADJUST MODE PAYMENT MODE
10 100 OTH 0 NULL
11 150 OTH 0 NULL
11 100 DET 0 NULL
12 0 NULL 50 CSH
12 50 DET 50 VIS
12 12 DET 0 NULL
My new table should be with 2 columns (INVNO & ADJUST) as below.
INVNO ADJUST
10 100
11 250
12 62
Moreover, I want ADJUST column should be not equal to zero.
Pls help me in creating the table & to link the data to new table.
Thanks for your help in advance.
Regards,
Selvarathinam.Selvarathinam wrote:
> Hi Everybody,
> I have a table as "AdjSummary" with 5 columns. I have to create a
> table with 2 columns from the above table. The structure of the table
> "AdjSummary" is as below
> INVNO ADJUST MODE PAYMENT MODE
> 10 100 OTH 0 NULL
> 11 150 OTH 0 NULL
> 11 100 DET 0 NULL
> 12 0 NULL 50 CSH
> 12 50 DET 50 VIS
> 12 12 DET 0 NULL
> My new table should be with 2 columns (INVNO & ADJUST) as below.
> INVNO ADJUST
> 10 100
> 11 250
> 12 62
> Moreover, I want ADJUST column should be not equal to zero.
> Pls help me in creating the table & to link the data to new table.
> Thanks for your help in advance.
> Regards,
> Selvarathinam.
SELECT INVNO, ADJUST
--INTO MyTable
FROM AdjSummary
WHERE ADJUST <> 0
This will return the records to you in Query Analyzer. If you want to
keep a permament copy of the records, uncomming the INTO line above,
and change "MyTable" to a legitimate table name.|||Tracy McKibben wrote:
> Selvarathinam wrote:
> SELECT INVNO, ADJUST
> --INTO MyTable
> FROM AdjSummary
> WHERE ADJUST <> 0
> This will return the records to you in Query Analyzer. If you want to
> keep a permament copy of the records, uncomming the INTO line above,
> and change "MyTable" to a legitimate table name.
Sorry, make that:
SELECT INVNO, SUM(ADJUST)
--INTO MyTable
FROM AdjSummary
GROUP BY INVNO
HAVING SUM(ADJUST) <> 0