Searchform including related tabels.

  • Thread starter Thread starter Lars Pedersen
  • Start date Start date
L

Lars Pedersen

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.



/Lars
 
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.
 
Thanks its working.
There is just one problem left, if I include criteria from tblMaps I get
just one instance from tblShip even that there might exist more that one
related record in tblMaps, which is the way I want it. But if I leave out
criteria from tblMaps I get severel instances of the same record from
tblShip, as many instances as it has related data from tblMaps, but I only
want one.
How can this be avoided.

/Lars
 
Sounds like you are using the INNER JOIN.
You can avoid the problem by using a subquery.
In the example, the main query's FROM clause contained tblShip only.

Another alternative migbt be to limit the fields you want, and include the
DISTINCT predicate, e.g.:
SELECT DISTINCT tblShip.ShipID FROM tblShip
WHERE ...
 
eeh yes it is an inner join her it is.
My select:
strSQL = "SELECT distinctrow tblShip.Registreringsnr, tblShip.Shipname,
tblShip.Type, [tblMaps].Mapno " & _
"FROM tblShip INNER JOIN [tblMaps] ON tblShip.Registreringsnr =
[tblMaps].ForeignKey"

When I select a map from a combo this is added to the WHERE:
strWhere = strWhere & " ([tblMaps].Mapno= " & Me![cbxShowMaps] & ") AND"


It works fine when I select a map for criteria, but if I only select other
criteria, I get 2 instances of a record from tblShip if it has 2 records in
tblMaps.

I think the best solution is the one with subquery, because I do have one
more related table called tblFormernames where one record from tblShip might
have several records, which also must be included in the search.

/Lars.
 
Back
Top