Question On Join only if Left table Items Is Not Null

[Xander]

I’m trying to make a query that joins two tables based on attributes set by
the users.
They have an input table with 25 attributes which they can either fill out
or leave blank.

If for instance fill out 2 attributes, it should find those attributes in
the other table and make a join on all matches and additionally it should
then ignore all the blanks in the join statement, as they don’t match.

I put this in a small example:

Parts Attr_1 Attr_2 Attr_3 Attr_ etc
Part 1 QW RT ..
Part 2 DF ..
Part 3 AS DF ER ..
Example User table


Attr_1 Attr_2 Attr_3 Attr_ etc Volume
TY QW RT .. 122
DF AE RT .. 211
AS DF ER .. 222
DF DF ER .. 433
Example Data to join

In the above example for instance for Part 2 it should join both the 2nd and
the 4th row; so that the matching volumes can be linked to the part.

I’ve tried doing this by making the join on all the criteria as an OR
statement and then add a WHERE stating either is the above join or left table
attribute Is Null. However this doesn’t work .
I’ve also tried using an IIF statement after the FROM statement, but this
doesn’t seem to be allowed.

The only possible way I could think of is to do a join for every possible
combination and then UNION them all together.
However with 25 attributes every possible combination is a list which is way
too program, so I’m hoping someone knows a solution that can help me out.

 

[Jerry Whittle]

The solution is to start over and redesign your tables. Any time that you
have data going across, such as Attr_1 though Attr_25, you are making a
mistake. You need to think 'down' and not 'across' with your data.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[Xander]

Jerry, thanks for you help.

The problem with the data is that I didn't create the table it needs to join
to. We buy this data from a 3rd party and they come in huge excel files with
all the attributes across as well.

Therefore I was trying to come up with a solution/work around to still join
this data.

 

[Jerry Whittle]

Then your idea of the Union queries is probably the best. Not ideal, but
given the constraints, it's about all there is.

 

[Sylvain Lafontaine]

Usually, the best way of solving this kind of problem is simply to build a
string containing all the attributes and Join on this using the LIKE (or the
Instr() function ?) operator. However, I'm more familiar at this moment
with the syntaxe of SQL-Server; so I'm not sure how to write this with
Access/JET.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)