DLookup in Queries, or Easiest Solution

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have looked a few post here and other websites, but am
truely new to VB, I did some kiddie VB programming about
10 years ago and haven't touched it since. Is it possible
to run Dlookup in queries?

Here is my problem. I have taken on the task of converting
an excel spreadsheet using multiple vlookup's and been
asked to convert it to Access! Fun joy! Anyways, I've
tried so many different ways to accomplish this without
trying to dip into VBA, because of my lack of knowledge.
If there is a way to do this via queries whether it may be
append, update, select, please let me know. I'd rather not
make this any more complicated then possible.

We have two tables, table called "Data", and table
called "Action Codes".

Within table "Data" remains most of the data however,
there are a few fields, focussing in on the "Step Number"
field, that needs to be populated. Both tables have
a "Action Code" field. Based off of this Action Code, and
for example, in [Data].[Action Code] the value is 724.
Looking at [Action Codes] table, we have the value 724,
and "Step Number" value is 3. In excel, you would run a
vlookup to get the information and I could populate
table "Data" Step Number with that value 3.

However, I am looking for the easiest solution, any help
or direction is most welcomed. Again, I am trying to do
this through a query, but am open to other solutions,
whether its a command button I need to do or whatever!
Thanks in advance again. Have a good day!
 
In the query builder window specify that you want to perform an update
query. Then in the window add both of your tables so that they are both
visible in the window "Data" on the left and "Action Codes" on the right.
From "Data" fin dthe field that holds the value you are searching (i.e.
724), clik on it and drag it over to the "Action Code" table in the windows
to the field there that would have the number 724. You should see a black
line connect the two field. Then on "Data" double click on the field that
is to be updated. This will add the field to your query. Since you
specified that this is an update query there should be a line that says
"Update To". Here add the reference to the field in "Action Code" that has
this value. [Action Code].[Step Number]

Run the query and "Data" should have all the Step Numbers if it found a
matching code (i.e. 724) in the 2 tables.

Kelvin
 
Wow, I guess I was looking way too far into this, or maybe
I just really misunderstood Update query as a whole.
Thank you very much for this, I was trying to exhaust all
of my resources before coming to any board. This has
worked, and now after 2 days of research I can continue on
with the databse. Again, thank you!

Eric Wade
-----Original Message-----
In the query builder window specify that you want to perform an update
query. Then in the window add both of your tables so that they are both
visible in the window "Data" on the left and "Action Codes" on the right.
From "Data" fin dthe field that holds the value you are searching (i.e.
724), clik on it and drag it over to the "Action Code" table in the windows
to the field there that would have the number 724. You should see a black
line connect the two field. Then on "Data" double click on the field that
is to be updated. This will add the field to your query. Since you
specified that this is an update query there should be a line that says
"Update To". Here add the reference to the field in "Action Code" that has
this value. [Action Code].[Step Number]

Run the query and "Data" should have all the Step Numbers if it found a
matching code (i.e. 724) in the 2 tables.

Kelvin

I have looked a few post here and other websites, but am
truely new to VB, I did some kiddie VB programming about
10 years ago and haven't touched it since. Is it possible
to run Dlookup in queries?

Here is my problem. I have taken on the task of converting
an excel spreadsheet using multiple vlookup's and been
asked to convert it to Access! Fun joy! Anyways, I've
tried so many different ways to accomplish this without
trying to dip into VBA, because of my lack of knowledge.
If there is a way to do this via queries whether it may be
append, update, select, please let me know. I'd rather not
make this any more complicated then possible.

We have two tables, table called "Data", and table
called "Action Codes".

Within table "Data" remains most of the data however,
there are a few fields, focussing in on the "Step Number"
field, that needs to be populated. Both tables have
a "Action Code" field. Based off of this Action Code, and
for example, in [Data].[Action Code] the value is 724.
Looking at [Action Codes] table, we have the value 724,
and "Step Number" value is 3. In excel, you would run a
vlookup to get the information and I could populate
table "Data" Step Number with that value 3.

However, I am looking for the easiest solution, any help
or direction is most welcomed. Again, I am trying to do
this through a query, but am open to other solutions,
whether its a command button I need to do or whatever!
Thanks in advance again. Have a good day!
Click to expand...


.
Click to expand...
 
Back
Top