DLookup for update query (1 Viewer)

[Vivirtruvian]

Hi all,

This is probably a super simple question, but DLookup and I never seem to get along.

I have an update query that I wish to run that will use the DLookUp function to update an item description based on a material number. I have three tables in play here:

1. tblImportTemp: a temp table that receives the .csv data
2. tblProtein: the main table with the information to be updated
3. tblProductList: a product table that holds all the data for each material (eg. material numbers, descriptions)

What I wish to do is use a DLookUp that will take the field in the temp table ([tblImportTemp].[F2]) which is the material number, look this number up in the product table and output the description of the product to the main table.

I have started with the following which yields no results:

DLookUp("description","tblProductList","[materialnumber]= [F2]") <- I knew that wasn't the correct way to order the argument hence the next comment!

I've tried a heap of variations to the above using & and ' characters but I am really throwing spaghetti at a wall to see what sticks to be honest.

For more info, tblProductList.materialnumber is a Short Text and tblImportTemp.F2 is a Number.

Thanks in advance to anyone who looks into this.

 

[theDBguy]

Hi. You need to concatenate the value of F2 in your criteria expression, but you shouldn't need to use DLookup() if you have the material number in the main table.

 

[Vivirtruvian]

Hi theDBguy,

So you mean DLookUp("description","tblProductList","[materialnumber]=" & [tblImportTemp]![F2])?

I don't think I explained the function of the process very well in the OP. The update query is to overwrite existing information in the main table where a material number will change from say A to B, and with it the description must be updated to match.

To break it down further, this:

[U]ItemID                   materialNumber                 description[/U]
123456                       0000001                            MAT0001
123457                       0000001                            MAT0001
123458                       0000001                            MAT0001

Would become this:

[U]ItemID                   materialNumber                 description[/U]
123456                       0000022                            MAT0022
123457                       0000022                            MAT0022
123458                       0000022                            MAT0022

The update query is there to identify each ItemID to be updated, update the materialNumber, and with it insert a new description that I though should be looked up from the tblProductList. Since the csv file informs the database of the new material number and not much else, I thought DLookUp would be the simplest solution.

 

[theDBguy]

Hi. What I was saying was if the material number is already in the main table, then there's no need to also store the description in the same table. That's considered redundant data and against normalization rules. If you don't store the description, then you won't need to update it when the material number changes. To get the description for the material number, you can just use a query.

 

[Vivirtruvian]

Hi tbeDBguy,

Thanks again for your response. If I understand what you are saying, I should just have a material number for each line item stored in the main table, and use this to call on the item description for the likes of reports and subqueries etc. While I have dabbled with this in an offline copy of our database, there are some distinctly key instances where having the data drawing from the main table alongside the rest of the data performs better, and to that end I would like to stick with this. The point of the product list table is to have a quick reference table to enable new stock items to be both set up and entered into the main table as efficiently as possible.

Back to the original question, I find it odd that I cannot get a DLookUp to source the correct information for this field. More frustrating though is that I actually saw it have the correct information in datasheet view and without changing anything it simply stopped working!

 

[theDBguy]

Hi tbeDBguy,

Thanks again for your response. If I understand what you are saying, I should just have a material number for each line item stored in the main table, and use this to call on the item description for the likes of reports and subqueries etc. While I have dabbled with this in an offline copy of our database, there are some distinctly key instances where having the data drawing from the main table alongside the rest of the data performs better, and to that end I would like to stick with this. The point of the product list table is to have a quick reference table to enable new stock items to be both set up and entered into the main table as efficiently as possible.

Back to the original question, I find it odd that I cannot get a DLookUp to source the correct information for this field. More frustrating though is that I actually saw it have the correct information in datasheet view and without changing anything it simply stopped working!

Hi. I understand. It's your project, and you're free to proceed however you feel comfortable. I was just doing my duty to at least inform you of any possibilities. Okay, to help you with your UPDATE query, can you post the SQL statement you're trying to use. Normally, if you want to use a DLookup(), it might look something like this:

UPDATE TableName SET MatlNumber=NewValue, Desc=DLookup("Desc","LookupTable","MatlNumber=NewValue")
WHERE MatlNumber=OldValue

However, using DLookup() could make the query to run slow, which seems to be a concern for you. If that's the case, it might be better to use a JOIN. For example:

UPDATE TableName
INNER JOIN LookupTable
ON TableName.MatlNumber=LookupTable.MatlNumber
SET TableName.MatlNumber=NewValue, TableName.Desc = LookupTable.Desc
WHERE TableName.MatlNumber=OldValue

Hope it helps...

 

[Vivirtruvian]

Hi,

Sorry I have not responded, I have been on the road for work.

Appreciate you taking the time to help out. Your comments about some redundant data storage has got me thinking and I may yet implement some changes going forward. Since this is all SharePoint data, the less I put into and request from the cloud the better!

I had a re-think about this and came up with an adhoc solution where I manipulate the temp table to have all the information I want first then update the main database from there. There were some other pieces of information in the .csv that needed to be cleaned up along the way anyway.

End of the day this all works out faster than the manual data entry the admin would have to do!

Thanks again

 

[theDBguy]

Hi. Glad to hear you found a working solution. Good luck with your project.