Table Looks Up

[VThokie]

I'm using Access 2007 and Window's XP.



I have a table that is the main table i am working with called FAILURES. It has a bunch of fields which our already filled in. But i have four fields in FAILURES that need to be filled in based on other fields in the table as well as using a feature similar to vlookup from a table called AssetCode.

For example in failures i have a field called AssetDescription which needs to filled in. It is based on the AssetItemCode which is already filled in in failures. To get the AssetDescription i must look in the AssetCodes table, find the correct code in the AssetItemCode column of AssetCode. Then I want to take the corresponding AssetDescription and place it in AssetDescription of failures.

I've tried DLookUp but I can only get it so it gives me a dropdown box with all the possiblities.

Any help would be great.

Thanks,
Andy

[June7]

This smacks of duplication of data. What you should do is build query that joins the tables on the key AssetItemCode, then the AssetDescription field will be available.

[VThokie]

I tried to create a query by joining the tables but I can't make AssetItemCode my key. I think it's because the values for AssetItemCode aren't unique.

[June7]

AssetItemCode has to be unique somewhere. There couldn't be more than one description for each AssetItemCode. You need to show diagram of your data structure because this is not making sense.

[VThokie]

Table Failure has AssetItemCode and FailureCode along with other fields which are irrelevant atm.

Table AssetCode has AssetItemCode, FailureCode, Descripition and AssetItemName.

AssetCode is a constant table for all projects we do. Failure is a set a data we get from the field ever couple of days.

I need to find Description and AssetItemName from AssetCode that match the AssetItemCode and FailureCode from Failure and have these fields combine with the rest of the fields in Failure. The AssetItemCode is not unique in either table. But the FailureCode is unique in AssetCode, but not in Failure.

Hopefully this help clarify my question. As far my data structure I don't think I currently have one, but I should be able to post sample of my tables once I double check with my boss.

Thanks in advance,
Andy

[NTC]

The correct approach depends on whether you want this set up to be done when the human is initially inputing data. Or whether you are going to get/find this data after-the-fact as a 1 time effort. From your post it sounds like the 2nd...but it is not totally clear.

You indicate that the new/needed data is dependent upon values existing. this implies that a correct join should work. but again that isn't 100% certain depending on how carefully you've chosen your words. if there is a 1:1 relationship between the new/needed data and the existing data - - then the db needs to know how to establish that with a common cross reference value.

Not sure this helps, maybe....

[VThokie]

then the db needs to know how to establish that with a common cross reference value.

I have a 1:1 relationship between existing and needed. How do i establish the common cross reference value?

[June7]

If these are truly 1:1 then each table should have a common unique ID field on which the tables can be joined in a query and every record in each table can have only one corresponding record in the other table. If so then this could possibly be one table. How many fields? Do all fields have data for all records in both tables? Will there always be related records in both tables?