[HELP] Covert Col to Row & Del Row where x is empty

[harky]

After key in the Code, it become like this

BS_Code	ContractID	Install BS Per Day	LayoutID	Qty
1111	2019	100	228WP	1
1111	2019	100	705WP

I want to delete the row if [qty] col is empty.


Result should be this

BS_Code	ContractID	Install BS Per Day	LayoutID	Qty
1111	2019	100	228WP	1

SELECT TbInvoice2.BS_Code, TbInvoice2.ContractID, TbInvoice2.[Install BS Per Day], '228WP' AS LayoutID, [228WP] AS Qty
FROM TbInvoice2 where null is null;
union all
SELECT TbInvoice2.BS_Code, TbInvoice2.ContractID, TbInvoice2.[Install BS Per Day], '705WP' AS LayoutID, [705WP] AS Qty
FROM TbInvoice2 where [705WP] is null;

 

[harky]

Just to say this is for Query

 

[welshgasman]

Your data is not structured correcly, so this is why you have this problem.?
Are there more xxSWP fields.?

You could just run a delete query for each xxSWP that is Null?

 

[harky]

actually i want to transpose from query (Covert Col to Row）from other table.
This is already a query, i just want to query with 2 statement?

Your data is not structured correcly, so this is why you have this problem.?
Are there more xxSWP fields.?

You could just run a delete query for each xxSWP that is Null?

Your data is not structured correcly, so this is why you have this problem.?
Are there more xxSWP fields.?

You could just run a delete query for each xxSWP that is Null?

 

[welshgasman]

So wjy not just bring in records that are not Null for xxSWP?

 

[harky]

i dont know how...........


i try where [Qty] is not null
but they ask me to fill in number...

So wjy not just bring in records that are not Null for xxSWP?

 

[welshgasman]

Well it is not qty is it?, it is 70SWP ?

The same applies to any other xxSWP field?

 

[harky]

it base on qty col.
as long [QTY] is empty, del the row or dont show up

Well it is not qty is it?, it is 70SWP ?

The same applies to any other xxSWP field?

 

[welshgasman]

I must confess, I an completely confused?

You have SQL as

SELECT TbInvoice2.BS_Code, TbInvoice2.ContractID, TbInvoice2.[Install BS Per Day], '705WP' AS LayoutID, [705WP] AS Qty
FROM TbInvoice2 where [705WP] is null;

which is always going to produce an empty quantity and then you want to delete it.?

 

[harky]

sorry i dont know SQL so tht was testing..

the fact is base on QTY.
If qty is empty, the whole row del, tht what u see on the result

SELECT TbInvoice2.BS_Code, TbInvoice2.ContractID, TbInvoice2.[Install BS Per Day], '705WP' AS LayoutID, [705WP] AS Qty
FROM TbInvoice2 where [QTY] is null;

I must confess, I an completely confused?

You have SQL as

SELECT TbInvoice2.BS_Code, TbInvoice2.ContractID, TbInvoice2.[Install BS Per Day], '705WP' AS LayoutID, [705WP] AS Qty
FROM TbInvoice2 where [705WP] is null;

which is always going to produce an empty quantity and then you want to delete it.?