QUERY (vlookup fashioned)

[Sinner]

Hi,
I have a field name 'USER' in tableMAIN.
How do I replace the user names with corresponding user names. I can
do that in xl using vlookup but now I'm trying to find a way to do
that in access.
For a user mismatch, it should give NA

Thx.

[Salad]

Sinner wrote:

Hi,
I have a field name 'USER' in tableMAIN.
How do I replace the user names with corresponding user names. I can
do that in xl using vlookup but now I'm trying to find a way to do
that in access.
For a user mismatch, it should give NA

Thx.

There are some alternatives.

One way is to use Docmd.TransferSpreadsheet and then use Vlookup in Excel.

Or you open both Access and Excel and then cut and paste the values into
a table.

Or you could manually enter the data into the table.

Or you might try an Update query.

If you would supply some further background regarding your problem and
you might get a helpful answer.

[Sinner]

On Nov 18, 9:05*pm, Salad <[e-mail]:

Sinner wrote:

Hi,

I have a field name 'USER' in tableMAIN.
How do I replace the user names with corresponding user names. I can
do that in xl using vlookup but now I'm trying to find a way to do
that in access.
For a user mismatch, it should give NA

Thx.

There are some alternatives.

One way is to use Docmd.TransferSpreadsheet and then use Vlookup in Excel..

Or you open both Access and Excel and then cut and paste the values into
a table.

Or you could manually enter the data into the table.

Or you might try an Update query.

If you would supply some further background regarding your problem and
you might get a helpful answer.

Hi,
The tableMAIN is as follows:

Fields are:
item date amount USER location
abc
abc
def
abc
xyz

UserlistTABLE is as follows:

Fields are:

user1 user2
abc ggg
def kkk
The result should yield:
item date amount USER location
ggg
ggg
kkk
ggg
NA(NOT AVAILABLE)

[Salad]

Sinner wrote:

On Nov 18, 9:05 pm, Salad <[e-mail]:

>>Sinner wrote:

>>>Hi,

>>>I have a field name 'USER' in tableMAIN.
How do I replace the user names with corresponding user names. I can
do that in xl using vlookup but now I'm trying to find a way to do
that in access.
For a user mismatch, it should give NA

>>>Thx.

There are some alternatives.

One way is to use Docmd.TransferSpreadsheet and then use Vlookup in Excel.

Or you open both Access and Excel and then cut and paste the values into
a table.

Or you could manually enter the data into the table.

Or you might try an Update query.

If you would supply some further background regarding your problem and
you might get a helpful answer.

Hi,
The tableMAIN is as follows:

Fields are:
item date amount USER location
abc
abc
def
abc
xyz

UserlistTABLE is as follows:

Fields are:

user1 user2
abc ggg
def kkk
The result should yield:
item date amount USER location
ggg
ggg
kkk
ggg
NA(NOT AVAILABLE)

I really don't know how you'd do that. The userlist table has some user
ids and TableMain has no user ids...just some locations.

I did this as a test. I created table Z1. It had 1 field; UserName. I
had 2 records; A and C. I created table Z2. It had 2 fields; Username
and Location with 3 records A, B, and C. My query is this

UPDATE z2 LEFT JOIN z1 ON z2.UserName = z1.UserName SET z2.Location =
IIf(Not IsNull([Z1]![UserName]),[Z1]![UserName],"N/A");

The result was
A A
B N/A
C C

Normally you'd have 2 tables. Start by selecting
Query/New/FindUnmatched. You'd now have a list of records that don't
exist between both tables. Once you have this, select from the menu
Query/Update. Now using the above IIF() statement as a template, change
it to suite your needs and removed the column with the ISNULL() criteria
row. Before you do this, because you are unfamiliar with this stuff,
make a backup of your tables before you run the query.

But I really see no way, with the example above you provided, to link
the two tables together.

[Sinner]

On Nov 19, 7:35*pm, Salad <[e-mail]:

Sinner wrote:

On Nov 18, 9:05 pm, Salad <[e-mail]:

>Sinner wrote:

>>Hi,

>>I have a field name 'USER' in tableMAIN.
How do I replace the user names with corresponding user names. I can
do that in xl using vlookup but now I'm trying to find a way to do
that in access.
For a user mismatch, it should give NA

>>Thx.

>There are some alternatives.

>One way is to use Docmd.TransferSpreadsheet and then use Vlookup in Excel.

>Or you open both Access and Excel and then cut and paste the values into
a table.

>Or you could manually enter the data into the table.

>Or you might try an Update query.

>If you would supply some further background regarding your problem and
you might get a helpful answer.

Hi,
The tableMAIN is as follows:

Fields are:
item * *date * * *amount * USER * *location
* * * * * * * * * * * * * * * * * ** abc
* * * * * * * * * * * * * * * * * ** abc
* * * * * * * * * * * * * * * * * ** def
* * * * * * * * * * * * * * * * * ** abc
* * * * * * * * * * * * * * * * * ** xyz

UserlistTABLE is as follows:

Fields are:

user1 * *user2
abc * * * *ggg
def * * * * kkk

The result should yield:

item * *date * * *amount * USER * *location
* * * * * * * * * * * * * * * * * ** ggg
* * * * * * * * * * * * * * * * * ** ggg
* * * * * * * * * * * * * * * * * ** kkk
* * * * * * * * * * * * * * * * * ** ggg
* * * * * * * * * * * * * * * * * ** NA(NOT AVAILABLE)

I really don't know how you'd do that. *The userlist table has some user
ids and TableMain has no user ids...just some locations.

I did this as a test. *I created table Z1. *It had 1 field; UserName.*I
had 2 records; A and C. *I created table Z2. *It had 2 fields; Username
and Location with 3 records A, B, and C. *My query is this

UPDATE z2 LEFT JOIN z1 ON z2.UserName = z1.UserName SET z2.Location =
IIf(Not IsNull([Z1]![UserName]),[Z1]![UserName],"N/A");

The result was
* * * * A * * * A
* * * * B * * * N/A
* * * * C * * * C

Normally you'd have 2 tables. *Start by selecting
Query/New/FindUnmatched. *You'd now have a list of records that don't
exist between both tables. *Once you have this, select from the menu
Query/Update. *Now using the above IIF() statement as a template, change
it to suite your needs and removed the column with the ISNULL() criteria
row. *Before you do this, because you are unfamiliar with this stuff,
make a backup of your tables before you run the query.

But I really see no way, with the example above you provided, to link
the two tables together.- Hide quoted text -

- Show quoted text -

Hi,

Thx for your reply.
A sample of my requirement is at link below.
http://www.savefile.com/files/1893950

I have worked around making a calculated field & then union will rest
of the tables to achieve what I was looking for.
Still I need to be able to get the 'N'A in case I don't match an IDor
if its blank. Also I would like to see if required IDs already exist,
it should skip it & check next & do the necessary thing.

Thx.

[Salad]

Sinner wrote:

On Nov 19, 7:35 pm, Salad <[e-mail]:

>>Sinner wrote:

>>>On Nov 18, 9:05 pm, Salad <[e-mail]:

>>>>Sinner wrote:

>>>>>Hi,

>>>>>I have a field name 'USER' in tableMAIN.
>How do I replace the user names with corresponding user names. I can
>do that in xl using vlookup but now I'm trying to find a way to do
>that in access.
>For a user mismatch, it should give NA

>>>>>Thx.

>>>>There are some alternatives.

>>>>One way is to use Docmd.TransferSpreadsheet and then use Vlookup in Excel.

>>>>Or you open both Access and Excel and then cut and paste the values into
a table.

>>>>Or you could manually enter the data into the table.

>>>>Or you might try an Update query.

>>>>If you would supply some further background regarding your problem and
you might get a helpful answer.

>>>Hi,
The tableMAIN is as follows:

>>>Fields are:
item date amount USER location
abc
abc
def
abc
xyz

>>>UserlistTABLE is as follows:

>>>Fields are:

>>>user1 user2
abc ggg
def kkk

>>>The result should yield:

>>>item date amount USER location
ggg
ggg
kkk
ggg
NA(NOT AVAILABLE)

I really don't know how you'd do that. The userlist table has some user
ids and TableMain has no user ids...just some locations.

I did this as a test. I created table Z1. It had 1 field; UserName. I
had 2 records; A and C. I created table Z2. It had 2 fields; Username
and Location with 3 records A, B, and C. My query is this

UPDATE z2 LEFT JOIN z1 ON z2.UserName = z1.UserName SET z2.Location =
IIf(Not IsNull([Z1]![UserName]),[Z1]![UserName],"N/A");

The result was
A A
B N/A
C C

Normally you'd have 2 tables. Start by selecting
Query/New/FindUnmatched. You'd now have a list of records that don't
exist between both tables. Once you have this, select from the menu
Query/Update. Now using the above IIF() statement as a template, change
it to suite your needs and removed the column with the ISNULL() criteria
row. Before you do this, because you are unfamiliar with this stuff,
make a backup of your tables before you run the query.

But I really see no way, with the example above you provided, to link
the two tables together.- Hide quoted text -

- Show quoted text -

Hi,

Thx for your reply.
A sample of my requirement is at link below.
http://www.savefile.com/files/1893950

You should tell people to skip the ad.

I have worked around making a calculated field & then union will rest
of the tables to achieve what I was looking for.
Still I need to be able to get the 'N'A in case I don't match an IDor
if its blank. Also I would like to see if required IDs already exist,
it should skip it & check next & do the necessary thing.

Thx.

I looked at your Excel spreadsheet. I haven't a clue as to what you are
attempting to do. Is this an IQ or aptitude test or something for Mensa
members to figure out?

There's an excellent children's book, also good reading for adults,
called "The Phantom Tollbooth". Milo, the hero of the story, is on an
errand to save Rhyme and Reason. He heads into the Mountains of
Ignorance and meets a stranger along the way asking for some help. The
stranger wants Milo to fill up a hole from a pile of sand a few feet
away. But he must use a tweezer to move the sand to a hole. After many
hours of putting a grain of sand inbetween the tweezer blades and
carrying the grain to the hole Milo calculates the time it will take to
fill that hole and it is a long, long time. And the stranger had many
more tasks similar to that one to do and Milo could help him out. Milo
had met one of the many monsters in those mountains, an amiable person
but a time waster. You remind me of that character.

If you can't describe your problem, if you send people to web sites
without instructions on how to view your file, you aren't going to get
much help here when you waste people's time.

Nobody here knows if you have linked the spreadsheet to your Access
database, if you know what an update query is, if you know what an
append query is, if you have a clue about Access or databases or
programming or logic or VBA, or if you even know what you are doing or
attempting to accomplish. If you expect us to extract a nugget of
information from you in each post, playing Mindreading 101, and hoping
to get closer to your solution over many days of extraction, I expect
you'll be waiting a long time for any solution.

[Sinner]

On Nov 19, 10:43*pm, Salad <[e-mail]:

Sinner wrote:

On Nov 19, 7:35 pm, Salad <[e-mail]:

>Sinner wrote:

>>On Nov 18, 9:05 pm, Salad <[e-mail]:

>>>Sinner wrote:

>>>>Hi,

>>>>I have a field name 'USER' in tableMAIN.
How do I replace the user names with corresponding user names. I can
do that in xl using vlookup but now I'm trying to find a way to do
that in access.
For a user mismatch, it should give NA

>>>>Thx.

>>>There are some alternatives.

>>>One way is to use Docmd.TransferSpreadsheet and then use Vlookup in Excel.

>>>Or you open both Access and Excel and then cut and paste the values into
a table.

>>>Or you could manually enter the data into the table.

>>>Or you might try an Update query.

>>>If you would supply some further background regarding your problem and
you might get a helpful answer.

>>Hi,
The tableMAIN is as follows:

>>Fields are:
item * *date * * *amount * USER * *location
* * * * * * * * * * * * * * * * * * *abc
* * * * * * * * * * * * * * * * * * *abc
* * * * * * * * * * * * * * * * * * *def
* * * * * * * * * * * * * * * * * * *abc
* * * * * * * * * * * * * * * * * * *xyz

>>UserlistTABLE is as follows:

>>Fields are:

>>user1 * *user2
abc * * * *ggg
def * * * * kkk

>>The result should yield:

>>item * *date * * *amount * USER * *location
* * * * * * * * * * * * * * * * * * *ggg
* * * * * * * * * * * * * * * * * * *ggg
* * * * * * * * * * * * * * * * * * *kkk
* * * * * * * * * * * * * * * * * * *ggg
* * * * * * * * * * * * * * * * * * *NA(NOT AVAILABLE)

>I really don't know how you'd do that. *The userlist table has some user
ids and TableMain has no user ids...just some locations.

>I did this as a test. *I created table Z1. *It had 1 field; UserName. *I
had 2 records; A and C. *I created table Z2. *It had 2 fields; Username
and Location with 3 records A, B, and C. *My query is this

>UPDATE z2 LEFT JOIN z1 ON z2.UserName = z1.UserName SET z2.Location =
IIf(Not IsNull([Z1]![UserName]),[Z1]![UserName],"N/A");

>The result was
* * * *A * * * A
* * * *B * * * N/A
* * * *C * * * C

>Normally you'd have 2 tables. *Start by selecting
Query/New/FindUnmatched. *You'd now have a list of records that don't
exist between both tables. *Once you have this, select from the menu
Query/Update. *Now using the above IIF() statement as a template, change
it to suite your needs and removed the column with the ISNULL() criteria
row. *Before you do this, because you are unfamiliar with this stuff,
make a backup of your tables before you run the query.

>But I really see no way, with the example above you provided, to link
the two tables together.- Hide quoted text -

>- Show quoted text -

Hi,

Thx for your reply.
A sample of my requirement is at link below.
http://www.savefile.com/files/1893950

You should tell people to skip the ad.

I have worked around making a calculated field & then union will rest
of the tables to achieve what I was looking for.
Still I need to be able to get the 'N'A in case I don't match an IDor
if its blank. Also I would like to see if required IDs already exist,
it should skip it & check next & do the necessary thing.

Thx.

I looked at your Excel spreadsheet. *I haven't a clue as to what you are
attempting to do. *Is this an IQ or aptitude test or something for Mensa
members to figure out?

There's an excellent children's book, also good reading for adults,
called "The Phantom Tollbooth". *Milo, the hero of the story, is on an
errand to save Rhyme and Reason. *He heads into the Mountains of
Ignorance and meets a stranger along the way asking for some help. *The
stranger wants Milo to fill up a hole from a pile of sand a few feet
away. *But he must use a tweezer to move the sand to a hole. *After many
hours of putting a grain of sand inbetween the tweezer blades and
carrying the grain to the hole Milo calculates the time it will take to
fill that hole and it is a long, long time. *And the stranger had many
more tasks similar to that one to do and Milo could help him out. *Milo
had met one of the many monsters in those mountains, an amiable person
but a time waster. *You remind me of that character.

If you can't describe your problem, if you send people to web sites
without instructions on how to view your file, you aren't going to get
much help here when you waste people's time.

Nobody here knows if you have linked the spreadsheet to your Access
database, if you know what an update query is, if you know what an
append query is, if you have a clue about Access or databases or
programming or logic or VBA, or if you even know what you are doing or
attempting to accomplish. *If you expect us to extract a nugget of
information from you in each post, playing Mindreading 101, and hoping
to get closer to your solution over many days of extraction, I expect
you'll be waiting a long time for any solution.- Hide quoted text -

- Show quoted text -

Hi,

Simply put, the xl sheet that I sent is NOT LINKED TO ACCESS.
It is just an example, a snap shot of what I was trying to do in
access.

tableMAIN sheet is actually the name of table in access.
userlistTABLE is actually the name of a table in access.
Result sheet is the required query in access.

Story was good.
I didn't know it was that hard to download a simple file from that
link.. silly.

[Salad]

Sinner wrote:

On Nov 19, 10:43 pm, Salad <[e-mail]:

>>Sinner wrote:

>>>On Nov 19, 7:35 pm, Salad <[e-mail]:

>>>>Sinner wrote:

>>>>>On Nov 18, 9:05 pm, Salad <[e-mail]:

>>>>>>Sinner wrote:

>>>>>>>Hi,

>>>>>>>I have a field name 'USER' in tableMAIN.
>>>How do I replace the user names with corresponding user names. I can
>>>do that in xl using vlookup but now I'm trying to find a way to do
>>>that in access.
>>>For a user mismatch, it should give NA

>>>>>>>Thx.

>>>>>>There are some alternatives.

>>>>>>One way is to use Docmd.TransferSpreadsheet and then use Vlookup in Excel.

>>>>>>Or you open both Access and Excel and then cut and paste the values into
>>a table.

>>>>>>Or you could manually enter the data into the table.

>>>>>>Or you might try an Update query.

>>>>>>If you would supply some further background regarding your problem and
>>you might get a helpful answer.

>>>>>Hi,
>The tableMAIN is as follows:

>>>>>Fields are:
>item date amount USER location
abc
abc
def
abc
xyz

>>>>>UserlistTABLE is as follows:

>>>>>Fields are:

>>>>>user1 user2
>abc ggg
>def kkk

>>>>>The result should yield:

>>>>>item date amount USER location
ggg
ggg
kkk
ggg
NA(NOT AVAILABLE)

>>>>I really don't know how you'd do that. The userlist table has some user
ids and TableMain has no user ids...just some locations.

>>>>I did this as a test. I created table Z1. It had 1 field; UserName. I
had 2 records; A and C. I created table Z2. It had 2 fields; Username
and Location with 3 records A, B, and C. My query is this

>>>>UPDATE z2 LEFT JOIN z1 ON z2.UserName = z1.UserName SET z2.Location =
IIf(Not IsNull([Z1]![UserName]),[Z1]![UserName],"N/A");

>>>>The result was
A A
B N/A
C C

>>>>Normally you'd have 2 tables. Start by selecting
Query/New/FindUnmatched. You'd now have a list of records that don't
exist between both tables. Once you have this, select from the menu
Query/Update. Now using the above IIF() statement as a template, change
it to suite your needs and removed the column with the ISNULL() criteria
row. Before you do this, because you are unfamiliar with this stuff,
make a backup of your tables before you run the query.

>>>>But I really see no way, with the example above you provided, to link
the two tables together.- Hide quoted text -

>>>>- Show quoted text -

>>>Hi,

>>>Thx for your reply.
A sample of my requirement is at link below.
http://www.savefile.com/files/1893950

You should tell people to skip the ad.

>>>I have worked around making a calculated field & then union will rest
of the tables to achieve what I was looking for.
Still I need to be able to get the 'N'A in case I don't match an IDor
if its blank. Also I would like to see if required IDs already exist,
it should skip it & check next & do the necessary thing.

>>>Thx.

I looked at your Excel spreadsheet. I haven't a clue as to what you are
attempting to do. Is this an IQ or aptitude test or something for Mensa
members to figure out?

There's an excellent children's book, also good reading for adults,
called "The Phantom Tollbooth". Milo, the hero of the story, is on an
errand to save Rhyme and Reason. He heads into the Mountains of
Ignorance and meets a stranger along the way asking for some help. The
stranger wants Milo to fill up a hole from a pile of sand a few feet
away. But he must use a tweezer to move the sand to a hole. After many
hours of putting a grain of sand inbetween the tweezer blades and
carrying the grain to the hole Milo calculates the time it will take to
fill that hole and it is a long, long time. And the stranger had many
more tasks similar to that one to do and Milo could help him out. Milo
had met one of the many monsters in those mountains, an amiable person
but a time waster. You remind me of that character.

If you can't describe your problem, if you send people to web sites
without instructions on how to view your file, you aren't going to get
much help here when you waste people's time.

Nobody here knows if you have linked the spreadsheet to your Access
database, if you know what an update query is, if you know what an
append query is, if you have a clue about Access or databases or
programming or logic or VBA, or if you even know what you are doing or
attempting to accomplish. If you expect us to extract a nugget of
information from you in each post, playing Mindreading 101, and hoping
to get closer to your solution over many days of extraction, I expect
you'll be waiting a long time for any solution.- Hide quoted text -

- Show quoted text -

Hi,

Simply put, the xl sheet that I sent is NOT LINKED TO ACCESS.
It is just an example, a snap shot of what I was trying to do in
access.

And what are you trying to do?
You have
abc ggg
def kkk

and that somehow becomes

ggg
ggg
kkk
ggg
NA(NOT AVAILABLE)

in column location. No rhyme or reason. Nothing to link the two tables
together.

>
tableMAIN sheet is actually the name of table in access.
userlistTABLE is actually the name of a table in access.
Result sheet is the required query in access.

Story was good.
I didn't know it was that hard to download a simple file from that
link.. silly.

Let people know you aren't selling web site designs. Stop ads. Etc.

[Sinner]

On Nov 20, 7:59*pm, Salad <[e-mail]:

Sinner wrote:

On Nov 19, 10:43 pm, Salad <[e-mail]:

>Sinner wrote:

>>On Nov 19, 7:35 pm, Salad <[e-mail]:

>>>Sinner wrote:

>>>>On Nov 18, 9:05 pm, Salad <[e-mail]:

>>>>>Sinner wrote:

>>>>>>Hi,

>>>>>>I have a field name 'USER' in tableMAIN.
>>How do I replace the user names with corresponding user names. I can
>>do that in xl using vlookup but now I'm trying to find a way to do
>>that in access.
>>For a user mismatch, it should give NA

>>>>>>Thx.

>>>>>There are some alternatives.

>>>>>One way is to use Docmd.TransferSpreadsheet and then use Vlookup inExcel.

>>>>>Or you open both Access and Excel and then cut and paste the valuesinto
>a table.

>>>>>Or you could manually enter the data into the table.

>>>>>Or you might try an Update query.

>>>>>If you would supply some further background regarding your problem and
>you might get a helpful answer.

>>>>Hi,
The tableMAIN is as follows:

>>>>Fields are:
item * *date * * *amount * USER * *location
* * * * * * * * * * * * * * * * ** abc
* * * * * * * * * * * * * * * * ** abc
* * * * * * * * * * * * * * * * ** def
* * * * * * * * * * * * * * * * ** abc
* * * * * * * * * * * * * * * * ** xyz

>>>>UserlistTABLE is as follows:

>>>>Fields are:

>>>>user1 * *user2
abc * * * *ggg
def * * * * kkk

>>>>The result should yield:

>>>>item * *date * * *amount * USER * *location
* * * * * * * * * * * * * * * * ** ggg
* * * * * * * * * * * * * * * * ** ggg
* * * * * * * * * * * * * * * * ** kkk
* * * * * * * * * * * * * * * * ** ggg
* * * * * * * * * * * * * * * * ** NA(NOT AVAILABLE)

>>>I really don't know how you'd do that. *The userlist table has someuser
ids and TableMain has no user ids...just some locations.

>>>I did this as a test. *I created table Z1. *It had 1 field; UserName. *I
had 2 records; A and C. *I created table Z2. *It had 2 fields; Username
and Location with 3 records A, B, and C. *My query is this

>>>UPDATE z2 LEFT JOIN z1 ON z2.UserName = z1.UserName SET z2.Location=
IIf(Not IsNull([Z1]![UserName]),[Z1]![UserName],"N/A");

>>>The result was
* * * A * * * A
* * * B * * * N/A
* * * C * * * C

>>>Normally you'd have 2 tables. *Start by selecting
Query/New/FindUnmatched. *You'd now have a list of records that don't
exist between both tables. *Once you have this, select from the menu
Query/Update. *Now using the above IIF() statement as a template, change
it to suite your needs and removed the column with the ISNULL() criteria
row. *Before you do this, because you are unfamiliar with this stuff,
make a backup of your tables before you run the query.

>>>But I really see no way, with the example above you provided, to link
the two tables together.- Hide quoted text -

>>>- Show quoted text -

>>Hi,

>>Thx for your reply.
A sample of my requirement is at link below.
http://www.savefile.com/files/1893950

>You should tell people to skip the ad.

>>I have worked around making a calculated field & then union will rest
of the tables to achieve what I was looking for.
Still I need to be able to get the 'N'A in case I don't match an IDor
if its blank. Also I would like to see if required IDs already exist,
it should skip it & check next & do the necessary thing.

>>Thx.

>I looked at your Excel spreadsheet. *I haven't a clue as to what you are
attempting to do. *Is this an IQ or aptitude test or something for Mensa
members to figure out?

>There's an excellent children's book, also good reading for adults,
called "The Phantom Tollbooth". *Milo, the hero of the story, is on an
errand to save Rhyme and Reason. *He heads into the Mountains of
Ignorance and meets a stranger along the way asking for some help. *The
stranger wants Milo to fill up a hole from a pile of sand a few feet
away. *But he must use a tweezer to move the sand to a hole. *Aftermany
hours of putting a grain of sand inbetween the tweezer blades and
carrying the grain to the hole Milo calculates the time it will take to
fill that hole and it is a long, long time. *And the stranger had many
more tasks similar to that one to do and Milo could help him out. *Milo
had met one of the many monsters in those mountains, an amiable person
but a time waster. *You remind me of that character.

>If you can't describe your problem, if you send people to web sites
without instructions on how to view your file, you aren't going to get
much help here when you waste people's time.

>Nobody here knows if you have linked the spreadsheet to your Access
database, if you know what an update query is, if you know what an
append query is, if you have a clue about Access or databases or
programming or logic or VBA, or if you even know what you are doing or
attempting to accomplish. *If you expect us to extract a nugget of
information from you in each post, playing Mindreading 101, and hoping
to get closer to your solution over many days of extraction, I expect
you'll be waiting a long time for any solution.- Hide quoted text -

>- Show quoted text -

Hi,

Simply put, the xl sheet that I sent is NOT LINKED TO ACCESS.
It is just an example, a snap shot of what I was trying to do in
access.

And what are you trying to do?
You have
abc * * * *ggg
def * * * *kkk

and that somehow becomes

ggg
ggg
kkk
ggg
NA(NOT AVAILABLE)

in column location. *No rhyme or reason. *Nothing to link the two tables
together.

tableMAIN sheet is actually the name of table in access.
userlistTABLE is actually the name of a table in access.

Result sheet is the required query in access.

Story was good.
I didn't know it was that hard to download a simple file from that
link.. silly.

Let people know you aren't selling web site designs. *Stop ads. *Etc.- Hide quoted text -

- Show quoted text -

I don't want anything in location. Not in column location. Why u keep
looking at location column?? It has nothing to do with location
column.

If I have a user name 'abc' in main table, in new query I want to
return ggg instead as per details because abc=ggg.
How do I make that query? Simple now?? common in both tables is abc
etc names.

What i have done is:

SELECT userlistTABLE.[USER2] AS [USER1], [tablemain].item,
[tablemain].date, [tablemain].amount, [tablemain].location
FROM tablemain LEFT JOIN userlist ON TABLEMAIN.[USER] = userlistTABLE.
[user1];

Having done that, now I want to get NA for a missing TABLEMAIN.USER
(from userlisttable where it looks at user1 and is getting user2 for a
match).

Hope its clear now.

[Salad]

Sinner wrote:

On Nov 20, 7:59 pm, Salad <[e-mail]:

>>Sinner wrote:

>>>On Nov 19, 10:43 pm, Salad <[e-mail]:

>>>>Sinner wrote:

>>>>>On Nov 19, 7:35 pm, Salad <[e-mail]:

>>>>>>Sinner wrote:

>>>>>>>On Nov 18, 9:05 pm, Salad <[e-mail]:

>>>>>>>>Sinner wrote:

>>>>>>>>>Hi,

>>>>>>>>>I have a field name 'USER' in tableMAIN.
>>>>>How do I replace the user names with corresponding user names. I can
>>>>>do that in xl using vlookup but now I'm trying to find a way to do
>>>>>that in access.
>>>>>For a user mismatch, it should give NA

>>>>>>>>>Thx.

>>>>>>>>There are some alternatives.

>>>>>>>>One way is to use Docmd.TransferSpreadsheet and then use Vlookup in Excel.

>>>>>>>>Or you open both Access and Excel and then cut and paste the values into
>>>>a table.

>>>>>>>>Or you could manually enter the data into the table.

>>>>>>>>Or you might try an Update query.

>>>>>>>>If you would supply some further background regarding your problem and
>>>>you might get a helpful answer.

>>>>>>>Hi,
>>>The tableMAIN is as follows:

>>>>>>>Fields are:
>>>item date amount USER location
>> abc
>> abc
>> def
>> abc
>> xyz

>>>>>>>UserlistTABLE is as follows:

>>>>>>>Fields are:

>>>>>>>user1 user2
>>>abc ggg
>>>def kkk

>>>>>>>The result should yield:

>>>>>>>item date amount USER location
>> ggg
>> ggg
>> kkk
>> ggg
>> NA(NOT AVAILABLE)

>>>>>>I really don't know how you'd do that. The userlist table has some user
>>ids and TableMain has no user ids...just some locations.

>>>>>>I did this as a test. I created table Z1. It had 1 field; UserName. I
>>had 2 records; A and C. I created table Z2. It had 2 fields; Username
>>and Location with 3 records A, B, and C. My query is this

>>>>>>UPDATE z2 LEFT JOIN z1 ON z2.UserName = z1.UserName SET z2.Location =
>>IIf(Not IsNull([Z1]![UserName]),[Z1]![UserName],"N/A");

>>>>>>The result was
> A A
> B N/A
> C C

>>>>>>Normally you'd have 2 tables. Start by selecting
>>Query/New/FindUnmatched. You'd now have a list of records that don't
>>exist between both tables. Once you have this, select from the menu
>>Query/Update. Now using the above IIF() statement as a template, change
>>it to suite your needs and removed the column with the ISNULL() criteria
>>row. Before you do this, because you are unfamiliar with this stuff,
>>make a backup of your tables before you run the query.

>>>>>>But I really see no way, with the example above you provided, to link
>>the two tables together.- Hide quoted text -

>>>>>>- Show quoted text -

>>>>>Hi,

>>>>>Thx for your reply.
>A sample of my requirement is at link below.
>http://www.savefile.com/files/1893950

>>>>You should tell people to skip the ad.

>>>>>I have worked around making a calculated field & then union will rest
>of the tables to achieve what I was looking for.
>Still I need to be able to get the 'N'A in case I don't match an IDor
>if its blank. Also I would like to see if required IDs already exist,
>it should skip it & check next & do the necessary thing.

>>>>>Thx.

>>>>I looked at your Excel spreadsheet. I haven't a clue as to what you are
attempting to do. Is this an IQ or aptitude test or something for Mensa
members to figure out?

>>>>There's an excellent children's book, also good reading for adults,
called "The Phantom Tollbooth". Milo, the hero of the story, is on an
errand to save Rhyme and Reason. He heads into the Mountains of
Ignorance and meets a stranger along the way asking for some help. The
stranger wants Milo to fill up a hole from a pile of sand a few feet
away. But he must use a tweezer to move the sand to a hole. After many
hours of putting a grain of sand inbetween the tweezer blades and
carrying the grain to the hole Milo calculates the time it will take to
fill that hole and it is a long, long time. And the stranger had many
more tasks similar to that one to do and Milo could help him out. Milo
had met one of the many monsters in those mountains, an amiable person
but a time waster. You remind me of that character.

>>>>If you can't describe your problem, if you send people to web sites
without instructions on how to view your file, you aren't going to get
much help here when you waste people's time.

>>>>Nobody here knows if you have linked the spreadsheet to your Access
database, if you know what an update query is, if you know what an
append query is, if you have a clue about Access or databases or
programming or logic or VBA, or if you even know what you are doing or
attempting to accomplish. If you expect us to extract a nugget of
information from you in each post, playing Mindreading 101, and hoping
to get closer to your solution over many days of extraction, I expect
you'll be waiting a long time for any solution.- Hide quoted text -

>>>>- Show quoted text -

>>>Hi,

>>>Simply put, the xl sheet that I sent is NOT LINKED TO ACCESS.
It is just an example, a snap shot of what I was trying to do in
access.

And what are you trying to do?
You have
abc ggg
def kkk

and that somehow becomes

ggg
ggg
kkk
ggg
NA(NOT AVAILABLE)

in column location. No rhyme or reason. Nothing to link the two tables
together.

>>>tableMAIN sheet is actually the name of table in access.
userlistTABLE is actually the name of a table in access.

>>>Result sheet is the required query in access.

>>>Story was good.
I didn't know it was that hard to download a simple file from that
link.. silly.

Let people know you aren't selling web site designs. Stop ads. Etc.- Hide quoted text -

- Show quoted text -

I don't want anything in location. Not in column location. Why u keep
looking at location column?? It has nothing to do with location
column.

In my email app your column data was f'd up, the usernames appeared
under location. All other columns were blank. Next time use a text
file and remove unnecessary crap from your posts. Anyway, I had
patience with you and the solutions are at the bottom.

>
If I have a user name 'abc' in main table, in new query I want to
return ggg instead as per details because abc=ggg.
How do I make that query? Simple now?? common in both tables is abc
etc names.

What i have done is:

SELECT userlistTABLE.[USER2] AS [USER1], [tablemain].item,
[tablemain].date, [tablemain].amount, [tablemain].location
FROM tablemain LEFT JOIN userlist ON TABLEMAIN.[USER] = userlistTABLE.
[user1];

Having done that, now I want to get NA for a missing TABLEMAIN.USER
(from userlisttable where it looks at user1 and is getting user2 for a
match).

Hope its clear now.

This example does not update the data, it simply displays the current
user and the new/real user. Copy/paste into a query to test.
SELECT tblMain.User, IIf(Not IsNull([user1]),[User2],"N/A") AS RealUser
FROM tblMain LEFT JOIN UserList ON tblMain.User = UserList.User1;

This example updates TableMain with the new values. Make a backup of
TableMain first before running it.
UPDATE tblMain LEFT JOIN UserList ON tblMain.User = UserList.User1 SET
tblMain.User = IIf(Not IsNull([user1]),[User2],"N/A");