Question

i have a table with the fields : [VARIABLE_CODE],[VARIABLE_NAME],[CAS_NO],[USER_VAR_GROUP_NAME] and what i want to do to this talbe is to either use an append query and take entries from the table and put them into another table with the extra field [INDICATOR_NAME]. The [INDICATOR_NAME] varies accoridng to whats in the [VARIABLE_NAME]. Example AluminumDissovled variable name ---> Aluminum D Indicator name.

But the only way i can think of doing this is to have many append queries. one for each condition, each indictor name. and that would be very many queries to write. Is there anyway to have an append query that encompasses and will perform all the indicator nmes/conditions?

页: 1

add the extra field [INDICATOR_NAME] and run an update query liek the one below. [INDICATOR_NAME] will be added according to what is in the [VARIABLE_NAME] field. The problem is, i have ten differnt conditions below. but in reality i have about a hundred. from A-Z. Yet there is a limit to how many IFF you can have in a queries.
Is there anyway to have that many conditions in one query????

UPDATE QRY_Variables_A SET INDICATOR_NAME = 
IIf([VARIABLE_NAME]= ALUMINUM DISSOLVED (AL)  页: 1[VARIABLE_NAME]= ALUMINUM_27 DISSOLVED -       AL , Aluminum D ,
IIf([VARIABLE_NAME]= ALUMINUM TOTAL  页: 1[VARIABLE_NAME]= ALUMINUM TOTAL RECOVERABLE  页: 1        [VARIABLE_NAME]= ALUMINUM_27 TOTAL RECOVERABLE - AL , Aluminum T ,
IIf([VARIABLE_NAME]= ANTIMONY DISSOLVED (SB)  页: 1[VARIABLE_NAME]= ANTIMONY_121 DISSOLVED - SB , Antimony B ,
IIf([VARIABLE_NAME]= ANTIMONY TOTAL  页: 1[VARIABLE_NAME]= ANTIMONY TOTAL RECOVERABLE  页: 1[VARIABLE_NAME]= ANTIMONY_121 TOTAL RECOVERABLE - SB , Antimony T ,
IIf([VARIABLE_NAME]= ARSENIC DISSOLVED  页: 1[VARIABLE_NAME]= ARSENIC_75 DISSOLVED - AS , Arsenic D ,
IIf([VARIABLE_NAME]= ARSENIC TOTAL  页: 1[VARIABLE_NAME]= ARSENIC TOTAL RECOVERABLE  页: 1[VARIABLE_NAME]= ARSENIC_75 TOTAL RECOVERABLE - AS , Arsenic T ,
IIf([VARIABLE_NAME]= BARIUM DISSOLVED  页: 1[VARIABLE_NAME]= BARIUM_137 DISSOLVED - BA , Barium D ,
IIf([VARIABLE_NAME]= BARIUM TOTAL  页: 1[VARIABLE_NAME]= BARIUM TOTAL RECOVERABLE  页: 1[VARIABLE_NAME]= BARIUM_137 TOTAL RECOVERABLE - BA , Barium T ,
IIf([VARIABLE_NAME]= BERYLLIUM DISSOLVED  页: 1[VARIABLE_NAME]= BERYLLIUM_9 DISSOLVED - BE , Beryllium D ,
IIf([VARIABLE_NAME]= BERYLLIUM TOTAL  页: 1[VARIABLE_NAME]= BERYLLIUM TOTAL RECOVERABLE  页: 1[VARIABLE_NAME]= BERYLLIUM_9 TOTAL RECOVERABLE - BE , Beryllium T ,

))))))))));

谢谢大家的言辞、回答、时间、评论。

Answer 1

You might like to consider creating another table that holds the list:

Variable_Name                       Indicator_Name
ALUMINUM DISSOLVED (AL)             Aluminum D
ALUMINUM_27 DISSOLVED -       AL    Aluminum D

因此。之后,你可以把这个表格列入你的主要表格,以获得指标名称:

SELECT MyTable.Variable_Name, NewTable.Indicator_Name 
FROM MyTable 
INNER JOIN NewTable
ON MyTable.Variable_Name = NewTable.Variable_Name

你甚至可能不需要更新该栏,但可以像现在这样安排,例如:

UPDATE MyTable 
INNER JOIN NewTable
On MyTable.Variable_Name = NewTable.Variable_Name
SET MyTable.Indicator_Name = NewTable.Indicator_name

友情链接