automatically look up and fill field


C

Connie

How do I get my data base to automatically fill in a
field based on information in another field. I can do
this in Excel using a vlookup. Can I do something like a
vlookup in Access?
 
Ad

Advertisements

F

fredg

How do I get my data base to automatically fill in a
field based on information in another field. I can do
this in Excel using a vlookup. Can I do something like a
vlookup in Access?
Look up DLookUp() in VBA Help.
 
D

DDM

Connie, since you posted your question to the "Getting Started" forum, I'm
going to assume that your question is really as basic as it seems and I'll
try everyone's patience by giving a very long reply.
Fred's answer is correct so far as it goes, but you want to be sure that a
lookup is what you really want. A lookup is useful in Excel because an Excel
table is a "flat" file.
What might be a better solution is to enter your data into separate tables,
join the tables, then create queries on those joined tables to display the
data in the combinations you require. For example, say you have an EMPLOYEES
table and a DEPARTMENTS table. Each department has an ID code as part of its
record in the DEPARTMENTS table. Each employee is assigned to a department,
so a department ID code is part of that employee's record in the EMPLOYEES
table.
Say you want to capture and display complete information for a given
employee, to include that employee's department ID and the name of the
department that goes with it. In Excel, you would use a VLOOKUP, as you
mentioned. In Access, you take a completely different approach. You join the
tables and then create a query that pulls in all the fields from the
EMPLOYEES table, plus the department name field from the DEPARTMENTS table.
This will create a virtual table that will show all the data as if you had
entered it into a single table. That yields the same result a VLOOKUP would,
if there were such a thing in Access. And that, I think, is what you are
really looking for.
For more on setting relationships, see Access MVP Allen Browne's example at
http://members.iinet.net.au/~allenbrowne/casu-06.html. I'm sure others can
suggest other examples, too.

DDM
"DDM's Microsoft Office Tips and Tricks"
http://ddmara.tripod.com
 
G

Guest

Thank you - I wasn't quite sure where to post so I
started here. I don't have much VBA experience so I was
hoping for a simpler answer. Here is my question with
more detail: I have one field in my table for an item
name. I would like another field in the same table to
automatically fill in the SKU number when I enter the
item name. I have already set up a lookup field which
pulls the SKU numbers from another table into a pull down
list, but this still requires the operator to choose the
SKU number (increasing errors) so I would like this to be
automatic so the correct SKU number is automatically
entered in the table when the item name is entered in the
table. Is there a simple way to so this?
 
D

DDM

Connie, based on the way you describe the problem, here is what I think you
should do:
1-Create two tables. One (TABLE A) will contain an Item Name field and an
SKU field. The item names must be unique. The other table (TABLE B) will be
the one you describe in your post, containing the Item Name field plus
whatever other fields you require (but NO SKU field).
2-Create a one-to-many relationship between these two tables, using the Item
Name field as the common field, with TABLE A on the "one" side of the
relationship. For more info, search "Work with Relationships" in Access
help. (BTW - Note that we would normally put the SKU field, rather than the
Item Name field, in both tables and use that as the common field, but I'm
basing this on the way you describe things in your post.)
3-Create a query using both tables. Include all the fields from TABLE B and
only the SKU field from TABLE A.
4-Run the query to get a "virtual" table that shows item name plus
corresponding SKU. You should be able to update the data here, and whenever
you enter an item name, the SKU will automatically appear.
5-Save this query and use it (instead of TABLE B) to update your data,
create data-entry forms, and run reports.
That should get you the result you are looking for without literally
duplicating the data from one table in another, which, as you say, leaves
open the possibility of operator error and makes updating the data (changing
an SKU number, for example) more difficult.

HTH

DDM
"DDM's Microsoft Office Tips and Tricks"
http://ddmara.tripod.com
 
Ad

Advertisements

C

Connie

It worked! Thanks so much for your help!!
-----Original Message-----
Connie, based on the way you describe the problem, here is what I think you
should do:
1-Create two tables. One (TABLE A) will contain an Item Name field and an
SKU field. The item names must be unique. The other table (TABLE B) will be
the one you describe in your post, containing the Item Name field plus
whatever other fields you require (but NO SKU field).
2-Create a one-to-many relationship between these two tables, using the Item
Name field as the common field, with TABLE A on the "one" side of the
relationship. For more info, search "Work with Relationships" in Access
help. (BTW - Note that we would normally put the SKU field, rather than the
Item Name field, in both tables and use that as the common field, but I'm
basing this on the way you describe things in your post.)
3-Create a query using both tables. Include all the fields from TABLE B and
only the SKU field from TABLE A.
4-Run the query to get a "virtual" table that shows item name plus
corresponding SKU. You should be able to update the data here, and whenever
you enter an item name, the SKU will automatically appear.
5-Save this query and use it (instead of TABLE B) to update your data,
create data-entry forms, and run reports.
That should get you the result you are looking for without literally
duplicating the data from one table in another, which, as you say, leaves
open the possibility of operator error and makes updating the data (changing
an SKU number, for example) more difficult.

HTH

DDM
"DDM's Microsoft Office Tips and Tricks"
http://ddmara.tripod.com





.
 
Ad

Advertisements


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