Trying to create query to do a lookup from another database.

[soupi]

Hi Guys, I am trying to do a lookup from a field from one of my databases (DB1) onto another field in (DB2).... I have been using excel to do vlookups but a report that I do weekly I do atleast 7 lookups for the past weeks... So I am trying to find a way in access to do lookups and save those lookups each time i run the report...


Do I have to create the query and make the ID the primary key on both the databases and then create a relationship?

any help would be appreciated.
thank you....

[JoeM]

Note that you can link tables from one Access database to another Access database.
So perhaps linking those tables, and then using them in queries will allow you to get what you need.

[soupi]

Thank you for your reply. How would I do that? I'm fairly new to queries in access.

[JoeM]

Go to the External Data menu
On the Import ribbon, double-click Access
Browse to the Access database where the other table resides
Select the "Link to data source by creating a linked table." radio button.
Click OK.
Select the table you wish to link and click OK.

The table should now be linked and available for you to use in your database.

[soupi]

how will the table know what my primary key is ?

Go to the External Data menu
On the Import ribbon, double-click Access
Browse to the Access database where the other table resides
Select the "Link to data source by creating a linked table." radio button.
Click OK.
Select the table you wish to link and click OK.

The table should now be linked and available for you to use in your database.

[June7]

Establishing a link to an external source does not require knowing primary key. A primary key is not even required to be present. Links can be established with Access tables, Excel spreadsheets, CSV text files, Outlook folders, other databases such as SQL or Oracle. The link is to the external object, not a specific field.

Use the link much like a table. Include it in queries just like tables or queries are used.

[soupi]

Select the "Link to data source by creating a linked table." radio button.
Click OK.
Select the table you wish to link and click OK.

The table should now be linked and available for you to use in your database.

Where would I see the link to data source button?

The table "lookup" row "vbuild status" is what I want to lookup off of and into "ISBD" Table

[June7]

Did you follow JoeM's instructions to open the Import/Link wizard?

A link to external source will show an small arrow at the left side of the table icon.

There are no linked tables in that image. Did you do import?

What is the data structure of ISBD table? What is the common field in both tables that can be used to join them in a query?

[soupi]

This first table "SHEET1" is the file that I want to fill in from the
lookup.. The ST and AFE coulmn would be my primary key.. I want to fill in the
coulmn vBuild Status 3-4-18











Now the 2nd table is the table that contains the lookup, "vbuild Status
3-4-14 which i want to use as a lookup table.

[JoeM]

So, wouldn't you just build a query, joining your two table on the "ST and AFE" fields, and return the field value you want?

[soupi]

So, wouldn't you just build a query, joining your two table on the "ST and AFE" fields, and return the field value you want?

Yes JoeM I did that but after running it, the lookup went throught and worked BUT I noticed that the query has 16000 records while the table ISBD has 28000 records... Why is the query missing all those records? When i did the lookup on the bottom is it the correct way?

[JoeM]

The default join type, an INNER JOIN, only returns matching records.
Try changing your join to a LEFT JOIN, which will include ALL records from your ISBD table.
You can do that by double-clicking on the line joining the two tables, and selecting the appropriate option (probably the second radio button, it will be evident when you read the description).

[soupi]

Thank you very much JoeM that worked!
Now I am trying to do another lookup from a different table (WC 1-7) in the same query... but the fields I am looking up off of in (WC 1-7) is budget code, status code and WC.. I made sure the ISBD (Main FILE that needs the lookup) primary key is pointing to both of the tables ST and AFE.... now when i run it it gives me a same table 'wc1-7' error (bottem right) do you know why its doing that and the way to get around it so I can do lookups off both tables.. or do i have to do it one at a time??

thank you Joe

[JoeM]

Why is it a Make Table Query instead of just a Select Query?
Since you can get what you need via the Query, there shouldn't be any need to write the results back to the table itself.

[soupi]

thank you for your assitance joe, it worked...

if i have any more questions I will post them here
have a great weekend.