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

 

[traingamer]

See Dlookup

traingamer

 

[PHV]

1)
UPDATE tbldata D INNER JOIN tblmastercompany M ON D.companycode=M.companycode
SET D.address=M.address
WHERE D.address<>M.address
2)
SELECT D.*
FROM tbldata D LEFT JOIN tblmastercompany M ON D.companycode=M.companycode
WHERE M.companycode Is Null

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

 

[lespaul]

PHV's solution 1 takes care of your problems 1 & 2.

Solution 2 shows the answer to problem 3


Leslie

 

[traingamer]

And my response answers a question found only in my imagination.


traingamer

 

[snoopy92211]

Thanks! But see I dont just want the company code to update in the master table, I want the user to see what codes didn't populate and add the corresponding companycode address information. See the data is still no good if the company code is added to tblmastercompany without the company address, etc.


Thanks traingamer, PHV, and leslie!

 

[pbrodsky]

Are you sure you really want company addresses in two different places? Unless you need the address in tbldata for historical reasons (ie. saving the address as it was at the time you created the record), you should just use the address in tblmastercompany when you need it.

 

[snoopy92211]

Yes, I need the companycode address appended to tbldata when I match the corresponding codes. I need the address for importing into another application.

 

[pbrodsky]

If you only need the CURRENT address, you would get it from tblmastercompany at the time you export the data. Keeping two copies violates normalization, and is prone to errors.

 

[snoopy92211]

I'm sorry maybe I'm not making myself clear.

There are 2 different addresses.

the address in tbldata is customer address
the address in tblmastercompany is companycode address.

once i check the companycode in tbldata to companycode ni mastercompany, i want the companyaddress (that is only in mastercompany) to append to the corrsponding record in tbldata

Therefore it wouldn't violate normalization. Please correct me if i'm wrong.

Thanks!

 

[pbrodsky]

The company address is in mastercompany (correct?). You DON'T copy it to tbldata; you match the records and include the company address only when you actually export the data, or am I missing something?

 

[snoopy92211]

I want the match the records and include the company address before I export the data. This is only a partial step... I have many more things to do to the table before I export it.

 

[pbrodsky]

You probably still don't need to copy the company address to a second table. What sort of things do you need to do with the data?

If you are imitating the way you would do it in Excel, there is probably a more straightforward of accomplishing it in Access.

Most likely if you posted some of the details, someone here could help make the entire task easier.

 

[snoopy92211]

Ok. Sorry for the confusion. Let's scrap the imitating in Excel. That was just my first thought of how I could accomplish this task. I just want get it done. Doesn't matter how.

Here's the deal.

1. I am importing a table, tbldata into the database. (this table has a lot of fields, but for these purposes the only field i'm interested in is the CompanyCode field.)
This table that I'm importing has different data every month)

2. The company code table, mastercompany, is the master listing of all the company codes and corresponding company code addresses.

3. I want to check the company code field from tbldata to the mastercompany table to check for missing locations

4. If there is a missing companycode (meaning that the mastercompany table is incomplete and the tbldata table has companycodes that aren't in the mastercompanytable), I want a box to prompt the user with the missing locations. The user will then be able to add the lcompanycode addresses to the mastercompany table.

5. When there aren't any missing locations, at that point I want to append the company codeaddress information (not the actual company code, because I already have that in tbldata - I just don't have the corresponding addresses) into tbl data.

Ex. tbldata has the following fields (Included I have an example of one record)

Name Suzie
CompanyCode 12345
BeginningDate 01012005
EndDate 02052005
Location Address 123 Main St
Location Zip 12345
Location State. FL

tblmastercompany has the following fields
Company Code 0001
Company Address 456 Town St.
Company Zip 09876
Company State MI

if 0001 is in the tbldata, then the code will append the following fields to tbl data: Company Address, Company zip, and Company State.

So Now, when I open tbldata, the first record will have this information:

Name Suzie
CompanyCode 12345
BeginningDate 01012005
EndDate 02052005
Location Address 123 Main St
Location Zip 12345
Location State. FL
Company Address 456 Town St.
Company Zip 09876
Company State MI


Or..if the Company Code 0001 is NOT in mastercompany, then some sort of input box will be available for this code (and every code in tbldata that isn't in the master table.)


Then, when (AND ONLY WHEN) the user inputs the company address information and updates the mastercompany table will the company address infromation be added to tbl data.

Ok Hopefully that makes sense.

Thanks!

 

[snoopy92211]

I'm sorry. I meant if 0001 is in the mastercompany table, then the code will append the following fields to tbl data: Company Address, Company zip, and Company State.

 

[willir]

snoopy92211

I have run into similar scenarios with contact info where one does not know which contact information is correct.

The way I have handled it is to have two subforms. One subform displays the contact info from one table, and other subform displays data for the other table. Say Contact1 and Contact2

I first decide which is the better "master" table. In this example, say Contact2

On the subforms and main form, I have several command buttons...
- Command button on the main form. Transfer all data from Contact1 to Contact2. All data = copy over missing data, or over-write the existing data. An UPDATE SQL statement works for this as provided by PHV
- Command buttons on the subform next to each line of the address, phone number, etc. Copies the specific address line from the one table to the other. For example, over-write the phone number but not the address.
- Missing an entire record. Another command button INSERTS the records from the one table to the other.

Then for the verification, the process works as follows...
- User chooses one source or the other to loop through. Note that although, one source will have been chosen to be the "master", there is still merit looping through Contact1 and then Contact2 to verify the records. Will call this the "Control" recordset.
- The code will loop through the Control recordset. When a mismatch is found, the record is displayed in the appropriate subform. An attempt is made to find the mathcing record in the other subform. If none is found, the subform is left blank.
- Any mismatched fields are highlight on the subform by using a different forecolour / background colour scheme to make the mismatch easy to see.

The end user then chooses the appropriate action, and moces on to the next mismatched pair, or missing record.

If necessary, repeat using the other table as the "Control" recordset.

There is work involved for this approach. After all, what seems easy to us "humans" can be difficult to program. For example...
123 Heaven St
123 Heaven St.
123 Heaven Street
123 Heaven Street,
123 Heaven Str
...etc

To a computer, a simple comparison will depict each of these variations as different. To the person deliverying the mail, they are all similar.

Richard

 

[snoopy92211]

Ok...So I've tried out this inner join thing. Makes sense, but I can't get my syntax to work. I have this in a module.

It says my update syntax is wrong. Basically, I want the location information from mpslocations to be added to the (rstimport.fields(3)) table.

What am I doing wrong?

db.Execute "Update rstImport.Fields(3).Value A Inner Join mpslocations B ON A.Location_Code=B.LocationCode" _
& "Set A.LocationAddress = B.StreetAddress" _
& "Set A.LocationCity = B.City" _
& "Set A.LocationState = B.State" _
& "Set A.LocationZip = B.Zip" _
& "where A.Location_Code <> B.LocationCode""," _
& "A.LocationAddress <> B.StreetAddress""," _
& "A.LocationCity <> B.City""," _
& "A.LocationState <> B.State""," _
& "A.LocationZip <> B.Zip"

 

[lespaul]

db.Execute "Update [b]rstImport.Fields(3).Value[/b] A Inner Join mpslocations B ON A.Location_Code=B.LocationCode" _
& "Set A.LocationAddress = B.StreetAddress" _
& "Set A.LocationCity = B.City" _
& "Set A.LocationState = B.State" _
& "Set A.LocationZip = B.Zip" _
& "where A.Location_Code <> B.LocationCode""," _
& "A.LocationAddress <> B.StreetAddress""," _
& "A.LocationCity <> B.City""," _
& "A.LocationState <> B.State""," _
& "A.LocationZip <> B.Zip"

the bold area above should be the table name, is your table named: rstImport.Fields(3).Value??



Leslie

 

[snoopy92211]

This is actually the table name in a recordset. But -- I managed to fix the code ( and my query works.

Here's the code:

UPDATE ALL701, MPSLocations SET ALL701.LocationAddress = [MPSLocations].[StreetAddress], ALL701.LocationCity = [MPSLocations].[City], ALL701.LocationState = [MPSLocations].[State], ALL701.LocationZip = [MPSLocations].[Zip]
WHERE (((MPSLocations.LocationCode)<>[All701].[Location_Code]));

However, (there's always a however), The location fields update, but not with the corresponding code address. For example - this is how this query should work. See the example below.

in my data table, I have these fields. (Before the update Query)

name Place locacode location address
Angel Kentucky D001 (blank)

Jane Texas D012 (blank)


In my location source table,

Location Code Location Address
D001 111 E. 34th
D012 419 E. 33nd
D111 1 W. Main St.

When I run the Update query, the data table should look like this:

name Place locacode location address
Angel Kentucky D001 111 E. 34th

Jane Texas D012 419 E. 33nd


The query is updating the location address with one static address, as opposed to choosing based on location code. Does this make sense?

 

[lespaul]

You're saying this doesn't work:

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

Leslie