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]

OK, first off, you should avoid using spaces in Object names (names of Table, Queries, Forms, etc) and Fields. It is good programming practice not to use spaces. I use underscores instead.
So I changed the name the "Material Part Number" fields to "Material_Part_Number".

Then, in using the method Allen Browne shows at the end of Example 2 here: http://allenbrowne.com/casu-07.html, I came up with this formula as the Control Source of my Text field, and it worked for me:

=DLookUp("[Price]","Cost","Material_Part_Number='" & [Material_Part_Number] & "'")

 

[posfog]

I am having to start again as just done something and hole thing crashed and lost everything (this may be a good thing only time will tell)

 

[posfog]

Hi All,
I managed to get it working so it displays the Part Name and Part Price within the form !!!!
But these two details are not then copied across to the database

Is there anyway round that?

 

[Joe4]

But these two details are not then copied across to the database

Do you mean back to the underlying table? No, that doesn't do that. It just looks up the value, which may be all that is really required.

Typically, in most cases, you would NOT stored calculated values at the table rule. The general rule of thumb is to not store any value which can easily be calculated. Doing so may undermine data integrity and make your database less dynamic. If a value can easily be calculated, usually there is no need to store it (it is like in Excel if you have the value of C1 equal A1 + B1, there is no need to "hard-code" the value when you can just leave the formula). If you wanted to do a query on the table, you would just join the two tables together in a query on the common field, and return the fields you want from each table.

The exception to storing values at the table level is if it is needed for historical reasons, i.e. the price can change, so you need to capture it at that particular point in time. So for something like that, instead of using the DLOOKUP formula in the Control Source of a Text field, you would use VBA that after a value is entered, it looks up the value and writes it to the table.

 

[stumac]

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

You wouldn't save the Description(s) - if you need to view these then you would use a query to link back to the data table - that's normalisation - if there is a link it is unusual to require duplication.

However with the price you are right, this is often required as a price can update and you wouldn't want to update historical orders. The best way to do this is using VBA code on the after update event of your form. If you are planning lots of thinks like this then it would be worthwhile having a look/play around with the Northwind sample database.

I have added to my sample here in a much more basic format than the Northwind database.

 

[posfog]

Hi Stumac

Just done the same in my database but geting this error

Cannot join on Memo,OLE, or Hyperling Object (Data.[Material Part Number]=cost.[Material Part Number]).

Any ideas?

 

[stumac]

Looks like one or both of these fields: Data.[Material Part Number], cost.[Material Part Number] are one of these data types: Memo,OLE, or Hyperling

Change them both to short text and it should be ok.

 

[posfog]

Thanks Stumac!!

 

[posfog]

Looks like one or both of these fields: Data.[Material Part Number], cost.[Material Part Number] are one of these data types: Memo,OLE, or Hyperling

Change them both to short text and it should be ok.

Morning Stumac,

I have added another coloumn to my Cost table with some more details so was thinking all i would need to do is change the details in my form

Eg
Copy
=[Material Part Number].[column](2)

But change the (2) to (4) as the new details i want to include is in coulmn 4 but it dosnt seam to want to work?

Any Ideas?

Regards

 

[stumac]

You need to change the row source of your combo box to include the new field. Also bear in mind that it starts at 0, therefore if there is 4 fields in the row source then the last one would be 3.