CODEWITHSUNDEEP
ms-accesssql-updateis-empty
Sunfile
Sunfile

Reputation: 125

Update MS Access table empty fields based on same table

In MS Access database, I'm working with a table that has rows. In some cases not all columns of the rows are filled. I want to create an update query to update the values of the empty fields with data from other rows where the column is not empty.

googled the question but no satisfying answer has been found. Can someone show me how the query should be build?

Current table looks like

| Invoicenumber | Customer | Date    |
|---------------|----------|---------|
| 5             | 12       | 12-6-19 |
| 5             |          | 12-6-19 |
| 5             |          | 12-6-19 |
| 5             |          | 12-6-19 |
| 6             | 18       | 15-6-19 |
| 6             |          | 15-6-19 |
| 6             |          | 15-6-19 |
| 7             | 20       | 20-6-19 |
| 7             |          | 20-6-19 |

I need the table to look like this after updating:

| Invoicenumber | Customer | Date    |
|---------------|----------|---------|
| 5             | 12       | 12-6-19 |
| 5             | 12       | 12-6-19 |
| 5             | 12       | 12-6-19 |
| 5             | 12       | 12-6-19 |
| 6             | 18       | 15-6-19 |
| 6             | 18       | 15-6-19 |
| 6             | 18       | 15-6-19 |
| 7             | 20       | 20-6-19 |
| 7             | 20       | 20-6-19 |

Upvotes: 0

Views: 2510

Answers (4)

Sunfile
Sunfile

Reputation: 125

Question answered by solution mentioned by @John Mo. Used his code to update table with data available within the table.

Upvotes: 0

John Mo
John Mo

Reputation: 1326

You can do it with just SQL by joining the table to itself:

UPDATE 
    Invoices 
    INNER JOIN Invoices AS Inv2
    ON Invoices.InvoiceNumber = Inv2.InvoiceNumber
SET
    Invoices.Customer = Inv2.Customer
WHERE 
    (Invoices.[Customer] Is Null)
    AND (Inv2.Customer IS NOT NULL)

Upvotes: 2

June7
June7

Reputation: 21389

Try using domain aggregate function to pull the Customer:

UPDATE table1 SET table1.Customer = DMax("Customer","table1","Invoicenumber=" & [Invoicenumber])
WHERE (((table1.Customer) Is Null));

Upvotes: 0

RunninThruLife
RunninThruLife

Reputation: 69

You can place a Switch statement into the update to choose the value to update based on the state of the CUSTOMER field.

Update TestTable 
Set CUSTOMER = Switch(CUSTOMER is  Null,OTHER_FIELD,CUSTOMER = '',OTHER_FIELD,CUSTOMER<>'',CUSTOMER)

This statement will update the CUSTOMER field to the OTHER_FIELD where CUSTOMER is blank or CUSTOMER is Null. If CUSTOMER has a value, it sets it to that value (essentially leaving it as the same.)

The 'SWITCH' statement is Access's version of a 'Switch' or 'Select Case' in code, where the first parameter is the condition to check, the parameter after it is the value to take if the previous condition returns true. So...

Switch(1=1,'YES',2=1,'NO', case 3, return 3, case 4, return 4, etc., etc.,)

would return the 'YES' because 1 is equal to 1.

There would be a more eloquent way to do this with code, but in an Access query, I don't know any other way.

Upvotes: 0

Related Questions