Access 2021: How to create an Update Query

Question

With reference to this graphic -

I wish to create an update query to update [Banknotes: Notes].[SEQ] to [Banknotes: Catalogue].[SEQ] - it's such along time that I dabled in generating Access queries, I've forgotten hoe to do it.

Could anyone please help?

Answer 1

Hello

I’m Adeyemi and I’d be happy to help you with your question.

Creating an Update Query in Access 202 can be done by following these steps:

1. Create a SELECT query: Determine the records that will be updated. Apply any required query criteria, and view the data that will be updated.
2. Convert to an Update Query: In the query design view, click on the drop-down arrow to the right of the Query Type button and choose Update Query
3. Specify Fields to Update: Specify the fields you want to update. The next step can be a little bit tricky—you need to tell Access which fields to update and how to update them. For example, if you wanted to raise the price of the Normal Price field by 10 percent you would write an expression—like [Normal Price]+ ( [Normal Price]*.1)—in the field's Update To row to make this happen.
4. Run the Query: Click the Run button. Access lets you know how many records will be affected and that the changes can't be undone by the Undo command. Click Yes. Access updates the records.

Please note that when you're creating an Update Query, there are some restrictions on fields that can be updated such as Calculated fields, Fields from a totals query or a crosstab query, AutoNumber fields, Fields in unique-values queries and unique-records queries, Fields in a union query, and Fields that are primary keys

Here is a good reference: https://www.customguide.com/access/update-queries. Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.'

I hope this helps

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below

Regards Adeyemi

Answer 2

Kindly please provide feedback on what you can see now after trying all the listed suggestions so we can help troubleshoot further

Regards Adeyemi

Answer 3

I think PREFIXSUB in the Catalogue table must have a unique index. Otherwise, the query might not be updateable.

Answer 4

I think PREFIXSUB in the Catalogue table must have a unique index. Otherwise, the query might not be updateable.

Yes it does!

Answer 5

I think it's not necessary and a violation of good table design.

If "SEQ" is the same thing in both tables, that violates the principle that data points are to be stored one time, in one place.

There is a more formal statement of the rule, although I tend to prefer the more informal approach. "One time, one place"

SEQ is stored as part of [BankNotes: Catalogue]. It is redundant and unnecessary to store it again in the related table [BankNotes: Notes]. At least the stub of the query in the screenshot seems to indicate you want to update the SEQ field in the [BankNotes: Notes] table.

In fact, there are other fields which appear to be redundant. Denomination and Condition.

Also, the nature of the relationship between tables is not clear. Is that because these are linked SQL Server tables, or is there really no primary or foreign key in either table?