Lookup Value from Another Table but in Main Table Value in 5 other Column

[amit.bhatt]

Name	ID 1	ID 2	ID 3	ID 4	ID 5	ID 6	ID 7
Amit	1020	1050	1030	1080	1090	1075	1065
AnuJ	1020	1040	1010	1050	1030	1080	1075

ID	Status
1010	Open
1030	Open
1040	Block
1050	Block
1080	Block
1050	Open
1090	Block
1075	Open
1065	Block

I have large amount of Data so this task only done in ACCESS.

Above is my Data type of in two Table.


Result need as below.

Name

ID 1

Status

ID 2

Status

ID 3

Status

ID 4

Status

ID 5

Status

ID 6

Status

ID 7

Status

Amit

1020

Open

1050

Block

1030

Open

1080

Block

1090

Block

1075

Open

1065

Block

AnuJ

1020

Open

1040

Block

1010

Open

1050

Block

1030

Open

1080

Block

1075

Open

I think its very esay for Access master.

but i dont know please help me...

[alansidman]

I believe that your first table is not normalized and this will create a big issue to get the results you want. Your first table should probably look like this. Note that the words Name and Value are reserved terms in Access and should not be used as a field name.

Table1
------------
tName
ID
tValue

In this manner, you can join the tables on the ID field
Here is a SQL statement that will join the information

SELECT Table1.tName, Table1.ID, Table1.tValue, Table2.Status
FROM Table1 INNER JOIN Table2 ON Table1.tValue = Table2.ID;

this presentation is vertical and not horizontal.