Here's the situation, the database has two tables. Table1 has a list of Vendors uniquely identified by a 5 digit number. Table2 has a list of parts with two different columns referring to the Vendor table index. The first column is easy, it contains a 5 digit number for the primary supplier. The second column is not, it contains as many as three vendor numbers delineated by commas (i.e. Alt = (43215, 61262, 76431) referring to alternate suppliers.

The goal is that when searching for a part number (that I have working fine, it returns a table of distinct Primary/Alternate Vendor fields), I want to display all the Vendor records which match the Primary (easy and done) and the Alternates (not easy, not working). I've tried using the Like operator with every syntax I could devise and the best I have managed is to return the Vendors matching the first 5 digits in the Alt-Vendor field. Anything after a comma is ignored. Is there any easy way to test if Vendor-ID is a substring of Alt-Vendor?

Here's the basic code I need.. just with syntax that works:
SELECT Vendor.ID
FROM Vendors, PartQuery
WHERE PartQuery.PrimaryVendor = Vendor.Id or PartQuery.AltVendor Like *Vendor.Id* ;

It appears that what you have is a many-to-many relationship between your vendors and your products.

Would it be possible to change the structure of your database? I would suggest putting in a link table that holds the key of your product and the key of the supplier. So if you have suppliers 1, 2, 4, and 8 for product 2, the table would have...

2 1
2 2
2 4
2 8

This eliminates any restriction on the number of vendors, gets rid of the many to many relationship and should make it easier to pull up a list of all vendors for a product, or even, all products for a vendor.

HTH

Mike

Mike
EchoVue.com

Mike,

You're absolutely right, it would make my life infinitely easier on not just this but a half dozen other tasks involving the database if I could restructure it. I'm not sure who built it but they treated Access like a spreadsheet. Unfortunately they built up so many other tools around it that I was not given authorization to change the structure. If I'm unable to find a workaround to the problem I will return to them with the limited primary vendor functionality and let them choose whether they'd rather do without alternates or change the structure. If there is a way to go about doing this though, it would be useful, because this is one of maybe a dozen many-to-many relationships where information that should be its own table is just listed as comma-delineated text in a field.

I wish they'd called me when they were designing it instead of just when they wanted it to look pretty and be easier to reference the 220000 parts and 500 vendors..

I used to have that mentality, but then I saw the light. Let me think back on my less competent days, and see if I can remember how I got around that...

I'll let you know if I remember anything.

Mike

Mike
EchoVue.com

Actually, looking at your original question, try this syntax on the last line...

WHERE PartQuery.PrimaryVendor = Vendor.Id or PartQuery.AltVendor Like '%' & Vendor.Id & '%';

Mike
EchoVue.com

It did not work, I get the distinct impression from Access that it will not allow the second half of a Like statement to be a field.

Where are you putting this SQL Statement? Is it in the Query Builder of is it from VBA?

If it is in query builder, you may need to enclose the field in square brackets. I usually do most of my SQL in VBA, so had forgotten about those!

Mike

Mike
EchoVue.com

I'm doing everything in VBA.

OK, that makes it a little simpler.

I have had some trouble lately trying to reference fields when I don't use a recordset, so to help us track that, lets set

strVendorNum = Vendor.id 'or rs.Fields("id")

next do your regular SQL string, but for the last part do...

... OR PartQuery.AltVendor Like '%' & '" & strVendorNum & "' & '%'"

I think that should work. If not, set a debug point on the strVendorNum, and ensure that it is getting a valid value.

Mike

Mike
EchoVue.com

I'm trying to think.. would something like this be possible?

SELECT *
FROM Vendor, PartQuery AS PQ
WHERE Vendor.ID = PQ.PrimaryVendor OR InStr(PQ.AltVendors, Vendor.ID)

I know the syntax is awful but if there's a way to just call a VB function that tests the condition and decide based on that if it should return a record.. it'd be nice :)