CODEWITHSUNDEEP
sqlms-accessms-access-2003
Chaostryder
Chaostryder

Reputation: 223

MS ACCESS 2003 (2000 file format) SQL query

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?

OR

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)' OR [VARIABLE_NAME]='ALUMINUM_27 DISSOLVED -       AL','Aluminum D',
IIf([VARIABLE_NAME]='ALUMINUM TOTAL' OR [VARIABLE_NAME]='ALUMINUM TOTAL RECOVERABLE' OR         [VARIABLE_NAME]='ALUMINUM_27 TOTAL RECOVERABLE - AL','Aluminum T',
IIf([VARIABLE_NAME]='ANTIMONY DISSOLVED (SB)' OR [VARIABLE_NAME]='ANTIMONY_121 DISSOLVED - SB','Antimony B',
IIf([VARIABLE_NAME]='ANTIMONY TOTAL' OR [VARIABLE_NAME]='ANTIMONY TOTAL RECOVERABLE' OR [VARIABLE_NAME]='ANTIMONY_121 TOTAL RECOVERABLE - SB','Antimony T',
IIf([VARIABLE_NAME]='ARSENIC DISSOLVED' OR [VARIABLE_NAME]='ARSENIC_75 DISSOLVED - AS','Arsenic D',
IIf([VARIABLE_NAME]='ARSENIC TOTAL' OR [VARIABLE_NAME]='ARSENIC TOTAL RECOVERABLE' OR [VARIABLE_NAME]='ARSENIC_75 TOTAL RECOVERABLE - AS','Arsenic T',
IIf([VARIABLE_NAME]='BARIUM DISSOLVED' OR [VARIABLE_NAME]='BARIUM_137 DISSOLVED - BA','Barium D',
IIf([VARIABLE_NAME]='BARIUM TOTAL' OR [VARIABLE_NAME]='BARIUM TOTAL RECOVERABLE' OR [VARIABLE_NAME]='BARIUM_137 TOTAL RECOVERABLE - BA','Barium T',
IIf([VARIABLE_NAME]='BERYLLIUM DISSOLVED' OR [VARIABLE_NAME]='BERYLLIUM_9 DISSOLVED - BE','Beryllium D',
IIf([VARIABLE_NAME]='BERYLLIUM TOTAL' OR [VARIABLE_NAME]='BERYLLIUM TOTAL RECOVERABLE' OR [VARIABLE_NAME]='BERYLLIUM_9 TOTAL RECOVERABLE - BE','Beryllium T',

))))))))));

Thank you everyone for your tips, answers, time, comments.

Upvotes: 1

Views: 289

Answers (1)

Fionnuala
Fionnuala

Reputation: 91316

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

And so on. You can then join this table to your main table to get indicator name:

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

You may not even have to update the column, but you can with a set-up like this, for example:

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

Upvotes: 2

Related Questions