Similar function needed to Excel VLOOKUP

[Kurani]

Hi everyone, I hope someone may be able to help.

I have a database with three tables. The main table is a portfolio of
projects with budget, date and other general information. There are a couple
of columns I need to update automatically by looking up information on the
other two tables which hold budget and date information.

The other two tables are linked to external excel sheets which allows the
tables to auto update when excel is updated.

How do I get the budget and date columns in the main table to lookup data
from the other two tables and return the up to date figures and dates?

I am not using forms, the database is just tables only.

Each project has its own code/id that appears in both the main table and
also the other two budget/date tables.

Thanks for any help you can give

 

[HouBMan]

Hi everyone, I hope someone may be able to help.

I have a database with three tables. The main table is a portfolio of
projects with budget, date and other general information. There are a couple
of columns I need to update automatically by looking up information on the
other two tables which hold budget and date information.

The other two tables are linked to external excel sheets which allows the
tables to auto update when excel is updated.

How do I get the budget and date columns in the main table to lookup data
from the other two tables and return the up to date figures and dates?

I am not using forms, the database is just tables only.

Each project has its own code/id that appears in both the main table and
also the other two budget/date tables.

Thanks for any help you can give

So if I understand you correctly, you want to update the data in the
main table right?

If so you should be able to do this using an update query like so:
*Create a query using the main table and include the other table that
has the data you'll use to update the main table
*Join them on the ID fields
*Change the query type to an update query
*Add the column of data from the main table that should be updated
*In the update to row of the query, type [ExcelTable1]![Field];
subsitute the name of your table and the field name that has the new
information from the linked excel table
*Click the red exclamation point to run the query.

The query should update the value(s) in the main table to the values
of the associated values from the linked excel table.

BTW - there is no VLOOKUP fcn in access; that's because every time you
join one table to another in a relational DB and run a query you're
technically doing a VLOOKUP; you're just not getting any of those #N/A
answers back.

 

[John W. Vinson]

I am not using forms, the database is just tables only.

That's a bit like saying you're using Excel and you're not using any
expressions in cells, just data.

Access is NOT "Excel on steroids" - it's a different program, with a different
way of thinking!

You need - at the very least - a Query joining your tables. The next (probably
essential) step is to create a Form or Forms with which to interact with the
data. A table may LOOK like a spreadsheet, but it doesn't work like one, and
isn't designed for user interaction.

See:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials