Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

cx Grid and cxLookupcombo

Posted on 2009-02-20
36
Medium Priority
?
728 Views
Last Modified: 2013-11-23
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?

0
Comment
Question by:srbenavrbe
  • 17
  • 17
  • 2
36 Comments
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23691086
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;
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23691091
the value you get in

qry.fields[0].AsFloat
0
 

Author Comment

by:srbenavrbe
ID: 23698099
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;
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 22

Assisted Solution

by:senad
senad earned 600 total points
ID: 23698133
I think it would be better if you used  CASE statement (example) :
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.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23698771
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
0
 

Author Comment

by:srbenavrbe
ID: 23707692
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?
0
 

Author Comment

by:srbenavrbe
ID: 23707710
sorry I think that should go : SELECT (SUM(plus),SUM(minus) FROM TABLE1 WHERE ...  
0
 

Author Comment

by:srbenavrbe
ID: 23707980
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...
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23708886
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
0
 

Author Comment

by:srbenavrbe
ID: 23709030
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...
0
 

Author Comment

by:srbenavrbe
ID: 23709076
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)
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23709084
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
0
 

Author Comment

by:srbenavrbe
ID: 23709175
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


0
 

Author Comment

by:srbenavrbe
ID: 23709251
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...
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23715736
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
 

0
 
LVL 22

Expert Comment

by:senad
ID: 23718598
just beginner ...and your code does not help (FROM)
0
 

Author Comment

by:srbenavrbe
ID: 23730907
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.
????

0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23730932
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
0
 

Author Comment

by:srbenavrbe
ID: 23731040
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.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23731150
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;
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23731160
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

0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23731163
you read these posts, then why not read the online books ?
just the same !
0
 

Author Comment

by:srbenavrbe
ID: 23731214
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.
0
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 1400 total points
ID: 23731243
for every different acc value you will have to run the query again

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;

Open in new window

0
 

Author Comment

by:srbenavrbe
ID: 23731300
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?

0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23731312
this works on oracle but maybe not on access
0
 

Author Comment

by:srbenavrbe
ID: 23731408
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???


0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23731419
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)
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23731426
you must also do FROM TABLE
0
 

Author Comment

by:srbenavrbe
ID: 23731486
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 ?
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23731508
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
0
 

Author Comment

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

Expert Comment

by:Geert Gruwez
ID: 23731618
off course not, you are mixing 2 syntaxis !

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

Author Comment

by:srbenavrbe
ID: 23732791
undefined function (if) error
this is beginnning to get on my nerves...
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 23733297
yeah, i had that too,
then i stopped using access ...
0
 

Author Comment

by:srbenavrbe
ID: 23741187
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Screencast - Getting to Know the Pipeline
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question