CODEWITHSUNDEEP
sqlms-access
Fernando Marana
Fernando Marana

Reputation: 21

Function to split value between columns in access

I have the following table:

Current

And I need to split the value column into two columns based on the value of the status column, also add a difference between the two. Like This:

Example

I was able to split it by using two separate queries, but when I merge them together I get duplicate values, even If I use the Sum and group by the costumer.

Upvotes: 1

Views: 56

Answers (3)

forpas
forpas

Reputation: 164099

With a LEFT self join:

select t.Customer,
  t.[value] as debt, tt.[value] as pay, t.[value] - Nz(tt.[value]) as diff
from tablename t left join tablename tt
on tt.customer = t.customer and t.[status] <> tt.[status]
where t.[status] = 'debt'

Results:

Customer    debt    pay   diff
Fernando    445     445   0
Marcelo     332     123   209
Adriana     889           889

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can sue conditional aggregation:

select customer, 
       sum(iif(status = 'debt', value, 0)) as debt,
       sum(iif(status = 'pay', value, 0)) as pay
       (sum(iif(status = 'debt', value, 0)) -
        sum(iif(status = 'pay', value, 0))
       ) as diff
from t
group by customer;

Upvotes: 2

zip
zip

Reputation: 4061

Try this please

    tb1: Customer, value, status

select d.Customer, d.value as debt, IIf(p.value Is Null, 0, p.value) as pay, d.value - IIf(p.value Is Null, 0, p.value)  as diff
from 
(select Customer, value from tb1 where status  = 'debt')d
left join
(select Customer, value from tb1 where status  = 'pay')p on d.Customer = p.Customer

Upvotes: 1

Related Questions