Re: Searchform including related tabels.

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 03/27/05 

Date: Sun, 27 Mar 2005 18:06:04 +0800

If you wish to build a SQL statement that searches for matches based on data
in a related table, a subquery in the WHERE clause might be the simplest
approach.

THe detail will depend on your structure, but typically you end up with
something like this:
    SELECT tblShip.* FROM tblShip
    WHERE EXISTS
        ( SELECT tblMaps.MapID FROM tblMaps
        WHERE tblMaps.ShipID = tblShip.ShipID );

If subqueries are new, see Microsoft's introduction:
    How to Create and Use Subqueries
at:
    http://support.microsoft.com/?id=209066

If it is just one related table you need to search, a simple INNER JOIN
would do the trick, but the subqueries are useful where you want to give the
user the freedom to select records from multiple related tables with various
other criteria applied. 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Lars Pedersen" <no> wrote in message
news:eHW6p%23qMFHA.3228@TK2MSFTNGP12.phx.gbl...
> Hi there.
> I have a searchform that builds a query (strSQL) and passes it to a 
> listbox on the searchform, but how do i include search in related tabels 
> and also show those data in the listbox.
> The mainform is based on tblShip, which has related data in tblMaps.
> So far my search only looks in tblShip.
>
> The searchform is based on the one found on 
> http://jfconsult.home.comcast.net/tips/ which I can recomend.

Relevant Pages

  • Re: Searchform including related tabels.
    ... the main query's FROM clause contained tblShip only. ... > related record in tblMaps, which is the way I want it. ... >> How to Create and Use Subqueries ... >>> The mainform is based on tblShip, which has related data in tblMaps. ...
    (microsoft.public.access.queries)
  • Re: Searchform including related tabels.
    ... There is just one problem left, if I include criteria from tblMaps I get ... tblShip, as many instances as it has related data from tblMaps, but I only ... > How to Create and Use Subqueries ...
    (microsoft.public.access.queries)