query help

[rjw24]

My main table is called tblMain and this has an abundance of information in
there. Each record has an ID called MainID. I need to do a 'search type
query' on 4 fields . I have a primary business unit which each record must
fill in (PrimaryBU). I then have another 3 related business unit fields
(RelatedBU1, RelatedBU2, RelatedBU3). All 4 of these fields are linked to a
lookup table tblBusinessUnit.

I need to write a query that will prompt the user to type a business unit (I
know how to do this part) and will then show any record which has that
business unit in any of those 4 fields.

Can you help please?

For example; the user will run the query and a prompt for the user to type a
business unit will open. If the user types retail then any record which has
retail contained in

 

[Jeanette Cunningham]

Hi,
something like the following:
SELECT tblMain.MainID, tblMain.PrimaryBU
FROM tblMain INNER JOIN tblBusinessUnit
ON tblMain.MainID = tblBusinessUnit.MainID
WHERE tblMain.PrimaryBU = 'retail' OR
tblBusinessUnit.RelatedBU1 = 'retail' OR
tblBusinessUnit.RelatedBU2 = 'retail' OR
tblBusinessUnit.RelatedBU3 = 'retail' ;


Jeanette Cunningham