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.

 

[KenSheridan via AccessMonster.com]

Unless rows in the Excel worksheets which relate to rows in your main table
can be deleted you should not store the data from the worksheets redundantly
in columns in the Access table. Simply join the three tables on the ID
columns and return the relevant columns from the worksheets. That way the
values returned will always reflect the current values in the worksheets.

If on the other hand rows can be deleted from the worksheets without the
corresponding rows in the Access table being deleted, and you wish to retain
the last data from the worksheets in the Access table, then you will need the
columns in the Access table. You should again join the tables in a query,
but this time make it an 'update' query to update the relevant columns in the
main Access table with the values of the corresponding columns in the Excel
worksheets. You may need to use two separate 'update' queries to do this,
joining the main Access table to one of the linked worksheets in each, if
rows can be deleted independently from each worksheet.

Ken Sheridan
Stafford, England

 

[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