How do I do this UPDATE in a single query?

[E-Town]

UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [26] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='26')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [50] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='50')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[51] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='51')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[62] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='62')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[66] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='66')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[80] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='80')) ;

There's a way to do it in regular SQL server using CASE, but in Access it
doesn't work!

Thx

 

[David C. Holley]

Do you have fields named [26], [50], [51], etc.? If so why are they named as
such? Typically [HCPC struct 12-17-09]. [26] would be read as [table
name].[field name]

 

[John W. Vinson]

UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [26] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='26')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [50] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='50')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[51] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='51')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[62] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='62')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[66] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='66')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[80] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='80')) ;

There's a way to do it in regular SQL server using CASE, but in Access it
doesn't work!

Thx

Access SQL and T/SQL are two different dialects. Two relevant differences are
that Access SQL does not allow multiple SQL commands in one query; another is
that Access does not support the CASE statement.

Your table with 80 (or more) fields named [26] and [50] and [80] and so on is
seriously denormalized, which is one reason you're having difficulty! How is
table [HCPC struct 12-17-09] related to other tables? Could its data be
generated by using a CROSSTAB query using [Mod] as the Column Header?

 

[John Spencer]

If for some reason you are stuck with the structure you have, you can use a
query like the following.

UPDATE [HCPC struct 12-17-09]
SET [HCPC struct 12-17-09].[26] = IIF([Mod]='26','1',[26])
, [HCPC struct 12-17-09].[50] = IIF([Mod]='50','1',[50])
, [HCPC struct 12-17-09].[51] = IIF([Mod]='51','1',[51])
, [HCPC struct 12-17-09].[62] = IIF([Mod]='62','1',[62])
, [HCPC struct 12-17-09].[66] = IIF([Mod]='66','1',[66])
, [HCPC struct 12-17-09].[80] = IIF([Mod]='50','1',[80])
WHERE [HCPC struct 12-17-09].[Mod] IN ('26','50','51','62','66','80')

Although, you might find it more efficient to run 6 separate queries instead
of running one query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [26] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='26')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [50] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='50')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[51] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='51')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[62] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='62')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[66] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='66')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[80] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='80')) ;

There's a way to do it in regular SQL server using CASE, but in Access it
doesn't work!