Query Match 2 Fields to 1

[CT Witter]

I have 2 tables in a query, Table1 has data, Table2 has a match field for lookup.

In table1 I would like to match field1 and field2 to table2 field1 and return field test in 2 different fields.

Here is table1

ID Field1 Field2
1 KBFI KJAC
2 KPFN KJAX


Here is table2

ID Field1 Test
2 KPBI Southern
76 KJAC Northwest Mountain
99 KPFN Southern
39 KJAX Southern


What I want as a result looks like:
ID Field1 Field2 Field3 Field4
1 KBFI KJAC Southern Northwest Mountain
2 KPFN KJAX Southern Southern


How do I accomplish this?

Thanks,
CT

 

[Andrew Fergus]

Hi,

Your example doesn't quite work - you are showing item 1 in table 1 as KBFI and KJAC. However, there is no KBFI in table 2 and for the purposes of my example, I have assumed you made a typo and I will ignore it (I actually changed KBFI in table 1 to KPBI to match the data in table 2). If this is not correct and I am misreading your example, please advise.

Anyway, try this :

Query 1 :

New Query, Design View, Add Tables 1 & 2, Close.
Leave the link between field 1 on table 1 and field 1 on table as is.
Field 1 = Field 1 from table 1
Field 2 = Field 2 from table 1
Field 3 = test from table 2
Save as Query 1 (or whatever you want to call it).

Query 2 :

New Query, Design View, Add Query 1 and Table 2, Close.
Select the line joining field 1 in query 1 with field 1 in table 2 by clicking on it once and delete it by pressing the delete key. Click and drag field 2 on query 1 onto field 1 on table 2. This will create the necessary join.

Field 1 = Field 1 from query 1
Field 2 = Field 2 from query 1
Field 3 = Field 3 (test) from query 1
Field 4 = test from table 2

Save and Run.

If this is not quite the right format then you can concatenate the 2 "test" fields to create a new one by replacing Field 3 and 4 in the 2nd query with something like this :

Field 3 = [Query1].[Test] & " " & [Table2].[Test]

HTH, Andrew.