Parameter Queries

[OV Brian]

Is there a way to have a user enter a parameter and have Access search for
that parameter in multiple fields? Example: A vendor information table has
the ability to store up to 3 products the vendor provides (Fields are Product
1, Product 2, Product 3). I want to have the user enter a product to search
for vendors who supply that. I would like the query to return all vendors
that have that product listed in field 1 or 2 or 3.

Thank you for any help

 

[Evi]

No, no no, you can't do this to that poor database. It sounds horribly
deformed.
you need:
1. TblVendor
VendID
Vendor details
stuff only to do about the vendor and nothing about the product

2.TblProduct
ProductID
Product
stuff only to do with product and nothing about the vendor

3.TblVendor Product
VPID
VendID (linked from TblVendor)
ProductID (Linked from TblProduct)
fields to do with that vendor and that product eg that vendor's price for
the product

Just think, you can have a Main form based on TblProduct with
TblVendorProduct as the subform so that you can see all the vendors who have
a particular product.
You could also have a Main form based on TblVendor with TblVendorProduct as
the subform so that you have a list of all his products.
And how easy it would be to filter the Product field in a query so that all
the vendors who have that product are displayed.

Evi

 

[John Spencer]

Yes, enter the exact same parameter under each of the three fields but on
different criteria lines so the search is for the value in Product 1 or in
Product 2 or in Product 3


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County