denormalizing update query, but not really

[clunetta]

Continuation school students begin the term with credits (tblClasses.Start) and earn additional credits each week (tblClasses.W1 through tblClasses.W40). At the begninning of the next term tblClasses.Start has to be updated to reflect progress to date and the weekly credits reset to zero. The resetting to zero portion I can do, but variations of the following have not been a success.

UPDATE tblClasses SET Start = SUM(Start+W1...+W40);

What am I doing wrong?

 

[Crowley16]

the sum is to add up different rows on the same column, try dropping the SUM()

--------------------
Procrastinate Now!

 

[clunetta]

I feel foolish--that was so simple. Thank you.

 

[dhookom]

IMHO, the other thing you are doing wrong is your un-normalized table structure. I would create separate records for each week rather than storing week numbers in the names of fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]