If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update query that is updating fields not included in any statement
I have been downloading data from a website that I import into a table. From
there I run queries on the data to append and update my tracking table for the data I download from the internet. After running an update query for just one field in the table (joined to the downloaded data) I have noticed that other fields are being updated. I checked the SQL View and cannot diagnose how the query is updating other fields. I did change the Dynaset in some queries but how could that change fields not even chosen in the design view or the SQL view? I have a feeling it has to do with the Dynaset settings. Any ideas would be greatly appreciated. Thanks. |
#2
|
|||
|
|||
Update query that is updating fields not included in any statement
Post the SQL here.
-- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Todd" wrote: I have been downloading data from a website that I import into a table. From there I run queries on the data to append and update my tracking table for the data I download from the internet. After running an update query for just one field in the table (joined to the downloaded data) I have noticed that other fields are being updated. I checked the SQL View and cannot diagnose how the query is updating other fields. I did change the Dynaset in some queries but how could that change fields not even chosen in the design view or the SQL view? I have a feeling it has to do with the Dynaset settings. Any ideas would be greatly appreciated. Thanks. |
#3
|
|||
|
|||
Update query that is updating fields not included in any state
This is the first query I run. It appends any records from my import table
that do not match the tracking table (* Base) joined on the tracking number. INSERT INTO [07517 m_eggers Base] ( Status, BTN, PON, [Reference TN], [Submitted Date], [Business Name], [Service Order #], [Due Date], [Completion Date], [Tracking #], [ASR Notes], [SORD Remarks], [Assignment Section], Product ) SELECT [qry SalesLog Import fixed].Status, [qry SalesLog Import fixed].[BTN Fixed], [qry SalesLog Import fixed].[PON Fixed], [qry SalesLog Import fixed].[RTN Fixed], [qry SalesLog Import fixed].SDate, [qry SalesLog Import fixed].[Business Name], [qry SalesLog Import fixed].[Service Order Fixed], [qry SalesLog Import fixed].[Due Date Fixed], [qry SalesLog Import fixed].[Completion Date], [qry SalesLog Import fixed].[Tracking # Fixed], [qry SalesLog Import fixed].[ASR Notes], [qry SalesLog Import fixed].[SORD Remarks], [qry SalesLog Import fixed].[Assignment Section], [qry SalesLog Import fixed].[Product Fixed] FROM [qry SalesLog Import fixed] LEFT JOIN [07517 m_eggers Base] ON [qry SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #] WHERE ((([07517 m_eggers Base].[Tracking #]) Is Null)); This is the second query I run to look for any asr notes that are different between the base and import tables. UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #] SET [07517 m_eggers Base].[ASR Notes] = [qry SalesLog Import fixed]![ASR Notes] WHERE ((([07517 m_eggers Base].[ASR Notes])[qry SalesLog Import fixed]![ASR Notes] Or ([07517 m_eggers Base].[ASR Notes]) Is Null) AND (([qry SalesLog Import fixed].[ASR Notes]) Is Not Null)); I run this query to put the BTN into the base table if the import table has a BTN in the field. UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #] SET [07517 m_eggers Base].BTN = [qry SalesLog Import fixed]![BTN Fixed] WHERE ((([07517 m_eggers Base].BTN) Is Null) AND (([qry SalesLog Import fixed].[BTN Fixed]) Is Not Null)); What is in here that would update some of the other fields in my base table with information from by import table? "Jerry Whittle" wrote: Post the SQL here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Todd" wrote: I have been downloading data from a website that I import into a table. From there I run queries on the data to append and update my tracking table for the data I download from the internet. After running an update query for just one field in the table (joined to the downloaded data) I have noticed that other fields are being updated. I checked the SQL View and cannot diagnose how the query is updating other fields. I did change the Dynaset in some queries but how could that change fields not even chosen in the design view or the SQL view? I have a feeling it has to do with the Dynaset settings. Any ideas would be greatly appreciated. Thanks. |
#4
|
|||
|
|||
Update query that is updating fields not included in any state
The SET statement seems to limit things to the [07517 m_eggers Base].BTN and
[07517 m_eggers Base].[ASR Notes] fields. However you have an inner join between two tables in the UPDATE section. I'm wondering if this is causing an wildcard-like condition. Instead of the inner join, I would use an IN or EXISTS statement in the WHERE clause. I also wouldn't worry about the or Not Null in the Where clause. If they happen to be equal, it won't hurt to update them. Depending on the business rules, you might need to avoid updating a current value with a Null so this something like this part might need to stay. [SalesLog Import fixed].[ASR Notes]) Is Not Null -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Todd" wrote: This is the first query I run. It appends any records from my import table that do not match the tracking table (* Base) joined on the tracking number. INSERT INTO [07517 m_eggers Base] ( Status, BTN, PON, [Reference TN], [Submitted Date], [Business Name], [Service Order #], [Due Date], [Completion Date], [Tracking #], [ASR Notes], [SORD Remarks], [Assignment Section], Product ) SELECT [qry SalesLog Import fixed].Status, [qry SalesLog Import fixed].[BTN Fixed], [qry SalesLog Import fixed].[PON Fixed], [qry SalesLog Import fixed].[RTN Fixed], [qry SalesLog Import fixed].SDate, [qry SalesLog Import fixed].[Business Name], [qry SalesLog Import fixed].[Service Order Fixed], [qry SalesLog Import fixed].[Due Date Fixed], [qry SalesLog Import fixed].[Completion Date], [qry SalesLog Import fixed].[Tracking # Fixed], [qry SalesLog Import fixed].[ASR Notes], [qry SalesLog Import fixed].[SORD Remarks], [qry SalesLog Import fixed].[Assignment Section], [qry SalesLog Import fixed].[Product Fixed] FROM [qry SalesLog Import fixed] LEFT JOIN [07517 m_eggers Base] ON [qry SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #] WHERE ((([07517 m_eggers Base].[Tracking #]) Is Null)); This is the second query I run to look for any asr notes that are different between the base and import tables. UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #] SET [07517 m_eggers Base].[ASR Notes] = [qry SalesLog Import fixed]![ASR Notes] WHERE ((([07517 m_eggers Base].[ASR Notes])[qry SalesLog Import fixed]![ASR Notes] Or ([07517 m_eggers Base].[ASR Notes]) Is Null) AND (([qry SalesLog Import fixed].[ASR Notes]) Is Not Null)); I run this query to put the BTN into the base table if the import table has a BTN in the field. UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #] SET [07517 m_eggers Base].BTN = [qry SalesLog Import fixed]![BTN Fixed] WHERE ((([07517 m_eggers Base].BTN) Is Null) AND (([qry SalesLog Import fixed].[BTN Fixed]) Is Not Null)); What is in here that would update some of the other fields in my base table with information from by import table? "Jerry Whittle" wrote: Post the SQL here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Todd" wrote: I have been downloading data from a website that I import into a table. From there I run queries on the data to append and update my tracking table for the data I download from the internet. After running an update query for just one field in the table (joined to the downloaded data) I have noticed that other fields are being updated. I checked the SQL View and cannot diagnose how the query is updating other fields. I did change the Dynaset in some queries but how could that change fields not even chosen in the design view or the SQL view? I have a feeling it has to do with the Dynaset settings. Any ideas would be greatly appreciated. Thanks. |
#5
|
|||
|
|||
Update query that is updating fields not included in any state
Thank you for your response. I realized afterword the field that was
updating (*base.status) was included in the append query. Either I was tweaking the queries and accidentally included it (you know, double click the table and drag all fields into the design view) or it was something I/someone else did. I now believe this may have been the problem. Your suggestion that the inner join is causing a wildcard like condition is interesting. I was thinking similarly with my dynaset line of thought. That may still be the case. I am still too ignorant about the workings of a database to completely understand. I will have to look into the IN and EXISTS statements to learn about them. I was wondering if anyone would catch the and Not Null part. It is important for me to not update my tracking database with a value to null and the part helps me to investigate/think about the how and why my import data is the way it is. It also allows me to see useful data, like the submitted date (to acquire an idea of how far back these records are changing) and exactly what records will be changed, by first running it as a selest query (without having thousands of records return). Again, thanks for your time. cheap.fast.good. another twist on the old adage you can't have it all. "Jerry Whittle" wrote: The SET statement seems to limit things to the [07517 m_eggers Base].BTN and [07517 m_eggers Base].[ASR Notes] fields. However you have an inner join between two tables in the UPDATE section. I'm wondering if this is causing an wildcard-like condition. Instead of the inner join, I would use an IN or EXISTS statement in the WHERE clause. I also wouldn't worry about the or Not Null in the Where clause. If they happen to be equal, it won't hurt to update them. Depending on the business rules, you might need to avoid updating a current value with a Null so this something like this part might need to stay. [SalesLog Import fixed].[ASR Notes]) Is Not Null -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Todd" wrote: This is the first query I run. It appends any records from my import table that do not match the tracking table (* Base) joined on the tracking number. INSERT INTO [07517 m_eggers Base] ( Status, BTN, PON, [Reference TN], [Submitted Date], [Business Name], [Service Order #], [Due Date], [Completion Date], [Tracking #], [ASR Notes], [SORD Remarks], [Assignment Section], Product ) SELECT [qry SalesLog Import fixed].Status, [qry SalesLog Import fixed].[BTN Fixed], [qry SalesLog Import fixed].[PON Fixed], [qry SalesLog Import fixed].[RTN Fixed], [qry SalesLog Import fixed].SDate, [qry SalesLog Import fixed].[Business Name], [qry SalesLog Import fixed].[Service Order Fixed], [qry SalesLog Import fixed].[Due Date Fixed], [qry SalesLog Import fixed].[Completion Date], [qry SalesLog Import fixed].[Tracking # Fixed], [qry SalesLog Import fixed].[ASR Notes], [qry SalesLog Import fixed].[SORD Remarks], [qry SalesLog Import fixed].[Assignment Section], [qry SalesLog Import fixed].[Product Fixed] FROM [qry SalesLog Import fixed] LEFT JOIN [07517 m_eggers Base] ON [qry SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #] WHERE ((([07517 m_eggers Base].[Tracking #]) Is Null)); This is the second query I run to look for any asr notes that are different between the base and import tables. UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #] SET [07517 m_eggers Base].[ASR Notes] = [qry SalesLog Import fixed]![ASR Notes] WHERE ((([07517 m_eggers Base].[ASR Notes])[qry SalesLog Import fixed]![ASR Notes] Or ([07517 m_eggers Base].[ASR Notes]) Is Null) AND (([qry SalesLog Import fixed].[ASR Notes]) Is Not Null)); I run this query to put the BTN into the base table if the import table has a BTN in the field. UPDATE [qry SalesLog Import fixed] INNER JOIN [07517 m_eggers Base] ON [qry SalesLog Import fixed].[Tracking # Fixed] = [07517 m_eggers Base].[Tracking #] SET [07517 m_eggers Base].BTN = [qry SalesLog Import fixed]![BTN Fixed] WHERE ((([07517 m_eggers Base].BTN) Is Null) AND (([qry SalesLog Import fixed].[BTN Fixed]) Is Not Null)); What is in here that would update some of the other fields in my base table with information from by import table? "Jerry Whittle" wrote: Post the SQL here. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Todd" wrote: I have been downloading data from a website that I import into a table. From there I run queries on the data to append and update my tracking table for the data I download from the internet. After running an update query for just one field in the table (joined to the downloaded data) I have noticed that other fields are being updated. I checked the SQL View and cannot diagnose how the query is updating other fields. I did change the Dynaset in some queries but how could that change fields not even chosen in the design view or the SQL view? I have a feeling it has to do with the Dynaset settings. Any ideas would be greatly appreciated. Thanks. |
Thread Tools | |
Display Modes | |
|
|