Solved

afterscroll queery

Posted on 2007-03-20
6
235 Views
Last Modified: 2010-04-05
I have 3 tables.'Companies','Subcompanies' and 'Workers'

1st one :
CompanyID
CompanyName

second :
CompanyID
SubcompanyID
SubcompanyName

Third :
CompanyID
SubcompanyID
WorkerID
Workername


On afterscroll of the 1st table
with ABSQuery1 do
 begin
   Close;
   if ABSTable1.FieldByName('CompanyID').AsString <> '' then
   begin
   SQL.Text:= 'select * from Subcompanies where CompanyID = ' +
       ABSTable1.FieldByName('CompanyID').AsString;
        Open;
     end;
 end;

However now I need to have workers displayed.
I want displayed only those who's CompanyID and SubcompanyID match.
That means another queery on afterscroll of the first ABSQueery1.
How is the sql supposed to look like ??
0
Comment
Question by:Vrtnar
  • 4
6 Comments
 
LVL 21

Expert Comment

by:ziolko
ID: 18756133
select * from workers where CompanyID = value_selected_in1table AND SubcompanyID = value_selected_in2table

ziolko.
0
 
LVL 6

Expert Comment

by:bokist
ID: 18756191
Hi vrtnar !

Try this way:

 if ABSTable1.FieldByName('CompanyID').AsString <> '' then
   begin
   Sql.Clear;
   Sql.add('select c.companyId, companyName, s.subcompanyId, subcompanyName, workerId, Workername');  
   Sql.add('  from companies c,  subcompanies s, workers w');
   Sql.add('where c.companyId = :co');
   Sql.add('    and c.companyId = s.companyId');
   Sql.add('    and s.companyId = w.companyId');
   Sql.add('    and s.subcompanyId = w.subcompanyId');
   Parambyname('co').asstring := ABSTable1.FieldByName('CompanyID').AsString;
   Open;

Not tested, just from head!!

regards
  steve


   
0
 

Author Comment

by:Vrtnar
ID: 18756286
if ABSQuery1.FieldByName('SubcompanyID').AsString <> '' then
   begin
   SQL.Text:= 'select * from workers  where companyID = ABSTable1.FieldByName('companyID').AsString AND ABSQuery1.FieldByName('SubcompanyID').AsString  ;

I get :
[Error] Unit1.pas(191): Missing operator or semicolon
[Error] Unit1.pas(192): Expression expected but 'AND' found
[Error] Unit1.pas(192): Unterminated string
[Error] Unit1.pas(193): Missing operator or semicolon
[Error] Unit1.pas(317): Statement expected but 'PROCEDURE' found
[Fatal Error] Project1.dpr(8): Could not compile used unit 'Unit1.pas'
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 21

Accepted Solution

by:
ziolko earned 125 total points
ID: 18756332
SQL.Text:= 'select * from workers  where companyID = ' + ABSTable1.FieldByName('companyID').AsString + ' AND SubcompanyID = ' +ABSQuery1.FieldByName('SubcompanyID').AsString

ziolko.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 18756339
...or

SQL.Text := Format('select * from workers where CompanyId=%d and SubCompanyID=%d', [ABSTable1.FieldByName('companyID').AsInteger, ABSQuery1.FieldByName('SubcompanyID').AsInteger]);

ziolko.
0
 
LVL 21

Expert Comment

by:ziolko
ID: 18756376
btw. cursor position in ABSTable and ABSQuery doesn't correspond with index of record selected in DBGrid or any other component that you use to display data, go to Demos folder and look for Master/Detail sample

ziolko.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now