Need help about using Vlokup in access 2007

[kdbrbr]

Hello guys,,

i have two table,
Table #1 : identity
No | Name | Address |
1 | Alex | St. Santana |
2 | Sarah | North West Area |
3 | Tommy | Missipi |
4 | Young | Colorado |

Table #2 : Process
No | NoReg | Process | Word_Proc | DueDate |
1 | 101 | Initial Process | IP | 10/10/2011 |
2 | 201 | Initial Process | IP | 11/10/2011 |
3 | 101 | Entry Data | ED | 11/10/2011 |


4 | 101 | Finding Problems | FP | 12/10/2011 |
5 | 201 | Entry Data | ED | 12/10/2011 |
6 | 301 | Initial Process | IP | 13/10/2011 |
7 | 401 | Initial Process | IP | 13/10/2011 |
8 | 301 | Entry Data | ED | 14/10/2011 |
9 | 101 | Analyze Solution | AS | 15/10/2011 |

i want make a query,, to get this result ::
Query #1
NoReg| Name | Date IP | Date ED | Date FP | Date AS |
101 | Alex | 10/10/2011 | 11/10/2011 | 12/10/2011| 15/20/2011|
201 | Sarah | 11/10/2011 | 12/10/2011 | | |
301 | Tommy| 13/10/2011 | 14/10/2011 | | |
401 | Young | 13/10/2011 | | | |

I'm very stress that after 3 days i can't solve how to do it ??
Is anyone here can help me....?? >_<

I'm try to use this formula
......
IIf([Process.Word_Proc]=IP,[Process.DueDate],Null) AS [Date IP]
......
but still didn't give my answerrr...
i try to used dlookup but still i don't know much how to use this formula
hiksss.... Hiks.... hikss.....

Thanks before for anyone who helps me....
May GOD always help you to....

[June7]

How are these tables related? There must be a common key value that they can be linked on.

How do you know Alex and 101 go together, same for Sarah and 201 and Tommy and 301 and Young and 401. The only possible connection I see is the No field of Table 1 coincides with the first digit of NoReg in Table2.

What you want to do with Table2 is transpose the data. This can be done with the sample data by:
TRANSFORM First(Table2.[DueDate]) AS FirstOfDueDate
SELECT Left([NoReg],1) AS [No], [NoReg]
FROM Table2
GROUP BY Left([NoReg],1), [NoReg]
PIVOT Word_Proc;

If I am correct about the relationship of No and NoReg, can now do a SELECT JOIN with Table1 and the CROSSTAB query.

However, this Crosstab will not work properly if there is more than one record with same NoReg and WordProc codes. In that case, other methods will be required to transpose the data. One of which uses the expression you tried. Like:
SELECT Left([NoReg],1) AS [No], NoReg, IIf([Word_Proc]="AS" [DueDate],Null) AS DateAS FROM Table2;
Now join this query to Table1.

[kdbrbr]

hehehe....sorry, i make wrong copy.
Yes thats want i mean June7

what i want to copy is

No | Name | Address |
101 | Alex | St. Santana |
201 | Sarah | North West Area |
301 | Tommy | Missipi |
401 | Young | Colorado |


Hmmm,,, thanks a lots "June7". I'll tray to transpose it...

[kdbrbr]

dude it didn't works,,,
The result is similar with my iff clause
the result is like

NoReg| Name | Date IP | Date ED | Date FP | Date AS |
101 | Alex | | | | 15/20/2011 |
101 | Alex | | | 12/10/2011| |
101 | Alex | | 11/10/2011 | | |
101 | Alex | 10/10/2011 | | | |
201 | Sarah | | 12/10/2011 | | |
201 | Sarah | 11/10/2011 | | | |
301 | Tommy| | 14/10/2011 | | |
301 | Tommy| 13/10/2011 | | | |
401 | Young | 13/10/2011 | | | |

hikss...hiks.... ?

[June7]

Then don't need the Left([NoReg],1). I built tables with your data (although I put the dates in as mm/dd/yyyy). Neither should make difference.

It works for me.

Show the exact SQL you used. Provide your project for analysis so have complete set of data to test.