Access SQL Join Query Help

[cool_dude_2000]

Hi

I am attempting to join two Access tables together using a combination of wildcards and exclusions.

Table1 is a data table which should pick up parameters from Table2. Table2 includes wildcards, where if the joined field is blank, it should match against every record in the joined field. This works ok, but the trouble is that if there is an exclusion, then that record is duplicated - once in the wildcard join and also in the individual record join.

It's easier if I demonstrate by way of example:

Table1 (data table):

field1 field2
12345 A
12345 B
12345 C
12345 D
12345 E

Table2 (parameter table):

field1 field2 cost
12345 10.00
12345 A 99.00

So the expected result should be that the blank value in field2 in Table2 should be treated as a wildcard and join against all field2 records in Table1, with the exception of record A which picks up its separate cost.

I am expecting this output:

field1 field2 cost
12345 A 99.00
12345 B 10.00
12345 C 10.00
12345 D 10.00
12345 E 10.00

The problem is I am getting two A field2 records, one for 99.00 and 10.00 as the wildcard is duplicating this also:

field1 field2 cost
12345 A 99.00
12345 A 10.00
12345 B 10.00
12345 C 10.00
12345 D 10.00
12345 E 10.00

My query is as follows:

SELECT table1.field1, table1.field2, table2.cost 
FROM table2 
RIGHT JOIN table1
ON table1.field1=table2.field1
AND table1.field2 LIKE iif(table2.field2 is null,'*',table2.field2)

Please help as I am going out of my wits end trying to resolve this through a simply query!

Many thanks.

 

[plog]

You can't use wildcards in a JOIN like that, nor can you JOIN on a NULL field. So to accomplish what you want, make a sub-query with the default value for each field1:

SELECT Table2.field1, Table2.cost
FROM Table2
WHERE (((Table2.field2) Is Null));

Name that 'sub1'. This will determine the value for any unmatched records from Table1 to Table2. Then to generate the data you want use this SQL:

SELECT Table1.field1, Table1.field2, IIf(IsNull([Table2].[cost]),[sub1].[cost],[Table2].[cost]) AS ActualCost
FROM (Table1 LEFT JOIN Table2 ON (Table1.field2 = Table2.field2) AND (Table1.field1 = Table2.field1)) INNER JOIN sub1 ON Table1.field1 = sub1.field1;

It's up to you to make sure there is one and only 1 Null field2 value for every field1 value in Table2. Otherwise you will get duplicate/no results for some field1 values.