Index Match for Access help please

[CBONG]

Hi, I am new to access and trying to use index match function.

This is what I am trying to do

2 Tables

tblVal

Validation	Phase	Validation date
VAL1	A	??
VAL2	B
VAL3	C
VAL2	A

<tbody>
</tbody>

which mean Val 1-3 can be any phase or vice versa

tblValDate

VALIDATION	PHASE A	PHASE B	PHASE C
VAL1	JAN	FEB	MAR
VAL2	APR	MAY	JUN
VAL3	JUL	AUG	SEP

<tbody>
</tbody>

I would like to pull the data from tblValDate to tblVal under the Validation Date
Hope this makes sense

How can I do this?

Thanks in advance

 

[ChrisM]

This is not the best way to set up table relationships in a database, for exactly the problem you have run in to. Since you are new to Access, you should spend time reading about how to set up relational tables.

The answer to your question is to set up tblValDate like this:

Validation, Phase, Month
Val1, A, JAN
Val1, B, FEB
Val1, C, MAR

Then join your two tables on Validation-to-Validation, and Phase-to-Phase, then you can easily set "Validation date" = "Month"

You end up with rows that look like they contain duplicate/redundant information (ie repeating VAL1 three times), but this is the way databases work, so you can easily do joins and queries.

 

[CBONG]

Thanks ChrisM for the idea. It solved my problem.

And thanks for your advise