| Author |
Topic |
|
man889
Starting Member
25 Posts |
 Posted - 2011-04-16 : 22:03:17
|
SELECT Sum(JobItem.JobPrice) AS JobPrice_Type_1_Sum, JobItem.InvNo
FROM JobItem
WHERE (((JobItem.JobTypeId)=1))
GROUP BY JobItem.InvNo;
SELECT Sum(JobItem.JobPrice) AS JobPrice_Type_2_Sum, JobItem.InvNo
FROM JobItem
WHERE (((JobItem.JobTypeId)=2))
GROUP BY JobItem.InvNo;
SELECT Sum(JobItem.JobPrice) AS JobPrice_Type_3_Sum, JobItem.InvNo
FROM JobItem
WHERE (((JobItem.JobTypeId)=3))
GROUP BY JobItem.InvNo;
SELECT Sum(JobItem.JobPrice) AS JobPrice_Type_4_Sum, JobItem.InvNo
FROM JobItem
WHERE (((JobItem.JobTypeId)=4))
GROUP BY JobItem.InvNo; |
|
|
man889
Starting Member
25 Posts |
Posted - 2011-04-16 : 22:26:18
|
Can I combine it into one table by InvNo and
having the column of InvNo, JobPrice_Type_1_Sum, JobPrice_Type_2_Sum, JobPrice_Type_3_Sum, JobPrice_Type_4_Sum |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-04-18 : 05:10:02
|
Have a look at the CASE statement
select invno,
case when JobTypeId = 1 then JobPrice
else 0
end as job1_raw,
case when JobTypeId = 2 then JobPrice
else 0
end as job2_raw,
etc
from jobitem
wrap the results inside a "group by" clause. Have a go, experiment and revert here with any problems. |
|
|
|
man889
Starting Member
25 Posts |
Posted - 2011-04-18 : 05:40:20
|
Hi AndrewMurphy,
Thank for your reply.
I figure out what I am look for is transform pivot after spend several hours searching.
and now I can get the result from ms access
TRANSFORM Sum(JobItem.JobPrice) AS JobPriceOfSum
SELECT JobItem.InvNo, Sum(JobItem.JobPrice) AS [Total Of JobPrice]
FROM JobItem
GROUP BY JobItem.InvNo
PIVOT JobItem.JobTypeId;
Here is the table and sample data
JobId, JobItemNo, InvNo, JobTypeId, JobPrice
1, 1, 3401, 1, 50
2, 2, 3403, 1, 60
3, 3, 3401, 2, 560
4, 4, 3402, 3, 10
5, 5, 3401, 1, 170
6, 1, 3402, 4, 140
7, 2, 3402, 3, 140
8, 1, 3403, 1, 300
9, 1, 3404, 2, 300
10, 1, 3405, 1, 300
11, 1, 3406, 1, 340
12, 1, 3407, 1, 300
13, 1, 3408, 1, 300
InvNo, Total Of JobPrice, JobTypeId 1, JobTypeId 2, JobTypeId 3, JobTypeId4
3418, 1090, 1080, 0, 10, 0
3419, 300, 300, 0, 0, 0
3420, 350, 300, 0, 0, 50
3421, 736 , 736 , 0, 0, 0
3422, 300, 300, 0, 0, 0
3429, 3400, 1150, 2250, 0, 0
3430, 2200, 0, 0, 2200, 0
3431, 2200, 0, 2200, 0, 0
3432, 2500, 0, 2500, 0, 0
3433, 1950, 0, 1950, 0, 0
but Now I am facing another problem is actually I will run the sql in VB.NET, the problem is VB.NET not allow using Transform pivot.
How can I convert it into standard sql statement?
Thank |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2011-04-18 : 07:22:17
|
| I'm not familiar with vb.net, but the SQL I provided "is standard" and will point you in the right direction. Have a go and post your efforts for others to observe and fine-tune. |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2011-04-29 : 19:10:29
|
Are you using ADO.NET to connect with Access?
~ Shaun Merrill
Seattle area |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-29 : 19:27:38
|
SELECT JobItem.InvNo,
Sum(case when JobItem.JobTypeId)=1 then JobItem.JobPrice else 0 end) AS JobPrice_Type_1_Sum,
Sum(case when JobItem.JobTypeId)=2 then JobItem.JobPrice else 0 end) AS JobPrice_Type_2_Sum,
...
FROM JobItem
GROUP BY JobItem.InvNo;
I think in access it might be something like
SELECT JobItem.InvNo,
Sum(iif (JobItem.JobTypeId)=1 , JobItem.JobPrice , 0) ) AS JobPrice_Type_1_Sum,
Sum(iif ( JobItem.JobTypeId)=2, JobItem.JobPrice, 0) ) AS JobPrice_Type_2_Sum,
...
FROM JobItem
GROUP BY JobItem.InvNo;
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
How to combine these sql