variable column name for update statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have 1 table (AllPrdts) and 1 query (PrdtsToUpdate).

Example of data in AllPrdts table
Country Fact Code Jan Feb Mar
AU Qty 20YH123 13 14 9
AU Sales 20YH123 4.8 2.24 5.86
AU GP 20YH123 11.38 15.95 10.83
AU CP 20YH123 0 0 0
SG Qty 20YH123 89.87 1 9.53
SG Sales 20YH123 21.65 0.5 21.05
SG GP 20YH123 25.61 0 2.84
SG CP 20YH123 25.61 3.5 2.84
SG GM 20YH123 125.95 13.5 18.21

Example of data for PrdtsToUpdate query (this query stores the Code that have a zero value for GP or CP in AllPrdts table when it's Qty is non zero)
Country Code Month Qty GP/CP
AU 20YH123 Jan 13 0
AU 20YH123 Feb 14 0
AU 20YH123 Mar 9 0
SG 20YH123 Feb 1 0

I need to change all the Facts of that particular code of that particular country listed in PrdtsToUpdate to zero (0). To produce the following output table.

Expected AllPrdts after Update
Country Fact Code Jan Feb Mar
AU Qty 20YH123 0 0 0
AU Sales 20YH123 0 0 0
AU GP 20YH123 0 0 0
AU CP 20YH123 0 0 0
SG Qty 20YH123 89.87 0 9.53
SG Sales 20YH123 21.65 0 21.05
SG GP 20YH123 25.61 0 2.84
SG CP 20YH123 25.61 0 2.84
SG GM 20YH123 125.95 0 18.21

I am trying to use the value captured in "Month" column of PrdtsToUpdate query as a variable column name when updating the AllPrdts table.

I've written a SQL statement like the following. But unsure how to do it in Access SQL

UPDATE AllPrdts LEFT JOIN PrdtsToUpdate ON ([AllPrdts].[Country]=[PrdtsToUpdate].[Country]) AND ([AllPrdts].
Code: 
=[PrdtsToUpdate].[Code]) SET AllPrdts.$(variable_storing_the_"Month"_column_of_PrdtsToUpdate_query) = 0;

Please kindly help if possible.
Thanks in advance,
yann
 
Hi Yann,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.

From your description, I understand that you wanted to update your ALLPrdts table with a
Variable Column captured from the existed query. Have I fully understood you? If there is
anything I misunderstood, please feel free to let me know.

Based on my experience, In T-SQL, we cannot use Variable Columns in the UPDATE
statement. The variables can be used in the expresson such as 'SET column_name = @var'.
However, we can use dynamic query to realize the changable columns in the UPDATE
statement, kludge the characters into one variable (@sql) and use EXEC(@sql) to execute the
whole statement as a String.

In Access, you can also perform this in VBA by using a variable to store the 'Month' and
kludge the UPDATE statement into one String. After that, you can use Docmd.RunSQL to run
the String and update your table....Or you can also use DAO.Querydef to Execute the SQL
statements in VBA.

Yann, does this answer your question? Please feel free to let me know if this help solves your
problem. If there is anything more I can do to assist you, please feel free to post it in the group.

Best regards,

Billy Yao
Microsoft Online Support
 
Back
Top