CODEWITHSUNDEEP
sqlms-accessms-access-2013
aqua_imanok
aqua_imanok

Reputation: 13

Create an update query with multiple criteria?

We have an Access 2013 database for our product listings, I've worked in Access for about a month. We've got fields for SKU as well as category, description, etc in Table 1.

Now I need to add even more fields to Table 1, including a column for weights and a column for out-of-stock message.

We have it set up so that only one instance of a SKU is allowed, no duplicates. I think that's why when I tried to import my Excel file with weights & messages, the import failed because all "records were lost due to key violations." There were no other errors with my file. I'm also working on a front end of a split database, if that makes a difference.

So, I imported the Excel file to a new table (Table 2). I am trying to run an UPDATE query, but I'm having a hard time with the criteria.

For example, on SKUs F496, K4492-92, and P49201, I need to update their blank weight & message fields in Table 1 with the values in Table 2 for weights and messages (all the weights & messages are unique). This will be for 2000+ products.

This seems like a basic thing, I've searched and searched but maybe I didn't have the right phrases.

Upvotes: 1

Views: 2399

Answers (1)

HansUp
HansUp

Reputation: 97101

Start by building a SELECT query from Design View of the query designer. Choose the two tables, then right-click the header of each to assign aliases for the table names: t1 for Table 1; and t2 for Table 2. Next drag a line between the SKU fields to create an INNER JOIN between them. Add the SKU, weight, and message fields from Table 1 to the design grid. Run the query (switch to Datasheet View) to confirm the results are reasonable. If you then switch to SQL View, you should see a statement similar to this ...

SELECT
    t1.SKU,
    t1.weight,
    t1.message
FROM
    [Table 1] AS t1
    INNER JOIN [Table 2] AS t2
    ON t1.SKU = t2.SKU;

If it's OK, switch back to Design View, find the Query Type section of the Design ribbon and change the type to Update. In the "Update To:" line under the weight field, enter [t2].[weight]. And enter [t2].[message] under message.

When you switch to SQL View again, you should see a statement like this ...

UPDATE
    [Table 1] AS t1
    INNER JOIN [Table 2] AS t2
    ON t1.SKU = t2.SKU
SET
    t1.weight = [t2].[weight],
    t1.message = [t2].[message];

If it looks OK, execute the query. If the update does not work as intended, copy the statement text, paste it into your question, include the full text of any error message, and explain how the result was different than what you need.

Upvotes: 1

Related Questions