110SidedHexagon 110SidedHexagon - 2 months ago 13
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

SELECT DISTINCT
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
DLookup
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.

Edit-

Using the second method from HansUp I got the following:

UPDATE MasterpartsTable INNER JOIN PartNumberX ON MasterpartsTable.Part_Number=

PartNumberX.Part_Number

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


Which results in an empty query.

Edit2-

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

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 ...

SELECT
    [Part Name],
    Part_Number,
    DLookUp(
        "Part_Name",
        "MasterPartTable",
        "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:

SELECT
    p.[Part Name],
    p.Part_Number,
    m.Part_Number,
    m.Part_Name
FROM
    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.

Comments