New to VLOOKUP

E

eramirez

Registered User.
Local time
Today, 08:51
Joined
Dec 10, 2007
Messages
17
Hello.

I have 2 tables. Table A has PIN#, NAME, ADDRESS, ZIP, ETC. This is the table where I want to lookup values.

Table B has other information on new orders. It also has fields PIN#, NAME, ADDRESS, etc.

I created my form using Table B. What I need to know how to do is when someone types in a certain PIN# the NAME, ADDRESS, etc automatically populates based on the values of Table A.

I've never used VLOOKUP before but I am assuming this is what I use? Where do I put the syntax and how should it look?

Any help anyone can provide would be appreciated!

Thanks
Enrique Ramirez
 
VLOOKUP is in EXCEL, not Access. You want DLookup in Access.

Also, your structure is not normalized. You really do NOT need to store data in multiple tables. You just need the ID of the record in one table to store the ID of that record in table B. Then you use a query to pull the data back together whenever you need it.
 
huh?

sorry but I hardly use Access for data management. I know what normalized means in statistical terms but what does it mean in Access? I have one table that has all the demographic information on a client, which won't change, and another table that will hold all ordering information which will change. When I am entering data on the form, what I want is that when someone enters a pin #, the demographic information for that pin# automatically gets populated. How do I do this?

Thanks again for your help and sorry for being so slow on this.

Enrique
 
Okay, here's a sample normalized data structure:

Table - ClientInfo
ClientID - Autonumber (Primary Key PK)
ClientName - Text
ClientAddress - Text
ClientCity - Text
ClientRegion - Text
ClientPostCode - Text

TABLE - PIN#
PINID - Autonumber (PK)
ClientID - Long Integer (FK - foreign key)
PINNUmber - Text (or could be number if actual number is used)
PINName - Text
PINOtherInfo - Text


You can then have a PIN that will be able to add a client to using a combo or list box.

If you have multiple clients that could appear on the PIN then you would need to have an additional JUNCTION table for the many-to-many relationship that would be. It would look like this:

TABLE - ClientPIN
ClientPINID - Autonumber (PK)
ClientID - Long Integer (FK)
PINID - Long Integer (FK)

And that would be what ties them all together. To enter this you would normally have a form/subform set up with either the PIN or the Client table used for the main form (depending on your setup and how you want to view it) and then set up the junction table as a subform with two combo, or list, boxes to select each.
 
You really need to understand data normalisation to make Access work properly. Try this link and do a google search. Also read Access help on normalisation. If your data isn't normalised you will have so many unnecessary problems.
 
thanks

After reading what normalization is I think I have an understanding of what to do. So having a table of client demographic and another for client orders is not normalized? So say none of the variables are repeated. I still don't know how to automatically populate fields? Lets say everything else is fine, what is the exact process of setting it up?

Thanks
Enrique
 
eramirez said:
After reading what normalization is I think I have an understanding of what to do. So having a table of client demographic and another for client orders is not normalized?
Click to expand...
That's not correct. It SHOULD have a table for demographic data and another for orders. BUT the initial post you had was wanting to put redundant data into the second table (client name, address, etc) when all it needs is the client ID.
 
correct

that's right I was being redundant. All I want is that when I am entering information on a form when I enter a PIN # I want the address, name, etc to be automatically populated. That's it. Let's not even think about normalization. All I want to know is how to do it. I know if I don't think about it now I will have problems later but all I want to know is that process for now. I will read about normalization, apply it and then use the information you are providing.

In an ideal situation how would I automatically populate the fields?

Thanks again for being so patient.

Enrique
 
In an ideal world your data would be normalised. You ignore that at your peril. Normalisation isn't a bolt on goody you can add later - it is fundamental to a correct design.

If you base your form on a query using your linked tables the info should be updated automatically.
 
thanks

I understand that and I will read and apply all the rules of normalization before I go any further but all I want to know is how to set it up. In laymen's terms preferably.

Enrique
 
Build a query that links the data from your normalised tables. Then use that query as the datasource for your form.
 
thanks again

hi, I'm usually not this slow but I am still having trouble understanding.

Table 1 TABLE 2
PIN# PIN#
NAME TYPE
ADDDRESS QUANTITY
CITY MANUFACTURER

They are linked by PIN#

When I create a form I am using TABLE 2. What I want is when someone enters a PIN#, the NAME, ADDRESS, gets automatically populated. How do I get those fields onto my form when they are not in TABLE 2? Is this basic table structure correct? I am new to this and would like a detailed description if possible.

Enrique
 
got it...thanks!

thanks to everyone's help I finally think I got it! It took a minute.

Thanks again for your patience and help!

Enrique
 
Glad you figured it out! I knew you could. It makes more sence when we work it out on our own that if someone gives us the answer. This forums are a gold mine of information, but the more we learn to work it the more valuable the information is. So see this forum as a learning exerience. A few people in this site could have easily created a quick sample db and given it to you, but that would have spoiled the learning experience! :)

Take care!

René
 
Hello Adam,

I'm afraid not. Villarillo Gardens? Where is that? I live in Central California. Now you got me wondering where Villarillo Gardens is at.

René
 
It's a company of somekind. I worked for a manufacturing plant a long time ago, and their primary distributor was Villarillo.
 
You must log in or register to reply here.

Similar threads

euphonium01
SUBFORM CONTROLS
Replies
11
Views
463
Ken Sheridan
Ken Sheridan
Technics
Question on Comboboxes
Replies
17
Views
830
Mark_
M
M
Solved Selecting data from a drop down box in one field, enters a value in a 2nd field
Replies
11
Views
600
XPS35
XPS35
MajP
MVF Technical Discussion and Uses (If you plan to Use Them)
2 3 4
Replies
63
Views
7,031
MajP
MajP
A
How to Properly Query and Search Multi-Valued Fields in a Form?
2
Replies
20
Views
1,610
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom