Start Free Trial

asked on

cx Grid and cxLookupcombo

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?

in the editor you get the value from the lookupcombobox using editvalue or editingvalue
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').AsInteger := LookupCombo.EditValue;

the value you get in

qry.fields[0].AsFloat

ASKER

sorry,new to this...
since the lookupcombo is in the table it becomes part of the table
why does it not work:
procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
with adoquery1 do begin
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Text:='insert into table2 (a1) value SELECT SUM(PLUS) FROM TABLE1 WHERE ACC = :ACC ';
adoquery1.Parameters.ParamByName('ACC').Value := Adotable1acc.Value;
adoquery1.ExecSQL;
end;
end;

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

using a speedbutton will not change the focus
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

ASKER

Problem I have is this:
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.ParamByName('k1').Value := cxDBEdit1;
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?

ASKER

sorry I think that should go : SELECT (SUM(plus),SUM(minus) FROM TABLE1 WHERE ...

ASKER

I have tried :
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...

this is the insert statement
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

ASKER

This last bit I do not understand:
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,a8) ??
common would be REFERENCE_NO
Parameters would be like : where acc=10,where acc =23,where acc = 29
Too complicated for me...

ASKER

What I want to do is this:
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)

INSERT INTO TABLE2 (a1, a2, a3, a4, a5)
SELECT SUM(PLUS), SUM(MINUS), acc, value4, value5
FROM TABLE1
WHERE acc in (:acc1, :acc2, :acc3, :acc4)
GROUP BY acc, value4, value5

ASKER

If I understand this correctly:
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

ASKER

SELECT SUM(PLUS), SUM(MINUS), acc, value4, value5 ???
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...

you really need to get some basic delphi understandings ...
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

just beginner ...and your code does not help (FROM)

ASKER

I tried first with one record only :
insert into table2 (a4) select CASE WHEN acc = 10 THEN SUM(plus) FROM table1 WHERE REFERENCE_NO = :z1
and it does not work.
????

do you have any database knowledge at all ?

here, you only need to read this:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm

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/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2065648
aggregate sum: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions120a.htm#86759
aggregate expressions: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions2a.htm#81312
case expression: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions5a.htm#1033394

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

ASKER

I came here to get some answers not to learn books.
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.

you need to have a fundamental understanding of some basic things
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').asinteger := 1;
parambyname('acc').asinteger := 10;

the code would look like this:

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;

Open in new window

you read these posts, then why not read the online books ?
just the same !

ASKER

that basic I do understand..and if i am not mistaken in previous posts i said i have it working with one parameter.
I am having problems with multi parameters (for the acc) since acc can be various values.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I will try this later but can you tell me what is wrong with this :
select SUM(CASE WHEN acc = 10 THEN PLUS ELSE 0 END)
why is this not working?

this works on oracle but maybe not on access

ASKER

this is basic sql and should work in access
this last part is mysterious
THEN PLUS ELSE 0 END)
what am I summing here? Field PLUS???

the expression_value can be any column or expression returning a value

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)

you must also do FROM TABLE

ASKER

did some searching.it seems "in ms access, you have the IF(<contion>, <value if true>, <value if false>) function to implement the CASE, however, as you can see, with only 1 condition.
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 ?

you don't need to with my sample
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

ASKER

it is still the same question...
I tried : insert into table2 (a4) select IF(ACC=10) then sum(plus) from table1...
wint work

off course not, you are mixing 2 syntaxis !

insert into table2 (a4) select SUM(IF(ACC=10,PLUS, 0)) from table1...

ASKER

undefined function (if) error
this is beginnning to get on my nerves...

yeah, i had that too,
then i stopped using access ...

ASKER

I will have to post this into access circle and see if they have an idea.thank you both for your time,especially Geert.I want to do this in sql since I am learning basics so I better stick to my level of knowledge.