Using a table as a query criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I hope someone can help here. I have a table called Table1 with several
fields including ClientReference, i need to select only records which have
the client reference equal to all those records in a second table called
Table2 (which has ReferenceClient). Obviously i can add the table and do a
join between them but this then makes the recordset 'not updateable' and i
need to update some records. I assume i can use either dlookup or In () to
look up the values as a criteria in the query but i cannot make either work.

Please can anyone help.
Thanks
Emma
 
Try something like this -

SELECT tblDrivers.intID, tblDrivers.bytDepot, tblDepots.bytID
FROM tblDrivers LEFT JOIN tblDepots ON tblDrivers.bytDepot = tblDepots.bytID
WHERE tblDepots.bytID Is Not Null;

With the cursor in the 'Show' field of the Query Grid (in Query Design mode)
right click, select 'Properties' and set 'Recordset Type' to 'Dynaset
(Inconsistent Updates)'

Good luck.
 
Emma,

What you need here is a subquery. Try something like:

SELECT * FROM Table1
WHERE ClientReference In (SELECT ReferenceClient FROM Table2)

HTH,
Nikos
 
Emma said:
Hi All,

I hope someone can help here. I have a table called Table1 with
several fields including ClientReference, i need to select only
records which have the client reference equal to all those records in
a second table called Table2 (which has ReferenceClient). Obviously i
can add the table and do a join between them but this then makes the
recordset 'not updateable' and i need to update some records. I
assume i can use either dlookup or In () to look up the values as a
criteria in the query but i cannot make either work.

Please can anyone help.
Thanks
Emma
Click to expand...

Joining to the other table for the sole purpose of filtering the output of the
primary table should not render the query non-updateable. You don't have
"Output All Fields" set to yes do you? Try setting the RecordSetType to
"Dynaset (Inconsistent Updates)".
 
Back
Top