P
PF
Hi all, i have the following tables
In tansaction the first field is a link to product
prod=id,desc transac=id(a
link),type(1or2),themonth(1-12),qt
1,prod1 1,1,1, 10
2,prod2 1,2,2, 15
3,prod3 1,1,2, 20
4,prod4 2,1,2, 25
5,prod5 3,1,1, 30
6,prod6 3,2,1, 35
7,prod7 2,2,2, 40
I want to display the data for month 1
and 2 on a form with a sql select
I need five columns,
prod, qt for type1 and month1, qt for type 2 and mont 1, qt for type 1 and
month 2, qt for type 2 and month 2
Select id, iif(transac.type=1 and themont=1,qt,0),
iif(transac.type=2 and themonth=1,qt,0),
iif(transac.type=1 and themonth=2,qt,0),
iif(transac.type=2 and themonth=2,qt,0)
I want
1, 10, 0,20,15
2, 0 , 0, 25 ,40
3, 30,35,0,0
But i dont have this result i have all the quantity on different lines.
I try to avoid making a lot of column by putting in few fields and the
data in the rows with an identifier but
now i have trouble displaying it!!
regards,
pierre
In tansaction the first field is a link to product
prod=id,desc transac=id(a
link),type(1or2),themonth(1-12),qt
1,prod1 1,1,1, 10
2,prod2 1,2,2, 15
3,prod3 1,1,2, 20
4,prod4 2,1,2, 25
5,prod5 3,1,1, 30
6,prod6 3,2,1, 35
7,prod7 2,2,2, 40
I want to display the data for month 1
and 2 on a form with a sql select
I need five columns,
prod, qt for type1 and month1, qt for type 2 and mont 1, qt for type 1 and
month 2, qt for type 2 and month 2
Select id, iif(transac.type=1 and themont=1,qt,0),
iif(transac.type=2 and themonth=1,qt,0),
iif(transac.type=1 and themonth=2,qt,0),
iif(transac.type=2 and themonth=2,qt,0)
I want
1, 10, 0,20,15
2, 0 , 0, 25 ,40
3, 30,35,0,0
But i dont have this result i have all the quantity on different lines.
I try to avoid making a lot of column by putting in few fields and the
data in the rows with an identifier but
now i have trouble displaying it!!
regards,
pierre