ADOQuery Master/Detail

Question

Data:

Nate Pond - LakeMaps.Lake_Name

Brook trout - Species.Species_Name

Creek chub

Golden shiner

Black Pond

Brook trout

Brown bullhead

Common shiner

Lake Placid

Lake trout

Smallmouth bass

Yellow perch

MDB Database

ADoTable1 = LakeMaps MASTER

ADOTable2 = Species DETAIL

Relationship

LakeMaps Table
LakeMaps.Field[0] = Lake_ID: Autonumber --- ]
LakeMaps.Field[1] = Lake_Name: Text---      |
                                            |Relationship set in the access database
Species Table                               |
Species.Field[0] = Species_ID: numeric ---  ]    
Species.Field[1] = Species_Name: text

The Species Table is a Detail the LakeMaps is the Master

How can I setup a ADOQuery to filter the data to display all lakes that have Brook trout in a DBGrid?

Filtered Data:

Nate Pond

Brook trout

Creek chub

Golden shiner

Black Pond

Brook trout

Brown bullhead

Common shiner

Answer 1

You can set Filtered = true and then use OnFilterRecord event and check if detail dataset contains requested value (this can be done in loop or with Locate procedure of dataset)

This will probably be very slow on larger amount of data. In those situations I usually filter master records directly in SQL. Something like this:

SELECT * FROM LakeMaps 
WHERE Lake_ID in (SELECT Lake_ID 
         FROM Species INNER JOIN SpeciesLakesRelation 
             ON (Species.Species_ID = SpeciesLakesRelation.Species_Id) 
         WHERE SPECIES_NAME = 'Brook Trout')

This SQL returns records from Lakes that have 'Brook Trout'.

SpeciesLakesRelation is table that contains relation between LakeMaps and Species.

Answer 2

Any idea why this produces a syntax error exception?

procedure TFormMain.CheckBoxFilterBySpeciesClick( Sender: TObject );
begin
  if CheckBoxFilterBySpecies.Checked then
  begin
    ADOQuery1.Close;
    ADOQuery1.SQL.Add( 'SELECT * FROM LakeMaps WHERE Lake_ID in ' +
      '(SELECT Lake_ID FROM Species INNER JOIN LakeMaps ON ' +
      '(Species.Species_ID = LakeMaps.Lake_Id) ' +
      'WHERE SPECIES_NAME = ' + ComboBoxSpecies.Text + ')');
    ADOQuery1.Open;
    ADOQuery1.First;
    ADOQuery1.Active := True;
  end
  else
  begin
    ADOQuery1.Active := False;
  end;
end;

Answer 3

Problem with your query in is that text in query must be in apostrophes. If ComboBoxSpecies.Text has value Brook Trout, then SQL evaluates to:

SELECT * FROM LakeMaps WHERE Lake_ID in 
  (SELECT Lake_ID FROM Species INNER JOIN LakeMaps ON 
     (Species.Species_ID = LakeMaps.Lake_Id) 
   WHERE SPECIES_NAME = Brook Trout)

Note that Brook Trout is not in apostrophes, so you get syntax error from MsAccess.

Edit:
As Gerry noted in comment:

• apostrophes should be added using QuotedStr function, instead of double apostrophe.
• best solution is to use query parameter

Delphi code, using QuotedStr, should look like this:

ADOQuery1.SQL.Add( 'SELECT * FROM LakeMaps WHERE Lake_ID in ' +
  '(SELECT Lake_ID FROM Species INNER JOIN LakeMaps ON ' +
  '(Species.Species_ID = LakeMaps.Lake_Id) ' +
  'WHERE SPECIES_NAME = ' + QuotedStr(ComboBoxSpecies.Text) + ')');

Now, if ComboBoxSpecies.Text has value Brook Trout, then this string:

'WHERE SPECIES_NAME = ' + QuotedStr(ComboBoxSpecies.Text) + ')'

evaluates as:

WHERE SPECIES_NAME = 'Brook Trout')