Update query. Within the same table: populating field 2 from field

[Remi]

Hi,

I have 1 table "Dispatch" with 3 fields:
A.DispatchID (autonumber; primary key),
B.ProductionbatchID (number; combobox coming from another table),
C.ProductionbatchIDBackup (with no value so far)

I am trying to create an update query that would poplulate the field C based
on B.

First, I have created a select query "ProdBatchIDNotBackedUp" based on the
table "Dispatch" in order to list all data in C that are blank or nil. It
produces the following fields:
1.DispatchID
2.ProductionbatchID (i.e. those that are not yet populated in field C table
"Dispatch").

Second I have created an update query, which contains the table "Dispatch"
and the select query "ProdBatchIDNotBackedUp" with an inner join between the
Dispatch.DispatchID and ProdBatchIDNotBackedUp.DispatchID
The goal of this update query is to:
UPDATE (table) Dispatches
SET (field) Dispatches.ProductionBatchBackUp = the ProductionbatchID from
select query ProdBatchIDNotBackedUp.

I have tried to insert a DLOOKUP in the SET section but I am getting it
right.

Does someone has a solution? Thanks

 

[John Spencer]

I am a bit confused but I think what you are trying to do is set
ProductionBatchIdBackup to ProductionBatchID if ProductionBatchIDBackup is Null.

If that is the case, then a simple query like the following should work:

UPDATE Dispatches
SET Dispatches.ProductionBatchIdBackup = [ProductionBatchID]
WHERE ProductionBatchIdBackup is Null or ProductionBatchIdBackup = ""

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Remi]

John,

Thank you for looking into it. I tried the solution but it does not work. I
only get results with 0.

As my first question was not very clear, I am providing a better (hopefully)
explanation below of what I am trying to achieve.

The last column (C) in the table below does not have any record (only 0). It
is intentional at the beginning. At a later stage, data in the the column B
will be changed but before it is changed I want to keep a back up of column B
(in C). This is why I am trying to set a query that would populate column C
with the data from column B.

Table Dispatch:
=====================================
A B C
DispatchID¦ProductionbatchID¦ProductionbatchIDBackup
=====================================
1 ¦ 20 ¦ 0
2 ¦ 21 ¦ 0
3 ¦ 22 ¦ 0
=====================================

If you know a quick fix for this one I would be more thane grateful. Thanks
again.

Remi

I am a bit confused but I think what you are trying to do is set
ProductionBatchIdBackup to ProductionBatchID if ProductionBatchIDBackup is Null.

If that is the case, then a simple query like the following should work:

UPDATE Dispatches
SET Dispatches.ProductionBatchIdBackup = [ProductionBatchID]
WHERE ProductionBatchIdBackup is Null or ProductionBatchIdBackup = ""

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I have 1 table "Dispatch" with 3 fields:
A.DispatchID (autonumber; primary key),
B.ProductionbatchID (number; combobox coming from another table),
C.ProductionbatchIDBackup (with no value so far)

I am trying to create an update query that would poplulate the field C based
on B.

First, I have created a select query "ProdBatchIDNotBackedUp" based on the
table "Dispatch" in order to list all data in C that are blank or nil. It
produces the following fields:
1.DispatchID
2.ProductionbatchID (i.e. those that are not yet populated in field C table
"Dispatch").

Second I have created an update query, which contains the table "Dispatch"
and the select query "ProdBatchIDNotBackedUp" with an inner join between the
Dispatch.DispatchID and ProdBatchIDNotBackedUp.DispatchID
The goal of this update query is to:
UPDATE (table) Dispatches
SET (field) Dispatches.ProductionBatchBackUp = the ProductionbatchID from
select query ProdBatchIDNotBackedUp.

I have tried to insert a DLOOKUP in the SET section but I am getting it
right.

Does someone has a solution? Thanks

 

[John Spencer]

Well, since you have ZERO in the field the where clause is going to screen out
those records.

UPDATE Dispatches
SET Dispatches.ProductionBatchIdBackup = [ProductionBatchID]
WHERE ProductionBatchIdBackup = 0 or ProductionBatchIDBackup is Null

If you are shure that ProductionBatchIDBackup will always contain zero (never
blank) then you can drop the
or ProductionBatchIDBackup is Null
line in the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John,

Thank you for looking into it. I tried the solution but it does not work. I
only get results with 0.

As my first question was not very clear, I am providing a better (hopefully)
explanation below of what I am trying to achieve.

The last column (C) in the table below does not have any record (only 0). It
is intentional at the beginning. At a later stage, data in the the column B
will be changed but before it is changed I want to keep a back up of column B
(in C). This is why I am trying to set a query that would populate column C
with the data from column B.

Table Dispatch:
=====================================
A B C
DispatchID¦ProductionbatchID¦ProductionbatchIDBackup
=====================================
1 ¦ 20 ¦ 0
2 ¦ 21 ¦ 0
3 ¦ 22 ¦ 0
=====================================

If you know a quick fix for this one I would be more thane grateful. Thanks
again.

Remi

I am a bit confused but I think what you are trying to do is set
ProductionBatchIdBackup to ProductionBatchID if ProductionBatchIDBackup is Null.

If that is the case, then a simple query like the following should work:

UPDATE Dispatches
SET Dispatches.ProductionBatchIdBackup = [ProductionBatchID]
WHERE ProductionBatchIdBackup is Null or ProductionBatchIdBackup = ""

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I have 1 table "Dispatch" with 3 fields:
A.DispatchID (autonumber; primary key),
B.ProductionbatchID (number; combobox coming from another table),
C.ProductionbatchIDBackup (with no value so far)

I am trying to create an update query that would poplulate the field C based
on B.

First, I have created a select query "ProdBatchIDNotBackedUp" based on the
table "Dispatch" in order to list all data in C that are blank or nil. It
produces the following fields:
1.DispatchID
2.ProductionbatchID (i.e. those that are not yet populated in field C table
"Dispatch").

Second I have created an update query, which contains the table "Dispatch"
and the select query "ProdBatchIDNotBackedUp" with an inner join between the
Dispatch.DispatchID and ProdBatchIDNotBackedUp.DispatchID
The goal of this update query is to:
UPDATE (table) Dispatches
SET (field) Dispatches.ProductionBatchBackUp = the ProductionbatchID from
select query ProdBatchIDNotBackedUp.

I have tried to insert a DLOOKUP in the SET section but I am getting it
right.

Does someone has a solution? Thanks

 

[Remi]

John,

Thanks again. Unfortunately I get the same result in the update query: the
values showing up are 0 for all lines. I will find another way around this
back up. Thanks again for your help.

Remi

Well, since you have ZERO in the field the where clause is going to screen out
those records.

UPDATE Dispatches
SET Dispatches.ProductionBatchIdBackup = [ProductionBatchID]
WHERE ProductionBatchIdBackup = 0 or ProductionBatchIDBackup is Null

If you are shure that ProductionBatchIDBackup will always contain zero (never
blank) then you can drop the
or ProductionBatchIDBackup is Null
line in the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John,

Thank you for looking into it. I tried the solution but it does not work. I
only get results with 0.

As my first question was not very clear, I am providing a better (hopefully)
explanation below of what I am trying to achieve.

The last column (C) in the table below does not have any record (only 0). It
is intentional at the beginning. At a later stage, data in the the column B
will be changed but before it is changed I want to keep a back up of column B
(in C). This is why I am trying to set a query that would populate column C
with the data from column B.

Table Dispatch:
=====================================
A B C
DispatchID¦ProductionbatchID¦ProductionbatchIDBackup
=====================================
1 ¦ 20 ¦ 0
2 ¦ 21 ¦ 0
3 ¦ 22 ¦ 0
=====================================

If you know a quick fix for this one I would be more thane grateful. Thanks
again.

Remi

I am a bit confused but I think what you are trying to do is set
ProductionBatchIdBackup to ProductionBatchID if ProductionBatchIDBackup is Null.

If that is the case, then a simple query like the following should work:

UPDATE Dispatches
SET Dispatches.ProductionBatchIdBackup = [ProductionBatchID]
WHERE ProductionBatchIdBackup is Null or ProductionBatchIdBackup = ""

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Remi wrote:
Hi,

I have 1 table "Dispatch" with 3 fields:
A.DispatchID (autonumber; primary key),
B.ProductionbatchID (number; combobox coming from another table),
C.ProductionbatchIDBackup (with no value so far)

I am trying to create an update query that would poplulate the field C based
on B.

First, I have created a select query "ProdBatchIDNotBackedUp" based on the
table "Dispatch" in order to list all data in C that are blank or nil. It
produces the following fields:
1.DispatchID
2.ProductionbatchID (i.e. those that are not yet populated in field C table
"Dispatch").

Second I have created an update query, which contains the table "Dispatch"
and the select query "ProdBatchIDNotBackedUp" with an inner join between the
Dispatch.DispatchID and ProdBatchIDNotBackedUp.DispatchID
The goal of this update query is to:
UPDATE (table) Dispatches
SET (field) Dispatches.ProductionBatchBackUp = the ProductionbatchID from
select query ProdBatchIDNotBackedUp.

I have tried to insert a DLOOKUP in the SET section but I am getting it
right.

Does someone has a solution? Thanks

 

[John Spencer]

Are you running the query (Menu: Query: Run) or are you just switching to
datasheet view?

Switching to datasheet view will show you what you would update if you ran the
query, but it will not update anything. You must execute the query (run:
query or open the query directly (not from design view) to actually update data.

If you know all this, I've wasted a bit of time. If you don't already know
this, then you may have a solution.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Remi]

John,

Yes I was just swtiching to datasheet view to see what would be updated as I
thought it would show the new values that would be inserted. (as you
mentioned, it only shows the rows selected for the update, not the update
itself).

Thank you very much. It worked.

Remi