Look up data from another table

[posfog]

Hi all,
I am fairly new to Access and need some help or assistance in creating what would be a Vlookup if done in excel.

What i have so far is two tables within the same access database

Table one name Data
Table two name Costs

When someone selects a certain Material Part Number i want the lookup to look at this column in the "Data" table called and find that same number in the "Cost" table and to display the price which is also shown next to the Material Part Number within the Cost table in another cell within the same Data table

I think once i may have it explained i should be ok to work on this for other look ups that i need to do within the same database.

Hope somone can help and point me in the right direction.

Regards

 

[Joe4]

A few more questions.
- What Data Type is this Part Number field? Is it Text or Numeric?
- Is this for an Entry Form, or a form to review Existing Data (or both)?

 

[welshgasman]

AFAIAA you only need [] if the names have spaces in them.?

This below works fine on my form

=DSum("Amount","Emails","CMS = " & [CMS] & " AND ID <=" & [ID])

Try

=DLookUp("Price","Cost","[Material Part Number]=" & [Material Part Number])

This assumes that the Material Part Number is numeric?

if text then

=DLookUp("Price","Cost","[Material Part Number]='" & [Material Part Number] & "'")

HTH

 

[posfog]

none of those worked then Welshgasman

Sorry for sounding dumb but if i did this in excel the formula would be

=VLOOKUP(A19,Cost!B:C,2,FALSE)

Look at A19 (which would be my Material Part Number on my form)

Look at cost table for material part number and bring back the price


All i got in at moment is a #ERROR

Even looked for a template online that has a working dlookup built in to see if i can understand it

 

[Joe4]

The devil is always in the details. Please answer the questions I asked in my previous post.

A few more questions.
- What Data Type is this Part Number field? Is it Text or Numeric?
- Is this for an Entry Form, or a form to review Existing Data (or both)?

These details matter, and affect how you need to write the formula (or if you should be using a query instead of a DLOOKUP).

 

[stumac]

In this kind of scenario I would consider changing your text box to a combo box for the control[Material Part Number], with the Record Source of the Combo being:

Select [Material Part Number], [Price] From Cost

Then set the Price text box to: =[Material Part Number].[column](1) (combo starts at column 0) - the benefits of this are that it will normally run faster than Dlookup, you can change the query of the combo to return more fields which can then be used without an additional dlookup overhead and limit the parts that can be selected and also improve your data quality.

 

[posfog]

I bet there isnt a way i could up load this somewhere so you can all have a look at it??

 

[posfog]

The devil is always in the details. Please answer the questions I asked in my previous post.

These details matter, and affect how you need to write the formula (or if you should be using a query instead of a DLOOKUP).

Hi Joe4

Part number field is a Alphanumeric

EG
Material Part Number
8W936568AA
9W839J447CD
DPLA12B637CE


And i am trying to get this to work on the form section where people will be entering the data

Hope this helps

 

[stumac]

Here is a sample showing both the lookup and combo methods.

If you are likely to have more than one Dlookup on the form from the one table I would definitely recommend using the combo box.

 

[posfog]

Here is a sample showing both the lookup and combo methods.

If you are likely to have more than one Dlookup on the form from the one table I would definitely recommend using the combo box.

Thanks Stumac - i will go away now and look in detail on how this example works and let you all know how i get on

 

[posfog]

Here is a sample showing both the lookup and combo methods.

If you are likely to have more than one Dlookup on the form from the one table I would definitely recommend using the combo box.

How would you get the part name description and part price then to appear in your data table?