Searching for partial match on a referenced table?

[rc51wv]

I have one large table called "tblProperty". It contains all the inventory
transactions for our contract. I have the locations for the property listed
as numbers. The numbers correspond to a second table called "tblLocation".

I need to be able to do a partial search in the main table for the location,
but unlike doing partial searches for other data that's contained in the main
table, I don't know how to setup the SQL statement to find a match in the
main table based on criteria in the 2nd.

This is on a subform. Would it just be easiest to create a query where the
two tables are linked and pull up the search results on the subform based on
the information in the query? Every other searchable field is already set up
with SQL statements using the "LIKE" qualifier.

-Thanks in advance.

 

[Piet Linden]

I have one large table called "tblProperty". It contains all the inventory
transactions for our contract. I have the locations for the property listed
as numbers. The numbers correspond to a second table called "tblLocation"..

I need to be able to do a partial search in the main table for the location,
but unlike doing partial searches for other data that's contained in the main
table, I don't know how to setup the SQL statement to find a match in the
main table based on criteria in the 2nd.

This is on a subform. Would it just be easiest to create a query where the
two tables are linked and pull up the search results on the subform basedon
the information in the query? Every other searchable field is already setup
with SQL statements using the "LIKE" qualifier.

-Thanks in advance.

Join your main table and the Locations table on LocationID (or
whatever the PK/FKs are called). Then you can do something like

SELECT...
FROM tblProperty INNER JOIN tblLocation ON tblProperty.LocationID =
tblLocation.LocationID
WHERE tblLocation.LocationName LIKE "*SomeLocation*"

 

[John W. Vinson]

I have one large table called "tblProperty". It contains all the inventory
transactions for our contract. I have the locations for the property listed
as numbers. The numbers correspond to a second table called "tblLocation".

HOW do you have them listed? Do you have an Access2007 "multivalue field", or
a string of numbers in a text field, or what?

I fear your tables may not be properly normalized! What tables do you have,
and how are they related? Is tblProperty in fact a table of Properties, or a
table of Transactions (I'm assuming that each property may be involved in
multiple transactions)?