srbenavrbe
asked on
SQL INSERT CASE
* REF_NO * ACC * AMOUNT1 * AMOUNT2
************************** ********** ********** ********** **********
* 1 * 10 * 100,00 * *
* 1 * 20 * * 110,00 *
* 2 * 30 * 211,00 * *
* 3 * 40 * * 216,00 *
* 5 * 40 * * 216,00 *
* 5 * 50 * 250,00 * *
* 5 * 60 * * 310,00 *
************************** ********** ********** ********** ********
Now what I am trying to do is this:Sum the AMOUNT1 or AMOUNT2
depending on the ACC number and send these results
to another table (Table2):
INSERT INTO TABLE2 (a1,a2,a3,a4...)
select
case
sum(if ACC=10 THEN insert under a1
sum (if ACC=20 THEN insert under a2
and so on...
How do you do it in access?
I was doing it in Delphi:
SUM(CASE WHEN ACC = 10 THEN AMOUNT1 ELSE 0 END)
....
AND REF_NO = k:1;
adoquery1.Parameters.Param ByName('k1 ').Value := cxDBEdit1;
and it will not work.
How is the queery supposed to be in access??
I am just a beginner so please bear with me.
**************************
* 1 * 10 * 100,00 * *
* 1 * 20 * * 110,00 *
* 2 * 30 * 211,00 * *
* 3 * 40 * * 216,00 *
* 5 * 40 * * 216,00 *
* 5 * 50 * 250,00 * *
* 5 * 60 * * 310,00 *
**************************
Now what I am trying to do is this:Sum the AMOUNT1 or AMOUNT2
depending on the ACC number and send these results
to another table (Table2):
INSERT INTO TABLE2 (a1,a2,a3,a4...)
select
case
sum(if ACC=10 THEN insert under a1
sum (if ACC=20 THEN insert under a2
and so on...
How do you do it in access?
I was doing it in Delphi:
SUM(CASE WHEN ACC = 10 THEN AMOUNT1 ELSE 0 END)
....
AND REF_NO = k:1;
adoquery1.Parameters.Param
and it will not work.
How is the queery supposed to be in access??
I am just a beginner so please bear with me.
If you are looking for query like that, you can update Table2 based on the query given.
ASKER
I have about 35 of them.
Does not 'as a1' create a field (a1) ? I allready have it!
I am not updating but inserting.
Amount columns are two (amounz1 and amount2.
Does not 'as a1' create a field (a1) ? I allready have it!
I am not updating but inserting.
Amount columns are two (amounz1 and amount2.
ASKER
I get syntax error on sum(iif(ACC = 10,Amount1,0)) as a1,
also I do not need to group.I think
also I do not need to group.I think
>> SUM(CASE WHEN ACC = 10 THEN AMOUNT1 ELSE 0 END)
can you tell me which amount you want to sum if the ACC is 20, 30 and so on? your requirement is not clear. better explain with some sample data.
can you tell me which amount you want to sum if the ACC is 20, 30 and so on? your requirement is not clear. better explain with some sample data.
ASKER
Example :
If ACC was 10 I want to sum amount1.
If ACC was 20 I want to sum amount2
and so on...
It all depends on the ACC number.
If ACC was 10 I want to sum amount1.
If ACC was 20 I want to sum amount2
and so on...
It all depends on the ACC number.
If ACC was 30 which amount you want to sum? similarly for 40 , 50 and 60?
ASKER
and when I sum them up I want to send them to Table2 in columns of my choice (example :a1,a2,a3...)
you didn't answer my qurestion in previous post.
I assume that you want insert sum of amounts when acc = 10 under a1,
sum of amounts when acc = 20 under a2
but what about a3, a4 , .... what values you want to insert into those columns?
I assume that you want insert sum of amounts when acc = 10 under a1,
sum of amounts when acc = 20 under a2
but what about a3, a4 , .... what values you want to insert into those columns?
ASKER
I will enlarge the queery later...that was just for demonstration purpose.
Since I have about 35 ACC that would mean (a1,a2 -> to 35)
Right now I cant seem to put even one value into my Table2. :-)
"sum of amounts2 when acc = 20 under a2"
"sum of amounts1 when acc = 10 under a1" like this
Since I have about 35 ACC that would mean (a1,a2 -> to 35)
Right now I cant seem to put even one value into my Table2. :-)
"sum of amounts2 when acc = 20 under a2"
"sum of amounts1 when acc = 10 under a1" like this
ASKER
By the way..they are all sum values.
This appears to be a really wierd way to structure your database tables.
Why have a different column for the total of each acc?
I suggest you read up on 3rd normal form and associated topics for how to do good database design.
Not sure if this applies to access, but typically to create a normalized table, you could use a view with Unions to eliminate the plethora of different columns for each account.
Select ref_no, acc, amount1 as amount where acc = 10
union
Select ref_no, acc, amount2 as amount where acc = 20
union
Select ref_no, acc, amount3 as amount where acc = 30
union
Select ref_no, acc, amount4 as amount where acc = 40
Now you have something that looks normalized and is much more useful to manipulate.
e.g. a single query
Select sum(amount) from view_name where acc = :Account
Allows you to get the total for any account, specified as a parameter
Why have a different column for the total of each acc?
I suggest you read up on 3rd normal form and associated topics for how to do good database design.
Not sure if this applies to access, but typically to create a normalized table, you could use a view with Unions to eliminate the plethora of different columns for each account.
Select ref_no, acc, amount1 as amount where acc = 10
union
Select ref_no, acc, amount2 as amount where acc = 20
union
Select ref_no, acc, amount3 as amount where acc = 30
union
Select ref_no, acc, amount4 as amount where acc = 40
Now you have something that looks normalized and is much more useful to manipulate.
e.g. a single query
Select sum(amount) from view_name where acc = :Account
Allows you to get the total for any account, specified as a parameter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check this query.
Open in new window