Update Fields in a table

[ajb_1976]

I have a database that allows an accounts department to reconcile data. I have a table that contains several fields, two of which are date and period. What i want to do is update the period pending on the date - i.e. if the date is 10/05/2005 then the period is to be updated to 5; 10/04/2005 to 4 etc. I can do 12 update queries stating update to n if date is between first day of month and last day but was just wondering if there is a better way.

 

[Mile-O]

Does the period field simply represent a month? If so, you can remove this field as it is a derived value - it can be derived from using the Month() function on the original date field in a query.

 

[ajb_1976]

the period filed needs to be represented as a number 1 - 12 as the it is used in conjunction with another field called year - year is entered as L (last); C (current); N (next); this is the structure that the accounts software uses. The users will choose both a year and a period so C and 5 will be May this year etc. Thanks