Using a table as a query criteria

[Emma Hope]

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

 

[Frank]

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.

 

[Nikos Yannacopoulos]

Emma,

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

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

HTH,
Nikos

 

[Rick Brandt]

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

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)".