Update Query

S

Springman

I have two tables [tbl_ACL_data] and [tbl_Update_RESTAT_Client_Code] joined
by [Client_Code] field

I want to populate the tbl_ACL_data.[RESTAT Client Code] field, which is all
null, with the data from the RESTAT_Client_Code1.[RESTAT_Client_Code] field
data.

The Update query returns all blank fields. Please advise me on where I'm
going wrong on this.

UPDATE tbl_ACL_data INNER JOIN tbl_Update_RESTAT_Client_Code1 ON
tbl_ACL_data.Client_Code = tbl_Update_RESTAT_Client_Code1.Client_Code SET
tbl_ACL_data.[RESTAT Client Code] =
tbl_Update_RESTAT_Client_Code1.[RESTAT_Client_Code]
WHERE (((tbl_ACL_data.[RESTAT Client Code]) Is Null Or (tbl_ACL_data.[RESTAT
Client Code])=""));
 
J

John Spencer

Are you running the query (Menu: Query: Run) or are you simply switching
the view to datasheet view? If you just switch the view then you will
see what would be updated.

UPDATE tbl_ACL_data INNER JOIN tbl_Update_RESTAT_Client_Code1
ON tbl_ACL_data.Client_Code = tbl_Update_RESTAT_Client_Code1.Client_Code
SET tbl_ACL_data.[RESTAT Client Code] =
tbl_Update_RESTAT_Client_Code1.[RESTAT_Client_Code]
WHERE (((tbl_ACL_data.[RESTAT Client Code]) Is Null Or
(tbl_ACL_data.[RESTAT Client Code])=""));

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
S

Springman

I've tried both. In both cases the query returns no data.

John Spencer said:
Are you running the query (Menu: Query: Run) or are you simply switching
the view to datasheet view? If you just switch the view then you will
see what would be updated.

UPDATE tbl_ACL_data INNER JOIN tbl_Update_RESTAT_Client_Code1
ON tbl_ACL_data.Client_Code = tbl_Update_RESTAT_Client_Code1.Client_Code
SET tbl_ACL_data.[RESTAT Client Code] =
tbl_Update_RESTAT_Client_Code1.[RESTAT_Client_Code]
WHERE (((tbl_ACL_data.[RESTAT Client Code]) Is Null Or
(tbl_ACL_data.[RESTAT Client Code])=""));

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

I have two tables [tbl_ACL_data] and [tbl_Update_RESTAT_Client_Code] joined
by [Client_Code] field

I want to populate the tbl_ACL_data.[RESTAT Client Code] field, which is all
null, with the data from the RESTAT_Client_Code1.[RESTAT_Client_Code] field
data.

The Update query returns all blank fields. Please advise me on where I'm
going wrong on this.

UPDATE tbl_ACL_data INNER JOIN tbl_Update_RESTAT_Client_Code1 ON
tbl_ACL_data.Client_Code = tbl_Update_RESTAT_Client_Code1.Client_Code SET
tbl_ACL_data.[RESTAT Client Code] =
tbl_Update_RESTAT_Client_Code1.[RESTAT_Client_Code]
WHERE (((tbl_ACL_data.[RESTAT Client Code]) Is Null Or (tbl_ACL_data.[RESTAT
Client Code])=""));
Click to expand...
Click to expand...
 
J

John Spencer

If you run this query what happens?

SELECT A.ClientCode, A.[RESTAT_CLIENT_CODE]
, B.ClientCode, B.[Restat_Client_Code]
FROM tbl_ACL_data As A INNER JOIN tbl_Update_RESTAT_Client_Code1 as B
ON A.Client_Code = B.Client_Code
WHERE WHERE A.[RESTAT Client Code] Is Null
Or A.[RESTAT Client Code]=""

Do you get any records returned? If not, then there are no records to
update. IF so, what is in the two Restat_Client_Code fields? Do they
have the expected values in them?

If there are records, can you type the correct values into the field? If
not then something is making this query not updateable.

You will have to trouble shoot this.

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

I've tried both. In both cases the query returns no data.

John Spencer said:
Are you running the query (Menu: Query: Run) or are you simply switching
the view to datasheet view? If you just switch the view then you will
see what would be updated.

UPDATE tbl_ACL_data INNER JOIN tbl_Update_RESTAT_Client_Code1
ON tbl_ACL_data.Client_Code = tbl_Update_RESTAT_Client_Code1.Client_Code
SET tbl_ACL_data.[RESTAT Client Code] =
tbl_Update_RESTAT_Client_Code1.[RESTAT_Client_Code]
WHERE (((tbl_ACL_data.[RESTAT Client Code]) Is Null Or
(tbl_ACL_data.[RESTAT Client Code])=""));

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

I have two tables [tbl_ACL_data] and [tbl_Update_RESTAT_Client_Code] joined
by [Client_Code] field

I want to populate the tbl_ACL_data.[RESTAT Client Code] field, which is all
null, with the data from the RESTAT_Client_Code1.[RESTAT_Client_Code] field
data.

The Update query returns all blank fields. Please advise me on where I'm
going wrong on this.

UPDATE tbl_ACL_data INNER JOIN tbl_Update_RESTAT_Client_Code1 ON
tbl_ACL_data.Client_Code = tbl_Update_RESTAT_Client_Code1.Client_Code SET
tbl_ACL_data.[RESTAT Client Code] =
tbl_Update_RESTAT_Client_Code1.[RESTAT_Client_Code]
WHERE (((tbl_ACL_data.[RESTAT Client Code]) Is Null Or (tbl_ACL_data.[RESTAT
Client Code])=""));
Click to expand...
Click to expand...
Click to expand...
 
S

Springman

The Select query returns the appropriate data in all fields.
If I recreate the Update query in a different database with a couple
records, it runs perfectly. Is their a limit on the number of records that
can be updated? I'm updating 1.4M records. I could have just used a make
table query, but the database would grow past 2GB.

John Spencer said:
If you run this query what happens?

SELECT A.ClientCode, A.[RESTAT_CLIENT_CODE]
, B.ClientCode, B.[Restat_Client_Code]
FROM tbl_ACL_data As A INNER JOIN tbl_Update_RESTAT_Client_Code1 as B
ON A.Client_Code = B.Client_Code
WHERE WHERE A.[RESTAT Client Code] Is Null
Or A.[RESTAT Client Code]=""

Do you get any records returned? If not, then there are no records to
update. IF so, what is in the two Restat_Client_Code fields? Do they
have the expected values in them?

If there are records, can you type the correct values into the field? If
not then something is making this query not updateable.

You will have to trouble shoot this.

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

I've tried both. In both cases the query returns no data.

John Spencer said:
Are you running the query (Menu: Query: Run) or are you simply switching
the view to datasheet view? If you just switch the view then you will
see what would be updated.

UPDATE tbl_ACL_data INNER JOIN tbl_Update_RESTAT_Client_Code1
ON tbl_ACL_data.Client_Code = tbl_Update_RESTAT_Client_Code1.Client_Code
SET tbl_ACL_data.[RESTAT Client Code] =
tbl_Update_RESTAT_Client_Code1.[RESTAT_Client_Code]
WHERE (((tbl_ACL_data.[RESTAT Client Code]) Is Null Or
(tbl_ACL_data.[RESTAT Client Code])=""));

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


Springman wrote:
I have two tables [tbl_ACL_data] and [tbl_Update_RESTAT_Client_Code] joined
by [Client_Code] field

I want to populate the tbl_ACL_data.[RESTAT Client Code] field, which is all
null, with the data from the RESTAT_Client_Code1.[RESTAT_Client_Code] field
data.

The Update query returns all blank fields. Please advise me on where I'm
going wrong on this.

UPDATE tbl_ACL_data INNER JOIN tbl_Update_RESTAT_Client_Code1 ON
tbl_ACL_data.Client_Code = tbl_Update_RESTAT_Client_Code1.Client_Code SET
tbl_ACL_data.[RESTAT Client Code] =
tbl_Update_RESTAT_Client_Code1.[RESTAT_Client_Code]
WHERE (((tbl_ACL_data.[RESTAT Client Code]) Is Null Or (tbl_ACL_data.[RESTAT
Client Code])=""));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding data to MS Access from csv file 0
Need some PWA help 0
Help: I have difficulties in Update Where Exists 2
AND query 4
Cross tab query 5
Update Query Help 2
Query gives me a warning when I ru it... 1
Calculation in Update query drops decimal places 2

Top