A Lookup of Sorts

[goaksmith]

The best way to explain this is with visuals I believe. So I am going to recreate my 2 tables below.

Table A (Trades)

Fund Number Fund Name State Status
9999 Hippo Fund MD Y
9998 Whale Fund WI Y
9998 Whale Fund MD N

Table B (State Status)

Fund Number MD WI MA NY
9999 Y Y Y N
9998 N Y N N


I have a Table A that is the trades that are processed in a day. Each trade has a fund number it was traded in, a fund name, and a state which the trade is originated from. I also have a Table B which is the master table of Fund Numbers and the 50 states in the US and if they are eligible to be traded in that state.

What I need is a way to look at the fund number in Table A and the State in Table A. Then using Table B it will return the value in Table B that correlates with the fund number and the state.

Example above being, Fund 9999 in MD (found in Table A) is according to Table B a Y for eligible. So I need that field to return a Y. In the next example Fund 9998 in WI (found in Table A) is according to Table B a Y for eligible. So I need that field to return a Y also. Finally for Fund 9998 in MD (found in Table A) is according to Table B a N for not eligible, So I need that field to return N.

I really have no idea where to begin solving this issue. I have some code written that performs a similar task, but I am looking for an easier way. Any suggestions would be helpful. Thanks!

 

[EMP]

I think the easiest way is to use the DLookup() function in a query to get the state status from Table B.

 

[Pat Hartman]

The problem is your table design. Your StateStatus table is "horizontal" like a spreadsheet instead of "vertical" like a real table.

The StateStatus table should contain only FundNumber and StateCd. The presence of a row in this table indicates the "yes" status.

Fund Number StateCd
9999 MD
9999 WI
9999 MA
9998 WI

With the StateStatus table properly structured, you would join to this table with a left join (since it is sparse) and if the state was found, the status is Yes and if not the status is No.

 

[EMP]

A database with a query using DLookup() is attached.

 

[GGib7711]

EMP

I registered to become a member in these forums just to say THANK YOU!

The solution you posted has helped me solve an interest rate lookup problem in our office. The interest rate table contains real time interest rates that frequently change. It never occurred to me I could use DLookup to look up values from varying columns in the table.

Thanks again.

George Gibson

 

[EMP]

The DLookup() function:

DLookup(expr, domain[, criteria])

is quite suitable for looking up values from a spreadsheet-like table as "In expr, you can include the name of a field in a table, a control on a form, a constant, or a function." (sentence quoted from the Access help file.)