Vlookup in Access

[KimTong via AccessMonster.com]

Hi,

I have 'Table1' with 3 fields: MonthOfFlight (text 3 digits), USExchange
(number), CANexchange (number) and 'Table2' with 3 fields: Flightdate (date),
Agent (text), amount (number).

The layout of table1, should be like this:
Jan 1.24 1,1
Feb 1.30 1.05
Mar 1.26 1.15 ... etc

The loyout of table2 should be like this:
1/1/07 AAAAA 1000
2/1/07 CCCCC 1500
3/1/07 BBBBB 1250

I create a new query using 'Table2' with all 3 the fields plus 1 more field
'Exchange'. I want to take USExchange to fill on Exchange field based the
mont of flightdate. So I have to use Month(Flightdate) and search on 'Table1',
if it match take the UScexcahnge from ;Table1' to field Exchange.

So the query should be like this:
Date Agent Amount Exchange
1/1/07 AAAAA 1000 1.1
2/1/07 CCCCC 1500 1.05
3/1/07 BBBBB 1250 1.15

How can I put the formula/function on Exchange, so the result like above?. If
anyone can help me, I am appreciated. Thanks..

KF

 

[kissybean]

Hi,

You can write the SQL like this:

select table1.*, table2.exchange
from table1, table2
where table1.month = format(month(flightdate), 'mmm')


My syntax might not be exact but the general idea is there! Hope this helps!

 

[kissybean]

oops forgot to mention that if table 1 has multiple rows for months then you
will get multiple rows in your result set:

So

Table1
Jan 1.24 1.1
Jan 555 5.5
Feb 1.30 1.05
Mar 1.26 1.15 ... etc

Table 2
1/1/07 AAAAA 1000
2/1/07 CCCCC 1500
3/1/07 BBBBB 1250


Will product result set:

Date Agent Amount Exchange
1/1/07 AAAAA 1000 1.1
1/1/07 AAAAA 1000 5.5 (because you have two rows for Jan in
Table 1
2/1/07 CCCCC 1500 1.05
3/1/07 BBBBB 1250 1.15

Hi,

You can write the SQL like this:

select table1.*, table2.exchange
from table1, table2
where table1.month = format(month(flightdate), 'mmm')

My syntax might not be exact but the general idea is there! Hope this helps!

Hi,

[quoted text clipped - 27 lines]

KF

 

[KimTong via AccessMonster.com]

Hi,

Thank you for the answer, but I haven't tried it yet. It may have some 'Jan'
or 'Feb' in table2 (not table1). But table1 only have 12 record unique: Jan -
Dec with 12 different Exchange rate.

KF

oops forgot to mention that if table 1 has multiple rows for months then you
will get multiple rows in your result set:

So

Table1
Jan 1.24 1.1
Jan 555 5.5
Feb 1.30 1.05
Mar 1.26 1.15 ... etc

Table 2
1/1/07 AAAAA 1000
2/1/07 CCCCC 1500
3/1/07 BBBBB 1250

Will product result set:

Date Agent Amount Exchange
1/1/07 AAAAA 1000 1.1
1/1/07 AAAAA 1000 5.5 (because you have two rows for Jan in
Table 1
2/1/07 CCCCC 1500 1.05
3/1/07 BBBBB 1250 1.15

Hi,

[quoted text clipped - 11 lines]

 

[KimTong via AccessMonster.com]

Hi,

I tried it, but it didn't work as I want.

Hi,

I have 'Table1' with 3 fields: MonthOfFlight (text 3 digits), USExchange
(number), CANexchange (number) and 'Table2' with 3 fields: Flightdate (date),
Agent (text), amount (number).

The layout of table1, should be like this:
Jan 1.24 1,1
Feb 1.30 1.05
Mar 1.26 1.15 ... etc

The loyout of table2 should be like this:
1/1/07 AAAAA 1000
2/1/07 CCCCC 1500
3/1/07 BBBBB 1250

I create a new query using 'Table2' with all 3 the fields plus 1 more field
'Exchange'. I want to take USExchange to fill on Exchange field based the
mont of flightdate. So I have to use Month(Flightdate) and search on 'Table1',
if it match take the UScexcahnge from ;Table1' to field Exchange.

So the query should be like this:
Date Agent Amount Exchange
1/1/07 AAAAA 1000 1.1
2/1/07 CCCCC 1500 1.05
3/1/07 BBBBB 1250 1.15

How can I put the formula/function on Exchange, so the result like above?. If
anyone can help me, I am appreciated. Thanks..

KF

 

[John W. Vinson]

I tried it, but it didn't work as I want.

Then fix it so it does.

We'll be glad to help if you'll a) post the code you actually used, b)
indicate what it did, and c) indicate what you want.

We're not there, and cannot read your mind.

John W. Vinson [MVP]