Help needed with update table query

[AndyDevine1]

Hi there

I have a table called "Current week" which contains a field called "Current week". This always contains one row of information, for this example lets say the current week is "week 20"

In my "master database" table I have an "all weeks" field and a "tag" field

In "all weeks" I have:
Row 1 = "2013 week 19"
Row 2 = "2013 week 20"
Row 3 = "2014 week 19"
Row 4 = "2014 week 20"
etc

What do I need to write an update query to update the "tag" field based on:

..if "all weeks" field contains "current week" then tag it with "yes", otherwise tag it with "no"?

ie to get...

All weeks (field)...tag (field)
Row 1 = "2013 week 19"...no
Row 2 = "2013 week 20"...yes
Row 3 = "2014 week 19"...no
Row 4 = "2014 week 20"...yes

Many thanks

 

[xenou]

You could do a two stepper, first "reset" the weeks to all no's, then update the current weeks to yes:

UPDATE [Master Database] SET [all week] = "no";
UPDATE [Master Database] SET [all week] = "yes" WHERE [all week] = (SELECT [Current week] FROM [Current week]);

It may (or may not be) faster to do it all at once - try it and see. You might do more in one pass even though using the IIF function requires extra processing:

UPDATE [Master Database] SET [all week] = IIF([all week] = (SELECT [Current week] FROM [Current week]), "yes", "no");

strictly from the point of view of database design you don't need the tag field since you can always join on the current week table to identify the current week - this means you just work with your queries using both tables. Be sure that all fields used in query criteria are indexed. For Access I would try not to have a table with the same name as a field just as a precaution.

 

[AndyDevine1]

Thanks for this...but isn't this code based on where they are the same rather than where it contains?

As all weeks string contains eg "2013 week 19" or "2013 week 22" etc...and Current Week contains "week 19" etc?

Please advise

Many thanks

 

[AndyDevine1]

I was using this to create new table..as found updating too difficult...

Tag2: IIf([Master Database].[All Weeks] Like "*[Current Week].[Current Week] *","yes","no")

This doesnt work...can you let me know why?

Appreciate your help

Many thanks

 

[xenou]

Hi,
There's a space in what you posted so that would cause it to fail. Also I wouldn't use Like here since you can use equality. Your questions aren't clear to me (unless your records contain more data than you have shown in your post - have you posted enough sample data to describe the problem?).
ξ