this depends on the immediatepost setting of the datacontroller or the lookupcombo

you would have to add a query to get the sum

SELECT SUM(PLUS) FROM TABLE WHERE ACC = :ACC

ParamByName('ACC').AsInteg

Solved

Posted on 2009-02-20

In my cxgrid I have a field defined as cxLookupcombo (gets data from another table).

Table looks something like this (4 fields) :

WTF ACC PLUS MINUS

One 10 11,55

Two 16 20,00

Seven 10 22,16

53,71

etc....

The field ACC is the cxLookupcombo.

Now I want to send this data to another table.Example:

If the cxLookupcombo value is 10 then I would sum all the values in the table where cxLookupcombo displays 10 (in the example above it would be 33,71) and insert it into another table.I would like to insert the footer value under the field PLUS also.Database is access (I am using ado)

How is the queery supposed to look like?

Table looks something like this (4 fields) :

WTF ACC PLUS MINUS

One 10 11,55

Two 16 20,00

Seven 10 22,16

53,71

etc....

The field ACC is the cxLookupcombo.

Now I want to send this data to another table.Example:

If the cxLookupcombo value is 10 then I would sum all the values in the table where cxLookupcombo displays 10 (in the example above it would be 33,71) and insert it into another table.I would like to insert the footer value under the field PLUS also.Database is access (I am using ado)

How is the queery supposed to look like?

36 Comments

this depends on the immediatepost setting of the datacontroller or the lookupcombo

you would have to add a query to get the sum

SELECT SUM(PLUS) FROM TABLE WHERE ACC = :ACC

ParamByName('ACC').AsInteg

since the lookupcombo is in the table it becomes part of the table

why does it not work:

procedure TForm1.SpeedButton1Click(S

begin

with adoquery1 do begin

adoquery1.Close;

adoquery1.SQL.Clear;

adoquery1.SQL.Text:='inser

adoquery1.Parameters.Param

adoquery1.ExecSQL;

end;

end;

case adotable1acc.Value of

10: showmessage('10');

end;

20:

...

means you will have to write a queery for each value.Also you will have to iterate through your table1 like :while not table1.eof - next ...etc.

the devexpress grid doesn't post changes unless the focus is changed

there is some possibilites, but the easiest one is to not use TSpeedButton,

use Standard TButton or TBitBtn which changes the focus

there is also the way of checking if in edit mode and if so, accepting the edit changes and post them first,

but this is awkward with a lot of buttons

I am a beginner on this so help..

First I have to send this data to another table for future reference.I did that.

Now I must get this data to another table (example Table2).

INSERT INTO TABLE2 (a1,a2,a3) VALUES SELECT ACC,PLUS,MINUS FROM TABLE1

(SUM(plus),SUM(minus) WHERE ACC = (10,20) AND REFERENCE_NO = k:1;

adoquery1.Parameters.Param

I think a queery should go something like that.Since I am a beginner I do not understand fully the where clause.Will the SUM(plus) apply to ACC 10 or no.?Please instruct me.Am I on the right track?

INSERT INTO TABLE2 (a1,a2) VALUES SELECT SUM(plus),SUM(minus) FROM TABLE1 WHERE

(REFERENCE_NO = :z1 and ACC= 10)

it works.

However I am having problem with the last part.What if value of ACC was 25 and I need it to be inserted under a3 value???Dont know how to add other values...

deleted word "values"

and added group by clause

INSERT INTO TABLE2 (a1,a2,a3)

SELECT SUM(plus),SUM(minus),acc

FROM TABLE1

WHERE (REFERENCE_NO = :z1 and ACC= :paramacc)

group by acc

Now in my table I have multiple ACC values (example: 10,20,23,25,29)

In your code I can only set one parameter.

Basically I am lost between table2 fileds and parameters.

What if I had to insert more data like INSERT INTO TABLE2 (a1,a2,a3,a4,a5,a6,a7,a7,a

common would be REFERENCE_NO

Parameters would be like : where acc=10,where acc =23,where acc = 29

Too complicated for me...

Grab the relevant data from table1 using REFERENCE_NO.

Now data returned can have this :

WTF ACC PLUS MINUS

One 10 11,55

Two 16 20,00

Seven 10 22,16

Nine 22 32,00

Eleven 16 20,00

Now I need to sum up all data ...ACC like this :

10 33,71 and send it to Table2 (a3)

16 40,00 and send it to Table2 (a9)

22 32,00 and send it to Table2 (a6)

SELECT SUM(PLUS), SUM(MINUS), acc, value4, value5

FROM TABLE1

WHERE acc in (:acc1, :acc2, :acc3, :acc4)

GROUP BY acc, value4, value5

INSERT INTO TABLE2 (a1, a1, a3, a4, a5)

SELECT SUM(PLUS), SUM(MINUS), ACC FROM TABLE1

WHERE REFERENCE_NO = :z1 AND ACC IN (:10, :23, :26, :29,:30)

so...the insert would accept this ?

a1 = 10

a2 = 23

I am not selecting any more values from table1 than those 3

(plus , minus ,acc)

If I use SELECT SUM(PLUS) it sums all the plus values found under common REFERENCE_NO .

(which I need also but..)

I must do for each acc : SELECT SUM(PLUS) WHERE ACC '10' and insert it into table2 in field a4.

SELECT SUM(PLUS) WHERE ACC '20' and insert it into table2 in field a9.

Table2 just ccepts these various values under various columns...

and database for that matter too !

you're design is needlessly complex, but that's a other matter

do you want sum(plus) + sum(minus) in field a4 ?

INSERT INTO TABLE (A4, A9, A4??, A9??, ACC)

SELECT

SUM(CASE WHEN ACC = :ACCA4 THEN PLUS ELSE 0 END) A4PLUS,

SUM(CASE WHEN ACC = :ACCA9 THEN PLUS ELSE 0 END) A9PLUS,

SUM(CASE WHEN ACC = :ACCM4 THEN MINUS ELSE 0 END) A4MINUS,

SUM(CASE WHEN ACC = :ACCM9 THEN MINUS ELSE 0 END) A9MINUS,

ACC

WHERE REFERENCE_NO = :z1

GROUP BY ACC

insert into table2 (a4) select CASE WHEN acc = 10 THEN SUM(plus) FROM table1 WHERE REFERENCE_NO = :z1

and it does not work.

????

here, you only need to read this:

http://download.oracle.com

it's the sql reference for oracle 9i

how to write queries

you seem to have absolutely no clue as how to write a select statement

what you need is these references:

select : http://download.oracle.com

aggregate sum: http://download.oracle.com

aggregate expressions: http://download.oracle.com

case expression: http://download.oracle.com

after you have read all that and tried the samples you should no problem at all !!!

most of it is valid for access too, but i think it is better explained in oracle

I have searched and tried to get this to work but I am having problems:

I also tried your way :

select SUM(CASE WHEN ACC= 10 THEN PLUS ELSE 0 END)

and it wont work.

to understand what we give you ...

the most simple insert for you would be:

insert into table2 (a4)

select SUM(plus) FROM table1

WHERE REFERENCE_NO = :z1

and acc = :acc

parambyname('z1').asintege

parambyname('acc').asinteg

```
query.SQL.Text :=
'insert into table2 (a4) '+
'select SUM(plus) FROM table1 '+
'WHERE REFERENCE_NO = :z1 '+
' and acc = :acc ';
query.parambyname('z1').asinteger := 1;
query.parambyname('acc').asinteger := 10;
query.ExecSQL;
```

I am having problems with multi parameters (for the acc) since acc can be various values.

here is a sample, for different acc values and different columns

```
type
RAcc = record
acc: integer;
col: string;
end;
procedure TForm1.SaveAcc(Z1: integer);
const
maxacc = 3;
acc: array[1..maxAcc] of RAcc = ((acc:10; col:'a4'), (acc:20; col: 'a5'), (acc: 30; col: 'a6'));
var I: Integer;
begin
for I := 1 to maxAcc do
begin
query.SQL.Text := Format(
'insert into table2 (%s) '+
'select SUM(plus) FROM table1 '+
'WHERE REFERENCE_NO = :z1 '+
' and acc = :acc ', [acc[I].Col]);
query.parambyname('z1').asinteger := Z1;
query.parambyname('acc').asinteger := acc[I].Acc;
query.ExecSQL;
end;
end;
```

select SUM(CASE WHEN acc = 10 THEN PLUS ELSE 0 END)

why is this not working?

this last part is mysterious

THEN PLUS ELSE 0 END)

what am I summing here? Field PLUS???

CASE

WHEN expression1 = true THEN expression_value1

WHEN expression2 = true THEN expression_value2

ELSE

expression_value_else

END

this only sums the field PLUS when ACC = 10 otherwise it's adds zero (0)

so, if you have multiple WHENs in your CASE of sql server, you need multiple IFs in you ms acces query".

so how should I write it ?

but you would probably nest them

IF (ACC=10, PLUS, IF(ACC=20, PLUS, 0))

remember to stick to your original question.

for different questions open an other one

I tried : insert into table2 (a4) select IF(ACC=10) then sum(plus) from table1...

wint work

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Can't connect to Outlook via TOutlookApplication component | 15 | 88 | |

Need Delphi function to get Youtube video title | 5 | 150 | |

Using idhttp to login to instagram | 2 | 49 | |

How to debug For loops? | 3 | 20 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**22** Experts available now in Live!