Query with Field 1 = "ABC" OR Field 2 = "ABC"

[dhstein]

I have a product table with 2 fields - Primary Vendor and Secondary Vendor.
I have a query that is giving information when Primary Vendor = "ABC Company"
I'd like to expand the query and show all records where Primary Vendor =
"ABC Company" OR Secondary Vendor = "ABC Company" I'm not sure how to
accomplish that. I would post the SQL here, but the query has a lot of
complexity, so I left it off for now. If it would be helpful to see it, I'll
post it. Thanks for any help on this.

 

[Allen Browne]

There's a couple of ways to achive this.

In the Criteria row under [Primary Vendor], enter:
"ABC Company"

Below that in query design is a row titled Or.
In that row under [Secondary Vendor], enter:
"ABC Company"

If you have criteria under other fields as well, you may need to repeat them
on the OR row also.

Another approach is to type into the Field row:
"ABC Company"
Then in the Criteria row under this, enter:
IN ([Primary Vendor], [Secondary Vendor])

Ultimately if one product can have multiple vendors, it would suggest a
related table. One vendor can also supply many products, so you would need 3
tables:
a) Product table, with ProductID primary key
b) Vendor table, with VendorID primary key
c) ProductVendor table, with fields:
- ProductID relates to the products table
- VendorID relates to the vendor table.
So if a product has 2 suppliers, it appears on 2 rows of this table. This
approach solves your immediate problem, as well as many others you will
encounter when you use repeating fields (such as Vendor1 and Vendor2.)

 

[Steve Schapel]

Dhstein,

In design view of the query, put "ABC Company" in the Criteria of the
Primary Vendor column, and "ABC Company" in *a different row* of the
Criteria in the Secondary Vendor column.

Having said that, the very existence of a Primary Vendor field and a
Secondary Vendor field is an indication of a design flaw in your database.
You really should have this data in two separate records in one field in a
related table. If it is possible to look at a re-design, I would recommend
it.

 

[John W. Vinson]

I have a product table with 2 fields - Primary Vendor and Secondary Vendor.
I have a query that is giving information when Primary Vendor = "ABC Company"
I'd like to expand the query and show all records where Primary Vendor =
"ABC Company" OR Secondary Vendor = "ABC Company" I'm not sure how to
accomplish that. I would post the SQL here, but the query has a lot of
complexity, so I left it off for now. If it would be helpful to see it, I'll
post it. Thanks for any help on this.

In the grid:

LIKE "ABC*"

under both vendor fields, on *different* lines of the query grid. If there are
other criteria they may need to be repeated on both lines; if you have
multiple OR criteria things can get messy in the grid.

In SQL:

([Primary Vendor] LIKE "ABC*" OR [Secondary Vendor] LIKE "ABC*")

 

[dhstein]

Thanks for all the responses. The query is part of an aggregate query, so
just to test the basic concept, I created a new query to just display records
with vendor 1 OR vendor 2 = "ABC" Actually the vendor field is a number
which is the primary key in the vendor table. So my test query has 1 column
which I coded as "Expr1: 1" in the Field row and "In
([ProductVendor1ID],[ProductVendor2ID])" in the criteria row. When I run it
I get a message box to Enter Parameter Value for tblProduct.1 Any ideas on
why that's happening ? Thanks.

There's a couple of ways to achive this.

In the Criteria row under [Primary Vendor], enter:
"ABC Company"

Below that in query design is a row titled Or.
In that row under [Secondary Vendor], enter:
"ABC Company"

If you have criteria under other fields as well, you may need to repeat them
on the OR row also.

Another approach is to type into the Field row:
"ABC Company"
Then in the Criteria row under this, enter:
IN ([Primary Vendor], [Secondary Vendor])

Ultimately if one product can have multiple vendors, it would suggest a
related table. One vendor can also supply many products, so you would need 3
tables:
a) Product table, with ProductID primary key
b) Vendor table, with VendorID primary key
c) ProductVendor table, with fields:
- ProductID relates to the products table
- VendorID relates to the vendor table.
So if a product has 2 suppliers, it appears on 2 rows of this table. This
approach solves your immediate problem, as well as many others you will
encounter when you use repeating fields (such as Vendor1 and Vendor2.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a product table with 2 fields - Primary Vendor and Secondary Vendor.
I have a query that is giving information when Primary Vendor = "ABC
Company"
I'd like to expand the query and show all records where Primary Vendor =
"ABC Company" OR Secondary Vendor = "ABC Company" I'm not sure how to
accomplish that. I would post the SQL here, but the query has a lot of
complexity, so I left it off for now. If it would be helpful to see it,
I'll
post it. Thanks for any help on this.

 

[dhstein]

I'm thinking about the need to redesign the table structure based on the
responses I've received. It seems that in order to maintain the concept of a
primary vendor, I would need a field in the ProductVendor Table which could
be a Boolean for Primary (yes or no). I suppose I could do that. But the
heart of the matter seems to me that this is not a typical many to many
relationship. It's almost as if the secondary vendor is a different field.
It happens to share some information with the primary vendor field (i.e. the
vendor table) and maybe that makes it a many to many relationship. But there
will never be 3 vendors for a product. I could perhaps duplicate the Vendor
table and have each vendor field point to it's own vendor table. I know I'm
rambling a bit here, but is this cut and dried? The only correct design is
to have the 3 tables? Thanks.

There's a couple of ways to achive this.

In the Criteria row under [Primary Vendor], enter:
"ABC Company"

Below that in query design is a row titled Or.
In that row under [Secondary Vendor], enter:
"ABC Company"

If you have criteria under other fields as well, you may need to repeat them
on the OR row also.

Another approach is to type into the Field row:
"ABC Company"
Then in the Criteria row under this, enter:
IN ([Primary Vendor], [Secondary Vendor])

Ultimately if one product can have multiple vendors, it would suggest a
related table. One vendor can also supply many products, so you would need 3
tables:
a) Product table, with ProductID primary key
b) Vendor table, with VendorID primary key
c) ProductVendor table, with fields:
- ProductID relates to the products table
- VendorID relates to the vendor table.
So if a product has 2 suppliers, it appears on 2 rows of this table. This
approach solves your immediate problem, as well as many others you will
encounter when you use repeating fields (such as Vendor1 and Vendor2.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a product table with 2 fields - Primary Vendor and Secondary Vendor.
I have a query that is giving information when Primary Vendor = "ABC
Company"
I'd like to expand the query and show all records where Primary Vendor =
"ABC Company" OR Secondary Vendor = "ABC Company" I'm not sure how to
accomplish that. I would post the SQL here, but the query has a lot of
complexity, so I left it off for now. If it would be helpful to see it,
I'll
post it. Thanks for any help on this.

 

[Allen Browne]

Yes: the 3 tables would be the correct design.

You could have a boolean, but my preference would be for a Number field in
the junction table, to indicate priority (ie. which is the #1 vendor, and
which is #2.) I find this a much more flexible design: it avoids the JET
problems with Yes/No fields on the outer side of a join; it allows for more
than 2 entries (if ever needed), and it allows for equals (if you don't care
which is the preferred vendor.)

You can avoid duplicates by using the combination of VendorID +ProductID as
primary key of the junction table.

There might be another field in this table also, for the vendor's order code
for this product.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm thinking about the need to redesign the table structure based on the
responses I've received. It seems that in order to maintain the concept
of a
primary vendor, I would need a field in the ProductVendor Table which
could
be a Boolean for Primary (yes or no). I suppose I could do that. But the
heart of the matter seems to me that this is not a typical many to many
relationship. It's almost as if the secondary vendor is a different
field.
It happens to share some information with the primary vendor field (i.e.
the
vendor table) and maybe that makes it a many to many relationship. But
there
will never be 3 vendors for a product. I could perhaps duplicate the
Vendor
table and have each vendor field point to it's own vendor table. I know
I'm
rambling a bit here, but is this cut and dried? The only correct design
is
to have the 3 tables? Thanks.

There's a couple of ways to achive this.

In the Criteria row under [Primary Vendor], enter:
"ABC Company"

Below that in query design is a row titled Or.
In that row under [Secondary Vendor], enter:
"ABC Company"

If you have criteria under other fields as well, you may need to repeat
them
on the OR row also.

Another approach is to type into the Field row:
"ABC Company"
Then in the Criteria row under this, enter:
IN ([Primary Vendor], [Secondary Vendor])

Ultimately if one product can have multiple vendors, it would suggest a
related table. One vendor can also supply many products, so you would
need
3 tables:
a) Product table, with ProductID primary key
b) Vendor table, with VendorID primary key
c) ProductVendor table, with fields:
- ProductID relates to the products table
- VendorID relates to the vendor table.
So if a product has 2 suppliers, it appears on 2 rows of this table. This
approach solves your immediate problem, as well as many others you will
encounter when you use repeating fields (such as Vendor1 and Vendor2.)

I have a product table with 2 fields - Primary Vendor and Secondary
Vendor.
I have a query that is giving information when Primary Vendor = "ABC
Company"
I'd like to expand the query and show all records where Primary Vendor
=
"ABC Company" OR Secondary Vendor = "ABC Company" I'm not sure
how to accomplish that. I would post the SQL here, but the query has
a lot
of complexity, so I left it off for now. If it would be helpful to see
it,
I'll post it. Thanks for any help on this.