Help with lookup

[keri]

Hi everybody,

I'm very new to access so please treat me with kid gloves! (i'm
hopeless with code and macros but enjoying learning). I am loving what
it is capable of though, and i'm sure I haven't scratched the surface.
I have a beginners book & have read many posts, and help files but i am
still stuck.

I am looking to use the equivalent of a Vlookup function from excel,
however I am sure there will be a better way to do this.

I have 2 tables [customers] and [petals].
[Customers] contains fields called [postcode] and [petal number]
[Petals] also contains fields called [postcode] and [petal number]

In the [petals] table each different postcode entered has a
corresponding petal number (1 - 6). So numerous different postcodes
apply to each petal no.

When the postcode is entered in the [customers] table I would lke the
[petal number] field to automatically lookup the value from the
[petals] table for the corresponding postcode.

I hope i have explained this well enough for everone to understand. If
I could just be pointed n the right direction that would be fantastic.
( know in this example it appears like I am repeating data in two
tables but it makes sense (to me at least).

Thanks in advance for any help you can give.

[keri]

I have also just read about a function called autolookup - perhaps this
is what need to use. However following the instructions to create the
autolookup query doesn't work.

[salad]

keri wrote:

Hi everybody,

I'm very new to access so please treat me with kid gloves! (i'm
hopeless with code and macros but enjoying learning). I am loving what
it is capable of though, and i'm sure I haven't scratched the surface.
I have a beginners book & have read many posts, and help files but i am
still stuck.

I am looking to use the equivalent of a Vlookup function from excel,
however I am sure there will be a better way to do this.

I have 2 tables [customers] and [petals].
[Customers] contains fields called [postcode] and [petal number]
[Petals] also contains fields called [postcode] and [petal number]

In the [petals] table each different postcode entered has a
corresponding petal number (1 - 6). So numerous different postcodes
apply to each petal no.

When the postcode is entered in the [customers] table I would lke the
[petal number] field to automatically lookup the value from the
[petals] table for the corresponding postcode.

I hope i have explained this well enough for everone to understand. If
I could just be pointed n the right direction that would be fantastic.
( know in this example it appears like I am repeating data in two
tables but it makes sense (to me at least).

Thanks in advance for any help you can give.

I'm not sure exactly what you are asking for. In customers you have a
zip code field. When you are on a customer record, you want to see
related records in the petal table. There is one record in customer and
there are 0 to Many (6) in the petal table. Is this correct?

You could create a form/subform. First, create a form for customers.
Make it a Single Form; only 1 customer for the form.

Next, create a form containing the data from Petals you want to display.
This could be a continuous form or a data sheet or a single record
form, your choice.

Now open the main form for customer in design mode. On the toolbar
(hammer/pick icon) select the subform control and place it on the form.
Link the two forms via the ZipCode field (you can see the link in the
property sheet for the Petals subform).

Now save and run.

[keri]

Sorry, I knew I had explained this very badly. I'll try again.

Basically every post code will be assigned a "petal number" (a category
number from 1-6). This will be done via a form. Imagine post code AB1
was assigned petal number 2 and NN4 was assigned petal number 5.
When I enter data (through a form) into the customer table, if I enter
"AB1" into the post code field I want the "petal number" field to
automatically show 2 or if i entered NN4 into the post code field the
"petal number" would show 5.

I have tried numerous queries and relationships but i really don't know
what I am doing. I hope this explanation is clearer.

Thanks.

[salad]

keri wrote:

Sorry, I knew I had explained this very badly. I'll try again.

Basically every post code will be assigned a "petal number" (a category
number from 1-6). This will be done via a form. Imagine post code AB1
was assigned petal number 2 and NN4 was assigned petal number 5.
When I enter data (through a form) into the customer table, if I enter
"AB1" into the post code field I want the "petal number" field to
automatically show 2 or if i entered NN4 into the post code field the
"petal number" would show 5.

I have tried numerous queries and relationships but i really don't know
what I am doing. I hope this explanation is clearer.

Thanks.

2 methods I'll relate. I'll assume zipcode is text, not numeric, so
I'll surround in single quotes. Assume your formname is called
FormName, the Petal field is called Petal.

=Dlookup("PetalNum","PetalTable","ZipCode = '" & Me.ZipCode & "'")

In the Petals control source you'd stick that Dlookup. It won't be
editable. In the ZipCode AfterUpdate event enter
Me.Petal.Requery
You requery in case an update is made. This will return ONE value.

If you have multiple values, you might create a combobox/listbox with
the Rowsource being something like
Select PetalNum From PetalTable WHere ZipCode = '" & _
Forms!FormName!ZipCode & "'"

In this case, this will select all petals with the zip code for the
customer you are looking at.

In the AfterUpdate event of the zipcode enter
Me.ComboBoxNameOrListBoxName.Requery

If this doesn't help, respond back.