110SidedHexagon 110SidedHexagon - 1 year ago 127
SQL Question

How to use DLookup In an Update Query

I have two tables: "PartNumberX" and "MasterPartTable". My end goal is to extract certain fields out of the "MasterPartTable" using queries to get a list of unique part numbers ("MasterPartTable" has duplicates). I was able to successfully get all the fields out that I wanted except for one using the

method. The last column, "Part Name" unfortunately is sloppily created (by someone else) and some part names have a comma where an identical part number may not eg. "Assembly, The Thing" and "Assembly The Thing" both have the same part number so I only want one of them. My idea was that I would be able to insert the good data into a table and then run an update query that uses a
to look up the part name using the part number in "PartNumberX" to look up the corresponding part name from "MasterPartTable" but I can't seem to get it to work.

When I use the update query below, it returns the same number of blank fields as there are in "PartNumberX".

UPDATE PartNumberX
SET PartNumberX.[Part Name] = DLookUp("[MasterPartTable].[Part_Name]","MasterPartTable","MasterPartTable.Part_Number = PartNumberX.Part_Number");

Am I missing something here or is there a different way to do this? I know that I could use VBA and scroll through each field individually to do this but I would prefer a much faster query.


Using the second method from HansUp I got the following:

UPDATE MasterpartsTable INNER JOIN PartNumberX ON MasterpartsTable.Part_Number=


SET PartNumberX.[Part Name] = [MasterpartsTable].[Part_Name];

Which results in an empty query.


I actually just ran the darn thing and it worked perfectly! I don't understand why the preview comes up empty, but regardless it works.

Answer Source

The DLookup criteria option ...

"MasterPartTable.Part_Number = PartNumberX.Part_Number"

... references the name of a field (PartNumberX.Part_Number). You need the field's value there instead of its name.

Use a SELECT query to work out the logic and syntax. After you have that returning the correct data, you can adapt as an UPDATE query.

If Part_Number is a numeric datatype ...

    [Part Name],
        "Part_Number = " & Part_Number
    ) AS master_part_name
FROM PartNumberX;

If Part_Number is text datatype, include quotes ...

"Part_Number = '" & Part_Number & "'"

However, I suspect you could avoid DLookup by joining those two tables. If this SELECT query aligns the data correctly, convert it to an UPDATE:

    p.[Part Name],
    PartNumberX AS p
    INNER JOIN MasterPartTable AS m
    ON p.Part_Number = m.Part_Number;

An UPDATE based on INNER JOIN could be noticeably faster than the DLookup version, especially if the join field (Part_Number) is indexed.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download