DLookup on two criteria

C

Coral

Hi, i am a new member of this group and really looking forward to some
urgent help..i have a problem converting my Xcel query logic to
Access...basically can we use DLookup in a Query- Design view
(Criteria field) to define/match two crietria:

I have two Tables Data (50,000 rows of Department and Country -
fields) and T530 (Valid Dept and Country) besides quite a few other
fields. What i need is to get a matching result for Country (Aust/NZ)
first and then a matching Dept that is valid from Table T530. So for
Country i have put a Join between the two tables (Join #2) on Country
field and for Dept, this is what i have enetered in


DLookUp("[ValidDept]","T530_ANZ","ValidDept =
.[Data].
[Department]")

but i end up getting error: '....Access can't find the name
.
[Data].[Department]'
 
P

pietlinden

Hi, i am a new member of this group and really looking forward to some
urgent help..i have a problem converting my Xcel query logic to
Access...basically can we use DLookup in a Query- Design view
(Criteria field) to define/match two crietria:

I have two Tables Data (50,000 rows of Department and Country -
fields) and T530 (Valid Dept and Country) besides quite a few other
fields. What i need is to get a matching result for Country (Aust/NZ)
first and then a matching Dept that is valid from Table T530. So for
Country i have put a Join between the two tables (Join #2) on Country
field and for Dept, this is what i have enetered in

DLookUp("[ValidDept]","T530_ANZ","ValidDept =
.[Data].
[Department]")

but i end up getting error: '....Access can't find the name
.
[Data].[Department]'


why not just use a query? IF you need to have two fields in your
join, then that's no problem...
 
C

Coral

Hi, i am a new member of this group and really looking forward to some
urgent help..i have a problem converting my Xcel query logic to
Access...basically can we use DLookup in a Query- Design view
(Criteria field) to define/match two crietria:
I have two Tables Data (50,000 rows of Department and Country -
fields) and T530 (Valid Dept and Country) besides quite a few other
fields. What i need is to get a matching result for Country (Aust/NZ)
first and then a matching Dept that is valid from Table T530. So for
Country i have put a Join between the two tables (Join #2) on Country
field and for Dept, this is what i have enetered in
DLookUp("[ValidDept]","T530_ANZ","ValidDept =
.[Data].
[Department]")
but i end up getting error: '....Access can't find the name
.
[Data].[Department]'


why not just use a query? IF you need to have two fields in your
join, then that's no problem...- Hide quoted text -

- Show quoted text -


Hi..the problem in using two joins is that my table fields (Dept) is
not unique...so for bothe countries Aust and NZ we have same dept that
get repeated...so in Data table (50,000) dept (many repeated) and T530
table (dept are repeated) and so joining brings multiple records
 
J

John W. Vinson

Hi..the problem in using two joins is that my table fields (Dept) is
not unique...so for bothe countries Aust and NZ we have same dept that
get repeated...so in Data table (50,000) dept (many repeated) and T530
table (dept are repeated) and so joining brings multiple records

So join as Piet suggests - on BOTH fields. There would be two join lines in
the query - dept joined to dept, country to country. You're not limited to
joining on just one field!

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top