Is there anything similar to Vlookup in Access? - Access 97

[snoopy92211]

I have a table, tbldata that lists, among other things,

name
companycode
address

I have another table, tblmastercompany that lists all the company codes and their corresponding companycode addresses.

What I want to be able to do is check tbldata against tblmastercompany and append the companycode address to tbldata.

For example
row 1 in tbldata has company code of 0001, name, Snoopy, and address is 800 Heaven St.

in tblmastercompany, the company code 0001 has a company address of 100 S. Fifth street.

I want to be able to
1. Check against mastercompany
2. append the corresponding company address information fields to tbldata for the particular record.
3. After it checks the entire table and appends, I want to let the user know if there are any unmatching records (i.e. any companycodes in tbl data that aren't in the mastercompany table) and gives them a prompt to add the new codes/address to the company table.

*phew* Normally, In excel I would do a V-Lookup to match Cmpany code information from both tables, but I don't have the slightest clue how to do it in MS Access.

Any help would be greatly appreciated!

Thanks,

Snoopy

 

[snoopy92211]

Well it appends and updates, it just doesn't update correctly.

This is what my table (with the update) looks like.

name Place locacode location address
Angel Kentucky D001 123 E. Main St

Jane Texas D012 123 E. Main St.


This is incorrct because each unique location code has a unique address.

 

[lespaul]

my bad, try this:

UPDATE ALL701 A 
INNER JOIN MPSLocations B ON A.LocaCode = B.LocaCode 
SET A.LocationAddress = B.[StreetAddress], A.LocationCity = B.[City], A.LocationState = B.[State], A.LocationZip = B.[Zip];

Leslie

 

[snoopy92211]

Ok. That does nothing. Prior to this step,

1. I imported the table (All701) into Access 97, and created/appended 4 location fields to the table.

2. when I open the table the appended location columns are blank.

2. I then run the update query. I open the table, and the location data hasn't been added to the fields. (It looks like step 2).

(This is using your suggestion. When I use my suggestion, the data populates, but not correctly.)

Thank you for helping me. This is driving me mad!!!

 

[PHV]

Does the following query return some rows ?
SELECT A.*, B.*
FROM ALL701 A INNER JOIN MPSLocations B ON A.Location_Code = B.LocationCode

In other words is the Location_Code field of the imported table properly populated ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[snoopy92211]

Ok...When I run this query, I basically get all the field names with no data.

 

[snoopy92211]

MAybe it would make sense if i could send you the database...if that would help please let me know.

 

[PHV]

all the field names with no data
Seems that MPSLocations don't have the info too ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[snoopy92211]

but the data is there!! *pulling hair in frustration* I'm just not understanding why the locationcodes aren't working.

 

[PHV]

And this ?
SELECT A.*, B.*
FROM ALL701 A INNER JOIN MPSLocations B ON Trim(A.Location_Code) = Trim(B.LocationCode)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[snoopy92211]

Ok!!! I FINALLY FIXED IT!! I had to add Trim.

Thanks so much.