Update Multiple Records in Access

Question

Hello,

I have a table named, Quality, that has the Sequence # and the Year Result columns.

Quality

Sequence# -------Year Result

1----------------------

1----------------------3.2

1----------------------

2----------------------2.1

2----------------------

2----------------------

2----------------------

The Sequence # column stores duplicate values such as 1, 1,1,,,2, 2,2,2,2....., but the  Year Result column has only one value for each duplicate sequence #.

On the Year Result column, I would like to have the 3.2 value, paste/appear to all the Sequence # 1. Likewise, the 2.1 value will be added to the all sequence #2 on the Year Result column.

1-----------3.2

1-----------3.2

1-----------3.2

2-----------2.1

2-----------2.1

2-----------2.1

2-----------2.1

I tried to create a update query to update the Year Result column, but I don't know what I should put at the criteria row to make it works. 

Any suggestions are appreciated.

Loi

Answer 1

Field: 2012 year Result

Table: Quality Metrics

Update To: SET [2012 Year Result] = DLookup("[2012 Year Result]", "Quality Services ", "[2012 Year Result] is Not Null And [Sequence] = " & [Sequence])  WHERE  [2012 Year Result] IsNull

Criteria:

You are pasting entire SQL statements into the UPDATE TO and CRITERIA cells in the query grid. That will NOT work.

Try creating a new query based on [Quality Metrics]. Don't add any fields at all. Instead switch to SQL view and copy and paste this text, between the ==== lines, into the SQL window. You can then switch to Query Design view to see how it's set up.

====

UPDATE [Quality Metrics] SET [2012 Year Result] = DLookUp("[2012 Year Result]","[Quality Metrics]", "[2012 Year Result] IS NOT NULL AND [Sequence] = " & [Sequence]) WHERE [2012 Year Result] IS NULL;

====

I'm confused about the table name - you say it's "Quality Metrics" but your query references a different table name, "Quality Services " (with an extra blank which will cause problems). Which is the real table name?

Answer 2

Please open the query in SQL view and copy and paste the complete SQL statement. Marshall and I gave the same (correct, I believe) expression - great minds run in the same channels! - so there is something else going wrong. If your fieldnames contain blanks you must enclose the fieldname in square brackets - might that be the issue? Note also that # is a date delimiter in Access; it's best not to use it in fieldnames. You might want to rename the field to SequenceNo rather than [Sequence#] or [Sequence #].

Answer 3

Hi Marshall and Barton,

Thank you so much for your help.

I got a message said, "the expression you entered contains invalid syntax."

The “WHERE” was always highlighted. please help

Thanks

Chi Loi

Answer 4

You should be able to get the desired result with the help of a DLookUp:

UPDATE tablename

SET YearResult = DLookUp("[YearResult]", "[tablename]", "[Sequence#] = " & [Sequence#] & " AND [YearResult] IS NOT NULL") WHERE [YearResult] IS NULL;

I've got some MAJOR concerns with your table design though! What if a given Sequence# has two or more non null YearResult values? Which do you want to use? Does your table have a Primary Key? If not how can you distinguish identical rows from one another? Are you assuming that the rows have a sequential order? They don't; that's spreadsheet logic, not relational logic.

I hope this query works but I'd strongly suggest reading up on normalization!

Answer 5

That requires a subquery, but update queries don't like subqueries, so use DLookup instead.  Try this and see if it's what you want.

UPDATE Quality

SET [Year Result] = DLookup("[Year Result]", "Quality ", "[Year Result] is Not Null And [Sequence#] = " & [Sequence#])  WHERE  [Year Result] Is Null