Query to merge text values from two columns and DLookUp text value in another table

K

kc-mass

I have a large data feed that comes in and is mixed from two sources. One
part concerns transactions in NAmerica, the other the rest of the world
(reflects accounting department splits).

The NA data carries its country names in [NACountry]. The IntCountry
carries its country names
in [IntCountry].

What I need to do first is blend this data with a preference for
[NACountry]. That is first take [NACountry] if it is not null and otherwise
use [IntCountry] in the next process. This is pretty simple to do in the
basic query. Something like :

"SELECT IIf(IsNull([NACountry]),[INTCountry],[NACountry]) AS Country
FROM tblMonthTransactions;"

or
Just to get the data in another wise larger query set a transaction to:
Country: IIf(IsNull([NACountry]),[INTCountry],[NACountry])

I next, though preferably in the same query, want to compare the above
result to records in an ISO Country Code table to convert all country names
(France) to a two character code (FR).

My thought was to surround the above statement with a DLookUp statement. I
very seldom used DLookups and am floundering. The field Value to look for
is "A2". The table to look in is "tblISOCountries". The WHERE clause if
you will is "tblISOCountries!CountryName = Country" with
"Country"determined from above.

Any help on the syntax or noting that I am and why I am a fool will help.

Thx

Kevin
 
K

KARL DEWEY

Try these --
qryCountry --
SELECT PrimaryKey, IIf([NACountry] Is Null,[INTCountry],[NACountry]) AS
Country
FROM tblMonthTransactions;

SELECT qryCountry.PrimaryKey, A2
FROM qryCountry LEFT JOIN tblISOCountries ON qryCountry.Country =
tblISOCountries.CountryName;
 
K

kc-mass

Thanks Carl

Your two query solution will certainly work.

My real interest which I did not make clear enough is to do this all in one
query.

That's why I found myself looking at the DLookUp function which I thought
could use the results of the IIF function.

Any ideas on that syntax??

My other thought was to put the IIF function in a subquery but do not know
the syntax of that either.

Found several examples on line but none work yet.

Thx

Kevin
KARL DEWEY said:
Try these --
qryCountry --
SELECT PrimaryKey, IIf([NACountry] Is Null,[INTCountry],[NACountry]) AS
Country
FROM tblMonthTransactions;

SELECT qryCountry.PrimaryKey, A2
FROM qryCountry LEFT JOIN tblISOCountries ON qryCountry.Country =
tblISOCountries.CountryName;

--
Build a little, test a little.


kc-mass said:
I have a large data feed that comes in and is mixed from two sources.
One
part concerns transactions in NAmerica, the other the rest of the world
(reflects accounting department splits).

The NA data carries its country names in [NACountry]. The IntCountry
carries its country names
in [IntCountry].

What I need to do first is blend this data with a preference for
[NACountry]. That is first take [NACountry] if it is not null and
otherwise
use [IntCountry] in the next process. This is pretty simple to do in the
basic query. Something like :

"SELECT IIf(IsNull([NACountry]),[INTCountry],[NACountry]) AS Country
FROM tblMonthTransactions;"

or
Just to get the data in another wise larger query set a transaction to:
Country: IIf(IsNull([NACountry]),[INTCountry],[NACountry])

I next, though preferably in the same query, want to compare the above
result to records in an ISO Country Code table to convert all country
names
(France) to a two character code (FR).

My thought was to surround the above statement with a DLookUp statement.
I
very seldom used DLookups and am floundering. The field Value to look
for
is "A2". The table to look in is "tblISOCountries". The WHERE clause if
you will is "tblISOCountries!CountryName = Country" with
"Country"determined from above.

Any help on the syntax or noting that I am and why I am a fool will help.

Thx

Kevin
Click to expand...
Click to expand...
 
K

KARL DEWEY

UNTESTED UNTESTED
SELECT tblMonthTransactions.*, IIf([NACountry] Is
Null,[INTCountry],[NACountry]) AS Country, A2 AS Country_Code
FROM tblMonthTransactions, tblISOCountries
WHERE IIf([NACountry] Is Null,[INTCountry],[NACountry])
=tblISOCountries.CountryName;

--
Build a little, test a little.


kc-mass said:
Thanks Carl

Your two query solution will certainly work.

My real interest which I did not make clear enough is to do this all in one
query.

That's why I found myself looking at the DLookUp function which I thought
could use the results of the IIF function.

Any ideas on that syntax??

My other thought was to put the IIF function in a subquery but do not know
the syntax of that either.

Found several examples on line but none work yet.

Thx

Kevin
KARL DEWEY said:
Try these --
qryCountry --
SELECT PrimaryKey, IIf([NACountry] Is Null,[INTCountry],[NACountry]) AS
Country
FROM tblMonthTransactions;

SELECT qryCountry.PrimaryKey, A2
FROM qryCountry LEFT JOIN tblISOCountries ON qryCountry.Country =
tblISOCountries.CountryName;

--
Build a little, test a little.


kc-mass said:
I have a large data feed that comes in and is mixed from two sources.
One
part concerns transactions in NAmerica, the other the rest of the world
(reflects accounting department splits).

The NA data carries its country names in [NACountry]. The IntCountry
carries its country names
in [IntCountry].

What I need to do first is blend this data with a preference for
[NACountry]. That is first take [NACountry] if it is not null and
otherwise
use [IntCountry] in the next process. This is pretty simple to do in the
basic query. Something like :

"SELECT IIf(IsNull([NACountry]),[INTCountry],[NACountry]) AS Country
FROM tblMonthTransactions;"

or
Just to get the data in another wise larger query set a transaction to:
Country: IIf(IsNull([NACountry]),[INTCountry],[NACountry])

I next, though preferably in the same query, want to compare the above
result to records in an ISO Country Code table to convert all country
names
(France) to a two character code (FR).

My thought was to surround the above statement with a DLookUp statement.
I
very seldom used DLookups and am floundering. The field Value to look
for
is "A2". The table to look in is "tblISOCountries". The WHERE clause if
you will is "tblISOCountries!CountryName = Country" with
"Country"determined from above.

Any help on the syntax or noting that I am and why I am a fool will help.

Thx

Kevin
Click to expand...
Click to expand...
Click to expand...
 
K

kc-mass

Thanks very much Karl

KARL DEWEY said:
UNTESTED UNTESTED
SELECT tblMonthTransactions.*, IIf([NACountry] Is
Null,[INTCountry],[NACountry]) AS Country, A2 AS Country_Code
FROM tblMonthTransactions, tblISOCountries
WHERE IIf([NACountry] Is Null,[INTCountry],[NACountry])
=tblISOCountries.CountryName;

--
Build a little, test a little.


kc-mass said:
Thanks Carl

Your two query solution will certainly work.

My real interest which I did not make clear enough is to do this all in
one
query.

That's why I found myself looking at the DLookUp function which I thought
could use the results of the IIF function.

Any ideas on that syntax??

My other thought was to put the IIF function in a subquery but do not
know
the syntax of that either.

Found several examples on line but none work yet.

Thx

Kevin
KARL DEWEY said:
Try these --
qryCountry --
SELECT PrimaryKey, IIf([NACountry] Is Null,[INTCountry],[NACountry]) AS
Country
FROM tblMonthTransactions;

SELECT qryCountry.PrimaryKey, A2
FROM qryCountry LEFT JOIN tblISOCountries ON qryCountry.Country =
tblISOCountries.CountryName;

--
Build a little, test a little.


:

I have a large data feed that comes in and is mixed from two sources.
One
part concerns transactions in NAmerica, the other the rest of the
world
(reflects accounting department splits).

The NA data carries its country names in [NACountry]. The IntCountry
carries its country names
in [IntCountry].

What I need to do first is blend this data with a preference for
[NACountry]. That is first take [NACountry] if it is not null and
otherwise
use [IntCountry] in the next process. This is pretty simple to do in
the
basic query. Something like :

"SELECT IIf(IsNull([NACountry]),[INTCountry],[NACountry]) AS Country
FROM tblMonthTransactions;"

or
Just to get the data in another wise larger query set a transaction
to:
Country: IIf(IsNull([NACountry]),[INTCountry],[NACountry])

I next, though preferably in the same query, want to compare the
above
result to records in an ISO Country Code table to convert all country
names
(France) to a two character code (FR).

My thought was to surround the above statement with a DLookUp
statement.
I
very seldom used DLookups and am floundering. The field Value to look
for
is "A2". The table to look in is "tblISOCountries". The WHERE clause
if
you will is "tblISOCountries!CountryName = Country" with
"Country"determined from above.

Any help on the syntax or noting that I am and why I am a fool will
help.

Thx

Kevin
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

How to merge data from different fields in a crosstab query 1
Problem with criteria and trouble with instr() 6
Union Query - Select Distinct 2 fields 7
DLookup on two criteria 3
IIf query modification 6
Invalid syntax in dlookup 4
Strange value added to table 4
Record with multiple lines 1

Top