Build an expression without table joins, i.e. use something other than &

[crobaseball]

I'm trying to build an expression which creates a SKU for for my items in inventory. The table relationships and expression look like this:



What I'm trying to do is create an expression where it only creates sku's with the unique ID's associated with [Items_Parent].[Vendor_Item_Code]. In other words, I want to limit the table joins unique to the identifiers which they are related to. But of course, it returns every combination of this with [Vendors].[Vendor_Code] and


[List_of_sources_for_items].[Descript_of_Source]. I think it has something to do with using "&" as the joiner, but I can see where I somehow need to incorporate the identifier relationships to list only the unique values.

e.g. - I have a vendor code BB23. It has the identifier "Vendor Attached" which is connected to a single vendor, BS. But when I build this expression, it returns each vendor code combination. So say I have 3 vendors, BS, RR, and CAP, I get 3 SKU's with BS, RR, and CAP. But I only want BS-BB23. How can I limit the expression to only return this value?

[June7]

The table [constraint fields] in query without JOIN results in a Cartesian relation of records.

Could have an IIf() that will return Null or some other value if field is not BS.

Or restrict the records - apply filter criteria.

[crobaseball]

The table [constraint fields] in query without JOIN results in a Cartesian relation of records.

Could have an IIf() that will return Null or some other value if field is not BS.

Or restrict the records - apply filter criteria.

I'm not sure what you mean by the first part. Can you flesh this out a bit more?

[June7]

Okay, take a query with only two tables and no JOIN clause. Every record from each table will associate with every record of other table. In your query, every record of [constraint fields] table will associate with every record generated by the other joined tables.

[crobaseball]

Actually, I discovered that my problem is that I'm using another query to create some fields, and needed to create a relationship from that query to one of the tables. Thanks for your help!