Repeating Query

[tomrector]

I have a table I wnat to insure has consistant data in the "Activity"
field, based on data in the "Code" field. I am using an update query,
but I have 75 possible Code values that I need to run or check.

How can I set up one update query to check the Code field and replace
data in the activity field to be consistant ? (without setting up 75
queries)

UPDATE fundBudget SET fundBudget.Activity = "EA Equipment Loan"
WHERE (((fundBudget.Code)="EA"));

UPDATE fundBudget SET fundBudget.Activity = "EB Equipment Rental"
WHERE (((fundBudget.Code)="EB"));

UPDATE fundBudget SET fundBudget.Activity = "EC Vehicle Storage"
WHERE (((fundBudget.Code)="EC"));

etc;etc;.......

Thanks,
Thomas Rector

 

[John Spencer]

Best way would be to have a NEW table with two fields. Field 1 would be the
Code and field two would be the expansion. This new table would have one
record for each Budget Code (about 75 records total)

Code Activity
EA EA Equipment Loan
EB EB Equipment Rental

Then all you need to do is to include the new table in your queries and link
the current Code to the Code field in your new table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[tomrector]

Wow, stumac !
Worked great.... Thanks
Changed DESCRIPTION to Activity
Removed [yourtable]!

&" "&   since my Activity data string
includes the code already.
ie EC Equipment Rental

Thanks again !
[QUOTE]
One way would be to create a new table containg 2 fields:
Code
Description

input your 75 codes & descriptions, then just create one update query

UPDATE yourtable INNER JOIN newtable ON yourtable.CODE = newtable.CODE SET
yourtable.ACTIVITY = [yourtable]![CODE] & " " & [newtable]![DESCRIPTION];

HTH

Stu


[QUOTE="[email protected]"]
I have a table I wnat to insure has consistant data in the "Activity"
field, based on data in the "Code" field.  I am using an update query,
but I have 75 possible Code values that I need to run or check.

How can I set up one update query to check the Code field and replace
data in the activity field to be consistant ?   (without setting up 75
queries)

UPDATE fundBudget SET fundBudget.Activity = "EA Equipment Loan"
WHERE (((fundBudget.Code)="EA"));

UPDATE fundBudget SET fundBudget.Activity = "EB Equipment Rental"
WHERE (((fundBudget.Code)="EB"));

UPDATE fundBudget SET fundBudget.Activity = "EC Vehicle Storage"
WHERE (((fundBudget.Code)="EC"));

etc;etc;.......

Thanks,
Thomas Rector
[/QUOTE][/QUOTE]