lookup info by row and field

[dokuin]

I am trying to make a Query that looks up info in a table based on the row and field info.
The Query gets info from Table A and Table B.
Table A has 2 fields of info that are used to lookup info from Table B.

Table A
C1 C2 (fields)
10 L1
10 L3
22 L2

Table B
N L1 L2 L3 (fields)
10 5 6 8
20 6 8 8
22 2 5 7

What I need to show in the query is the first 2 fields from Table A, and the third field derived from Table B:
Query C
C1 C2 TB
10 L1 5
10 L3 8
22 L2 5

I know how to do this if Table B has only 2 field (N and L1).
I can't figure out how to do a matrix lookup.

I get these tables as is. I can not change the format/layout the data comes in.
This info has to go into a Query as there are several steps after I get this info.
The above info is a simplified version of the actual tables, but this is the crux of the problem.

Thanks for any help.

 

[Jon K]

(I assume C1 and N are numeric fields, and the second 10 in C1 is a typo and is actually 20 as in N.)

Try this query (type/paste in the SQL View of a new query):-

SELECT [C1], [C2], DLookup([Table A].[C2],"Table B","[N]=" & [Table A].[C1]) AS TB
FROM [Table A];


If C1 and N are text fields, C1 need to be enclosed by single quotes in the DLookup() function as follows:-

,"[N]='" & [Table A].[C1] & "'") AS TB


Note: If the TB's returned by DLookup are text, you can convert them to numbers using CInt():-

CInt(DLookup([Table A].[C2],"Table B","[N]=" & [Table A].[C1])) AS TB


Hope this helps.

 

[dokuin]

That worked!

Your use of the DLookup function solved my problem.

I had known that the DLookup function could be used as the following, with Table B being the lookup table:
DLookup (
“Table B field name that contains wanted values”,
“Table B name”,
“Table B field that matches Table A linking field =” & [Table A name].[Table A linking field name]
)

What I did not know was that the DLookup function could be used as:
DLookup (
[Table A name].[Table A field name, that contains Table B field names that contain wanted values],
“Table B name”,
“Table B field that matches Table A linking field =” & [Table A name].[Table A linking field name]
)
Which allows for a variable field name.

One clarification. Your assumptions are half right. C1 and N are numeric fields. The second 10 is not a typo, it was my unclear way of trying to convey that there are repeated values in Table A, and that Table B has records that do not match Table A (it is not a one to one lookup).

You solved my column and row matrix lookup problem.